In current versions of Creatio, autonumber fields are now set as a column default value. This new type of autonumber field is much easier to create (the previous method involved creating system settings, then modifying the object subprocess or adding code to pages). The autonumber itself is done as a sequence in the database. For any autonumber columns, Creatio creates a sequence in the database, the name of the sequence is the columns UId.
Read more about database sequences for Postgresql or MSSQL
One thing that made the older method of working with autonumbers was that to reset the value of the autonumber (or change it to start at some new number) was easy, you simply modified the system setting. However, since this is now at the database level, this article will provide some useful functions for working with sequences (Note, since new systems for Creatio are Postgresql only, I’ll only be providing these for working with Postgresql sequences. Similar methods do exist for MSSQL that you can research)
Getting the Column UId
The name of the sequence will be the UId for the column. Once you add the autonumber column, you’ll need to get the UId for the column. You can open the object then select “Open metadata” under the Actions menu, then find the column and get the Guid for the UId. That is the name of the sequence in the database. For the samples in this article, I’ll be using the UId “ed398640-de69-842b-c50c-6c673da5aa98”. Obviously, you’ll need to replace that with the UId for your autonumber column.
Getting the Next Number in the Sequence
To get the next number in the sequence, you can use the following (Note, this will also increment the sequence value, meaning, it doesn’t just tell you the next number – the sequence position will now be on the next number):
SELECT nextval('ed398640-de69-842b-c50c-6c673da5aa98')
Checking the Last Used Number in the Sequence
If you’d like to view what the last number the sequence generated, you can use the following:
SELECT last_value FROM "ed398640-de69-842b-c50c-6c673da5aa98"
Resetting the Value of the Sequence
If you’d like to reset the sequence to start back at 1 again, you can use the following:
SELECT setval('ed398640-de69-842b-c50c-6c673da5aa98', 1, false);
Note, the 1 in the above means that it will reset the sequence to the value 1. The false indicates that the 1 is not yet used, meaning the next number will be 1, not 2. If you wanted to set this to start at the value 58601, you’d execute the following:
SELECT setval('ed398640-de69-842b-c50c-6c673da5aa98', 58601, false);
View all Sequences and Details in Database
Lastly, if you want to see all of the sequences in the database and details, such as their current value, etc. You can execute the following:
select * from pg_sequences
Autonumber Prefix and Number of Digits
Note, that for autonumber columns in Creatio, the database level sequence just stores the current number. The prefix (such as “REQ-“, “SR-“, etc) and the number of digits are stored at the object level. View the column default value for the object to change the prefix and number of digits. See more in the article Working With Autonumber Fields in Creatio
