KNOWLEDGE BASE
Log In    |    Knowledge Base    |    4D Home
Tech Tip: Resetting Sequence Numbers in 4D v11 SQL
PRODUCT: 4D | VERSION: 11.4 | PLATFORM: Mac & Win
Published On: August 20, 2009
Your current browser may not allow you to download the asset. We recommend Mozilla Firefox or Google Chrome.
Log In

If you find yourself in a situation where the Sequence Number for a certain table is not exactly what you expect it to be, never fear because 4D allows you to manually set it.

In order to set the sequence number for a given table one must call the SET DATABASE PARAMETER command with the appropriate selector (31) and issue a new value for the current Sequence Number (the next time the Sequence number command is called for this table the value returned will be this number +1).

One approach is to set the Sequence Number value to that of the Total Number of records for the table (which is a good approach if no records have ever been deleted).

For example, the following code sets the Sequence Number for the Employees table to be equal to the total number of records found in the Employees table:

SET DATABASE PARAMETER([Emp];Table Sequence Number;Records in table([Emp]))


If you have deleted records from the table then you could use the Record number command to retrieve the physical record number of the last record (unless you deleted the last record). Here is an example of how to use that approach:

ALL RECORDS([Emp])
LAST RECORD([Emp])
SET DATABASE PARAMETER([Emp];Table Sequence Number;(Record Number([Emp])+1))


NOTE: Record numbers start at 0 while Sequence Numbers start at 1. For this reason the record number is increased by 1 in the call to SET DATABASE PARAMETER.