SalesLogix and Auto-incrementing Table IDs

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.

ABOUT THE AUTHOR

Kris Halsrud

Kris Halsrud is a Senior Analyst / Developer for Customer FX Corporation.

1 Comment

  1. Just ran across this post Kris. Just a heads up – do accidentally NOT turn on auto-increment for the VirtualFileSystem table. PKIDs for that table are in the form of string based GUID values, but the AutoIncrement logic does not contain logic for dealing with this PKID type.

    Problematic PKIDs are generated that result in errors running the WebClient.

    I know Ryan Farley used to have the practice of setting automatically AutoIncrement to T for all rows in SECTABLEDEFS.

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!