Introduced in SalesLogix 6.2.1, SalesLogix offers the ability to define a field as an auto-incrementing field. This means that if you perform an insert, connecting through the SalesLogix OLE DB Provider, and leave off the field that is set as an auto-incrementing field, SalesLogix will automatically do the work of generating a new ID and stuffing it into the inserted row. This is especially useful in that it allows the use of standard data integration tools like SQL DTS or SSIS while at the same time preserving the SalesLogix rules around table ID creation and synchronization.
To set a field as auto-incrementing, you need to set the AUTOINCREMENT field in the SECTABLEDEFS table. This field should contain either a “T”, “F”, or NULL. The values are case-sensitive.
It is important to note that the Auto-increment feature is only supported for the Primary Key of the table. The functionality also exposes the @@IDENTITY variable through the SalesLogix OLE DB provider. This variable works the same as in SQL server, allowing you to retrieve the last auto-increment value executed using the current connection. It is also possible to query the last record using a query like “Select * from table where tableid = @@IDENTITY”.
Note that starting in version 7.0 SalesLogix has started to use this functionality in some of the SalesLogix LAN code. You should not turn on or off the standard AUTOINCREMENT flags in the SECTABLEDEFS table without first testing what the impact will be on SalesLogix.
In future posts, I will be demonstrating some real-world application of this feature utilizing SQL Server and DTS.