This is not a good database design for uniquely identifying invoices. A simple serial number is a better design option.
But there is a script step that can reset the next serial value back to zero or any value you set it up to specify. There is also a special function, getnextserialvalue, that can return the value that a field with this feature is set to assign to the next new record.
Other thoughts on invoice numbers:
For audit control purposes, it's best to never delete an invoice record but to mark an invoice you were going to delete as "void". This avoids "gaps" in your serial numbers. You can also set up a system that only assigns such a value to the invoice (not for use in relationships) just at the moment the transaction is finalized not when the invoice is first created.
can you write example
Hi day mond.
This is actually pretty easy to create.
You can create a new table occurrence of your Invoice table and relate it to your invoices by the year and month.
That way you can see the highest invoice number for that month in the related table and create a number that is one higher.
When there is a new month and therefore no existing invoice numbers you start back from 1.
This explanation might not make a lot of sense so I quickly made a youtube video to display how you could create something like this.
Hi Guy, good to see you back, but there's a cautionary comment that is needed for your method: When you have multiple users using this method to create new invoices all at the same time, there's a small but significant chance that you'll get two invoices with the same number. Essentially, two different people run the script (or the auto enter calculation evaluates) at the same time and thus both get the same Max value returned for the invoice number, add one to it and get the same invoice. For invoice numbers, this is not a good thing.
It's better to rest an auto-entered serial number via a script that runs on a regular schedule during "off hours", than to risk such an outcome with this other method.
Hi PhilModJunk, good to see you are still here providing your wisdom :)
It's true that this might be tricky with multiple users making invoices at the exact same moment. And there is a possibility of this going wrong all be it a very small one.
If your system doesn't have to many users then this might be sufficient. If not then PhilModJunk's system might be a better idea.
The only problem with PhilModJunk's system is that when you create an invoice and send it, it might not have an Invoice number yet. And so you can't send it yet to your client. You would either have to wait for it to get an invoice number in the off hours or force the invoice numbers to be made at that moment.
The user might forget to create the invoice number, but that could be checked with a script so you are not able to email an invoice out if it doesn't have a number.
But this is all a bit of work that might not be needed if you are just working a small company with few users.
I guess it depends on the case at hand.