Awesome News for the SalesLogix Provider – Support for Auto-Increment Primary Keys in SP1

This is some great news. Just caught an announcement from Stu Carnie, the man behind the provider at SalesLogix, about a huge change in the SalesLogix Provider coming in SP1. This will make SalesLogix imports using DTS really simple now. Awesome stuff!

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.

ABOUT THE AUTHOR

Ryan Farley

Ryan Farley is the Director of Development for Customer FX and creator of slxdeveloper.com. He's been blogging regularly about SalesLogix, now Infor CRM, since 2001 and believes in sharing with the community. His new passion for CRM is Creatio, formerly bpm'online. He loves C#, Javascript, web development, open source, and Linux. He also loves his hobby as an amateur filmmaker.

1 Comment

  1. Guillermo,

    It is no different than working with autoincrement fields in SQL or Access. You simply omit the ID field from the insert and it is automatically created (assuming you’ve turned on AutoIncrement for that particular table).

    -Ryan

    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!