Hi,
I need to generate a sequential invoice number in a multi-user app.
I’m using a Bot that assigns this value to the field numero_factura in the Ventas table:
MAX(Ventas[numero_factura]) + 1
The problem is that when multiple users create records at the same time, some rows end up with duplicated numbers, and others skip the sequence. It seems like several bots are reading the same MAX value simultaneously before saving.
Table structure (simplified):
id_venta
numero_factura
What is the correct way to generate a reliable, strictly sequential number in a multi-user scenario in AppSheet?
I believe in AppSheet in a multiuser environment it is not possible and the behavior you are observing of several duplicates , skipped sequence will take place.
The following help article explains overall scenario well.
There have been several attempts in that direction but I believe none is totally impeccable.
That feature requires very specific conditions that are outside of AppSheet’s scope. In effect, if that’s your need, AppSheet isn’t the right tool for the job.
The reason this cannot be done is because of the NEW-ish technology brought to the table - namely offline processing.
AppSheet was built as a Mobile First platform with the aim to support offline processing from the very beginning. It has become an important differentiating factor between AppSheet and most other no-code platforms.
To support offline processing, the AppSheet platform was built around a distributed system - meaning every device gets its OWN isolated copy of the app and data. Each device has NO idea of what is happening on other devices so the same sequential number can be generated across multiple devices. if it is used as a key, it will cause problems
The only way to handle sequential numbering across many different users is to have a RELIABLE centralized service that hands-out the next sequential number to each device. Mobile systems are NOT 100% reliable with connectivity.
You could always do post-processing to assign/adjust sequential numbering AFTER rows have been inserted - if your use case allows that.
Lastly, the use of sequential numbers is an ARCHAIC technique that was used for performance boosts in older client/server systems. Those systems used a sequential number as an ID, to easily determine total counts and organize records chronologically. However, if there was a system error and a number was computed wrong, several pieces of information were affected and could be quite problematic.
There are much better ways to do all of these in today’s more advanced digital technology that are basically foolproof. Sequential numbering is a bygone technique. You will see it requested usually be those who simply have come to rely on it from older systems. It’s just what they are used to. By using sequential numbers, the app risks re-introducing those same old issues from ages ago!!
My advice is to adopt randomly generated Invoice numbers. There is no reason these days that they need to be sequential.
I understand your point, and I’d really like to adapt this to modern technologies. But what can you do when you have no control over the sequential numbering of invoices, like in countries where this type of numbering is mandatory?
For example, in our case the government assigns a starting number and an ending number for invoices, along with a validity period, and requires that all invoices be issued strictly in sequential order for every entity that invoices.
the invoices once generated are immediately emailed or is a PDF is immediately generated based on that invoice number?
Is the app used 24 hours by users or it is primarily a limited hours ( say day time office hours) use app.
Can invoice generation be a batch or scheduled process during non working hours. For example during the day time, the users flag off the records where invoices need to be generated. Then the invoices/PDFs are generated in batch/scheduled process at night and next working day those are available to the respective users for further emailing to clients/recipients etc.?
Actually point 2 in your response (working hours 8am to 7pm all days) could have been used in another approach. This approach creates the invoice sequence numbers and PDFs in the night at say 10 PM in a scheduled bot. First the bot assigns the sequential numbers (next to the previous day’s last sequence number) to the records flagged off for the invoice generation during the day. Then invoice PDFs are created for those records.
Since during non office hours, there will not be multiple users, sequential numbers could possibly be assigned through the bot.
This of course needs to be thoroughly tested for any edge case failure but I believe generally would be possible.
Alternatively, can a lag of say 10-15 minutes will do for the invoice generation if not one day? In that case the users flag the records for invoice generation but the sequence numbers and invoices PDF are actually generated by the scheduled bot. You could have 4 to 6 hourly scheduled bots spaced 10-15 minutes. Again, this concept needs to be thoroughly tested.
Yes It’s possible, but it’s my only solution to fix this right now. Besides, certain conditions have to be met for it to work. I’m testing it; we have to find ways.
If you’re using GAS, you’re having to wait for a sync to get the invoice number. If you can wait for the sync, you might as well just generate the number with AppSheet itself (with a bot) rather than using GAS. There’s no benefit to using GAS, but there is a downside: complexity. Generating sequential numbers with a bot is the safest way with AppSheet.