Question about Autonumbers within Entities and how/when they are generated.
0
Hi I have an issue with one of my applications where we are using an Autonumber within the Entity to help generate a unique ID based on the last submitted record. Currently to generate our ID we are retrieving the First item from the Entity (where it has a CurrentStep so as to not retrieve the current record or anything which is uncommitted) and we have sorted it on our Autonumber Descending so as to get the latest one. The application then uses another attribute from this item to generate a unique ID, xxxx-nnn(+1 from the returned item)/yyyy This has been working fine for us in practise for a while now but we have recently came across on issue. As you can see from the screenshot below eCASID 660 was committed 10 minutes before 658 was. eCASID 660 has the correct FormNumber as the Retrieve logic above would have returned eCASID 657 as the highest ID for a created/committed item. I am then assuming eCASID 658 has worked in the correct manner because it has Retrieved 660 as the highest Autonumber ID for a created/commited item despite it being higher than the current Autonumber. However the problem seems to occur on eCASID 661, it will return 660 as the highest Autonumber but because the FormNumbers are now out of sync it has created a duplicate entry. I intend on writing some logic to double check that the FormNumber doesn't already exists before committing it in order to try and work around this but I am confused about how the AutoNumber is behaving. Is the AutoNumber generated on the Create Object and then available to all users across the system despite not being committed? There is also no way to delete an Item in our system so I don't know why 659 was skipped (there are multiple cases of skipped AutoNumbers). A user can start a form and then Cancel it, it is then never committed at any point in the process, would this account for the missing AutoNumbers? Thanks Grant
asked
Grant Finlay
1 answers
2
If you create an object it will have an autonumber and indeed if that object is not committed you have a gap in your numbers as you already found out. And indeed you can not reuse that number. If you want to have a consecutive list of numbers you have to generate those yourself. You could use for instance an after commit action that checks if the object already has a number and then if not find the current highest number and add one to it. And do not forget to set an unique constrained on it so that you never get two the same number.