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.
Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85