Auto-increment support for SalesLogix Primary Keys
New to version 6.2 SP1 of SalesLogix is support for auto-increment primary keys, using the standard SalesLogix key generation algorithms.
This support functions exactly how auto-increment (identity) columns work in SQL Server, so when a new record is added to a recordset, but the primary key value is omitted, the OLE DB Provider will automatically generate the key value. If ADO is being used, it will then populate the primary key in the recordset after calling the Update or Update Batch method of the recordset. This functionality is achieved with support for the @@IDENTITY variable in the SalesLogix OLE DB Provider.
The @@IDENTITY variable works the same as in SQL Server, where it represents the last auto-increment value executed on this connection. It is possible to even manually execute a statement like “SELECT * FROM ACCOUNT WHERE ACCOUNTID = @@IDENTITY”, to retrieve the last row that was inserted with an auto-incremented key.
This functionality can be easily demonstrated in ADO tools such as ADO Explorer, by selecting an editable recordset and specifying values for all the necessary columns in the editable grid, except the primary key. As you move to the next row, to post the inserted record to the database, the primary key value will be displayed in the primary key column, assuming it was included in the SELECT statement. It is not a requirement to include the primary key in the SELECT.
Metadata in the form of a new column has been added to the SECTABLEDEFS system table, called AUTOINCREMENT. It should contain either “T”, “F” or NULL and is case-sensitive. “T” is only supported for the PRIMARY KEY, and consequently it is ignored for any other columns. A value other than “T” is treated as “F”.
This is represented as a new checkbox for the key column within the DB Manager of the Architect or Admin tools.