Unique key problem

I created a table in Attain 3.01 and found that Navision insists on making the first column a unique key. Is there an identity function so that I can assign a unique key automatically? I tried removing the key, but it was put back automatically. This table simply logs an order number, date and time each time we post receiving for a PO. There is not really a unique key since partial receipts cause an order to be posted more than once. Right now, the order number is treated as a unique key and I cannot insert another record for the same order number. David Rochford Zesco Products 317-269-9300 x188 david@zesco.com

What do you mean with ‘an identity function to assign a unique key automatically’? If you don’t assign a key to a table (properties-keys), then Attain automatically takes the first column as the key. Why don’t you make a key with the order number, date and time? Another option is to make an extra column, eg. ‘Entry No.’ and make this field the unique key.

Microsoft Access has an identity type that automatically assigns a unique number to each record. Does Navision require a unique key in each table? I don’t mind the PO Number field being a key but it is not unique in this situation. I would prefer to use PO Number as a non unique key or use no keys at all on this table. But I may be forced to create a unique key it seems. David Rochford Zesco Products 317-269-9300 x188 david@zesco.com

That’s right. A Navision database is not like ACCESS. Navision needs a ‘primary’ key. This is always the first key defined in Properties - Keys (if no key is defined, the first field in the table is taken as the key). Secondary keys can be defined as well. They don’t have to be unique within the table. So, you can make PO No. a secondary key. But the first key has to be unique within the table.

quote:


**David Rochford wrote:**Microsoft Access has an identity type that automatically assigns a unique number to each record. Does Navision require a unique key in each table? I don’t mind the PO Number field being a key but it is not unique in this situation. I would prefer to use PO Number as a non unique key or use no keys at all on this table. But I may be forced to create a unique key it seems.


That the identity type is automatically assigning a number to each record doesn’t means that Access does not require an unique key… in fact, the unique key is usually in those tables the identity type. You can do the same in Navision by using a serie and a code 20 field as key, or using an integer and modifying the code for autoincreasing that number. When using a database you’ve to define wich record you’re want to use, and for that you have to be able of unique identifying each record… if you’re going to have more than one record for each PO and you’re setting as key for that table just the PO then you’re starting in a wrong way… Ways of solving: a) Create a field type integer and modify the onInsert code for having it auto-increased. You can still use secondary keys for ordering by PO. b) Create a field type code 20 and assign a serie to the table, so you’ll modify the onInsert for using that serie.You can still use secondary keys for ordering by PO. c) Create a field type integer and set the key as PO No., integer field, and when inserting checking the last value of the integer field for that PO No. and increase it…You can still use secondary keys for ordering by PO. Regards from California, Alfonso Pertierra (Spain)apertierra@teleline.es

Access does support the Autonumber type and indeed is often the primary key however, Access does support both multifield keys and NO PRIMARY KEY as well. If you create a table in Access and try to save it without a Primary key, the system will prompt you to assign one or let the system assign one (Autonumber type) or NO PRIMARY KEY. Bill Benefiel Manager of Information Systems Overhead Door Company billb@ohdindy.com (317) 842-7444 ext 117

Access does in fact always have a primary key and it is Record Number. It just lets you think that is has no key. The solution to your problem is to have an index value as the primary key and increment as each entry is made, as in the registers. Paul Baxter

I solved my problem by making the primary key order number,date,time. I did not realize at first that the key could be made up of many fields. I’m very familiar with SQL and in fact I’m using SQL with Navision. SQL would not require a key at all. But Navision still uses its database methodology and simply stores the data in SQL. I’m probably dreaming but it sure would be nice if Navision would make Attain use native SQL and use SQL directly in their code. David Rochford Zesco Products 317-269-9300 x188 david@zesco.com

David, It would open up a whole heap of problems with application code to allow tables without primary keys in Attain since many aspects of the database and C/AL depend on it, and depend on the fact that all secondary keys also contain the values of the primary key; in actual fact the number of tables that would not need (or recieve) a primary key are few and far between. Technically there is no problem to do it and use SQL flexibly. If you look at the LinkedObject feature, for external database tables, it is possible to use a table without a primary key from Attain, but this is intended for integration purposes. At times, SQL Server will create an internal primary key - a ‘uniquifier’ - in some cases where it is not defined in the table schema but is needed for, for example, secondary keys. Your point about using SQL directly in application code is another matter and would be a great benefit.