Filling Missing Transactions Numbers in GP

If the end user created any kind of transactions in GP that holds let’s say the ID 9 and another one that holds 10, then deleted 9; the next number will be 11 and 9 will be ignored.

For the clients that do not accept these gaps, and since almost all of GP transaction tables have setups for loading next ID number, I have created the following solution:

Create a trigger on your transactions table For INSERT and DELETE that perform the following actions:

UPDATE “Setup-Table” SET “Next-ID” =

(SELECT TOP 1 Covert(BIGINT, A.Number) + 1 As NextID From Table As A LEFT OUTER JOIN Table As B on Covert(BIGINT, A.Number) + 1 = Covert(BIGINT, B.Number) WHERE B.Number IS NULL)

WHERE SETUPKEY = XXX

Replace the “Number” with column name and Table with your table name.

Now each time a transaction added or deleted, your trigger will get the next available number and update the defined next number.

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS