We have a file that we use for invoicing. Each file has a serial number that is incrementally auto entered.
On occasion we need to issue a dummy invoice or a back order. The back order needs to have the same number as the original order but with a letter added on. (i.e. 12345a, 12345b). It is not a huge issue to add the letter manually as it doesn't come up too often.
So here is the problem. In order to get a new record for the back order we get a new original serial number. We can change the new record to have the proper serial number, but then the subsequent invoice will have the wrong number because it will skip the number that we changed. In other words, if I need to make a back order for invoice 12345, I would get a new record, so the new record would be 12346. I would change 12346 to 12345a for the back order. The next invoice, therefore would be 12347. I need it to be 12346 as that is the next number. Right now, what I do is go back to "manage database" and reset the next number to 12346 again, not very convenient or user friendly.
I saw a way to have the number generated on "commit" rather than on creation, but it replaces the number that is there and I am still left with the same problem.