Why the system allocates next counter for AutoNumber column?
Question
ID 30
Category TeamDesk
Date Created 5/17/2007 3:21:10 PM
Date Updated 5/17/2007 3:24:19 PM
When user creates new record the system allocates new counter even if record creation is cancelled. Why?
Answer
Autonumber reserves the entry when displayed, so the record will be saved with the number visible to user.
Why it is not cancelled? Imagine the following situation. One user tries to create the entry and #255 is shown. Second user tries to do the same, #256 will be shown. First user cancels. Should second user's entry be saved under #256, or #255?
Removing autonumber column from the edit form ( at least when adding the record ) will not allocate next number until the record is actually saved.
When deleting the record, it is not deleted physically, but, instead, marked as deleted, with the timestamp and the user. Deleted records are only displayed in the Recycle Bin (you may find the link to the recycle bin under the tabs). From there you can recover deleted record back to normal state, or physically delete it. Or the record will be deleted automatically after 30 days.
This is another reason why the autonumbers can not be reused. The situation: record #255 is deleted and new record will reallocate #255. Then the user go to recycle bin and tries to recover deleted record. Since autonumbers are typically keys with unique constraint, the recover will fail due to duplicate #255 entry.