Auto-Increment Field

Each record in a Table should contain a field or set of fields that provides the record a unique ID. This will usually be your Primary key. In some cases, the value of the primary key must be manually provided by the operator (e.g. entering a unique string ID as part of data input). In some cases, the back-end database will provide the primary key automatically when the record is saved. In other cases, the Data Dictionary must to provide the unique ID when the record is first saved.

The auto-increment feature of the Data Dictionary provides this facility by automatically assigning unique sequential incrementing numbers for each new record.

In order for the Data Dictionary to know what next increment value will be for a new record, it must store the last assigned number in some other external table. To create an auto-increment field you will need to define the table and field of the external table as well as the ID field in your Data Dictionary. The field property, Field_Auto_Increment, is provided to do this:

Set Field_Auto_Increment Field Customer.cust_number to File_Field OrdSys.last_cust_num

In this example, the field last _cust_num in the system table ordsys is designated to increment and provide a value to the field cust_number in the Table Customer.

Auto incrementing will only work correctly if the external table is a (one record) system table or is a related parent table.

You will use a system table if the unique ID is single segment as is shown in the above example with cust_number.  If you are using a field from a system file, you need to register this table as an external table so that it will be correctly locked during save operations. For example:

Set Field_Auto_Increment Field Customer.cust_number to File_Field OrdSys.last_cust_num

Set Add_System_File to ordsys.last_cust_num DD_LOCK_ON_NEW_SAVE_DELETE

You may use a related parent table if your unique ID is multi-segment. For example, an order-detail ID may consist of an Order Number (OrderDtl.Order_number) and a system assigned detail number (OrderDtl.Detail_Number). If the last detail number was stored in each Order within the Order header table (OrderHea.Last_Detail_Num) the auto increment field would be defined as:

Set Field_Auto_Increment Field OrderHea.Last_Detail_Num to File_Field OrderDtl.Detail_Number

You may only assign one auto increment field per DDO. If you attempt to assign two, then the first field’s auto-increment setting will be erased. If you need to assign additional auto-increment fields within your Data Dictionary you may easily do this by adding custom code to the Data Dictionary Creating event.

 

See Also

Defining Data Dictionary Field Attributes