I work with two (actually three) types in my invoices. First you create a new record and that's an offer. That has an Id.
Only when an offer actually becomes an invoice I change the type to "Invoice"
Only then an invoice number gets generated. And it's the max invoice number plus 1.
That way you don't need to try and fill in gaps later on.
An offer only becomes an invoice on the moment the work has been done and the client needs to be billed.
I recommend that you mark the invoice void. This is much less troublesome to maintain and reviewing your voided invoices after the fact--while discussing what happened with the person that voided it can be a useful way to gather feedback on how to improve your user interface. Also, the invoice number is often referred to as an informal way to determine when it was created or when it was processed (depends on when you assign the invoice number)
I suppose the following script could determine that there's a "gap" in your series, but I don't recommend it for reasons I will list at the bottom>
Show All records
Sort Records [no dialog] //sort in ascending order by Invoice serial number
Go to record/request/page [first]
Set Variable [$K ; Invoices::InvoiceID]
Exit loop if [$K ≠ Invoices::InvoiceID]
Set Variable [$K ; value: $K + 1]
Go to record/request/page [next ; exit after last]
Set field [Invoices::InvoiceID ; $K ]
Reasons this is not the best idea:
1) If you have two or more users creating new invoice records at the same time, it's pretty easy to get two records with the same invoice number. Adding validations and code to handle that when it occurs to correct the issue can get a bit messy.
2) As the number of invoices in your table increase, it will take longer and longer to add new invoice records.
One option for minimizing the creation of Voided invoices, is to use an internal, hidden serial number field as the primary key for your invoices and assign the visible invoice number that you want to be gap free just before you print a copy and finalize the sale transaction that it represents.
I've come up with a 'serial number on demand' trick that can be used in that way and it avoids the problem of multiple users producing duplicate serial numbers. If that sounds useful, let me know and I'll describe that method.
Thanks for your help. I think I will go ahead with the idea of having an internal serial number and then the printed invoice would have a separate number that we input. I see how after the table has more records it would take longer for a script to search for the new one.
I wouldn't have the user input the serial number--too much chance for user error if you do.
Do it this way
current table: Invoices
__pkInvoiceID (auto-entered serial number, used in relationships to link to other tables)
New table: InvoiceNumbers
_fkInvoiceId (number field, use it in link to Invoices)
InvoiceNumber ( auto-entered Serial Number )
Define this relationship:
Invoices::__pkInvoiceID = InvoiceNumbers::_fkInvoiceID
Enable "Allow creation of records via this relationship" for InvoiceNumbers in this relationship.
Now this script step can assign a unique invoice number to the Inovoices record once and only once. (Run the script again on the same record and nothing changes.)
Set Field [InvoiceNumbers::_fkInvoiceID ; Invoices::__pkInvoiceID ]
The first time it is performed on a given record, a new record in InvoiceNumbers is created and InvoiceNumber auto-enters the next serial number in its series. If it should be accidentally run a second time, no new record is created and _fkInvoiceID is set to the value it already has--so there is no change to any data.
To display this invoice number on your Invoices layout, just use the field tool to add InvoiceNumbers::InvoiceNumber to the layout. It wil be empty until you choose to run the script with the above set field step in it.