A script could reset the serial number value to one value less to eliminate the gap in the invoice number sequence, but this must be done with care as it could result in invoices with duplicate numbers if not implemented correctly. If your understand how to write a script, look up Set Next Serial Value to see how this could be done.
A simpler solution may be to keep the invoice record created in error, but change a status field in it as "void". Then you have a sequence of Invoice numbers that are free of gaps--which makes auditors happy.
Another method is to keep the Primary Key for linking invoices to other tables such as a line items table separate from the invoice number printed on the invoice. The primary key is generated when you create the invoice, the invoice number is generated via script from a related table when you print or mark the invoice as "complete".
Thank you for this information! This gives me a clear view on my problem.
As you suggest in the last option, I won't like to use the primary key for this. Could you help me with finding a script to generate such an invoice number and that resets every year?
Thanks for your support! Very useful for a newbie.
The trick I came up with for "on demand" serial numbers is to use a related table to generate the serial number. You create a record in this table at the time you need the serial number.
Add a new table to your system and link it to invoices like this:
Invoices::__pk_InvoiceID = InvoiceNumbers::_fk_InvoiceID
Enable "allow creation of records via this relationship".
Define: InvoiceNumber as an auto-entered serial number in InvoiceNumbers.
Use this script step, performed from your invoices layout, to generate your invoiceNumber to use in your calculation:
Set Field [InvoiceNumbers::_fk_InvoiceID ; Invoices::__pk_InvoiceID ]
This generates invoice numbers reliably for both single user and multi-user (where two or more users might be doing this at the same time) systems. If you accidentally run this script twice on the same invoice, no harm is done and the number does not change as this step creates a new related record only if one does not already exist.
You will still need to reset this serial number's next serial value back to 1 at the start of each new year if you want that type of series here for this number.