Improving Sequential Number reliability

[GriffAdmin]

I know creating sequential number sequences is fraught with danger when multiple users are involved but I thought I had found a way around it. Every one of my users has a unque prefix and so my sequential number Initial Value formula is: 

Max(SELECT(Daysheets[PreparedBySeq], ([PreparedByCode] = [_THISROW].[PreparedByCode])))+1

When I combine the prefix and the number, it's usually unique. To make it more robust I have it set to Sync on Start. However Delayed Sync, Automatic Updates are off and the App can start when offline. 

Most of the time this gives me a unique sequential number for each user, but once in a while it will come up with a duplicate number for a user. When I look at the records, the two records may well be created several hours apart (based on the timestamp). I don't understand how the formula could come up with that duplication. Does anyone have suggestions as to why this may be  occuring, and if there's anything I can do to make it more reliable.

[Jonathon]

No - there is no reliable way to make sequential identifiers work in appsheet that doesn't come with huge tradeoffs elsewhere. Sequential ID's are unreliable when working offline or with concurrent users, and flat out don't work when using appsheets import functionality. They're a ticking time bomb.

You're trying to work around the multi-user constraint by adding a unique user prefix. It is still possible for your users to break this if they interact with the application from more than 1 device while logged into the same account.

And, ultimately, I have to ask... why do you want sequential ID's? What benefit are they providing other than to say record A existed before record B, or as a tally of total records created to date? Both of these items are easily determined without a sequential ID. If you really want a sequential ID, calculate them server side via an automation bot / spreadsheet formula / sql identity column; do not use them as the primary key in appsheet.

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Serial-Numbers-If-You-Must/m-p/286300

https://support.google.com/appsheet/answer/10106361?hl=en

All of these issues go away when you pledge allegiance to the hexadecimal god 

[GriffAdmin]

Hi Jonathon, thanks for your comments.

Yes, I understand that sequential numbering can't be 100% reliable and that's why I am using UNIQUE() to generate the Key. However it is impractical to provide a customer with a Day Sheet numbered something like eb2cb647 - trying to work with that at a customer service level introduces even more issues than my faulty sequential numbering.

Therefore I created the sequential number to make it easier, with the awareness that it may not be totally reliable. I also have the added complication that I need to allow the staff member to over-ride that number if he/she is data entering a paper day sheet with it's own number.

All I am trying to do is improve the reliability and I thought if I could understand how the duplication is introduced, I could reduce the occurrence.

I assume that the MAX() function works on local records, not server side records, so I don't understand how it misses previously created records on the local device. Each user has their own prefix so I don't care about records on the server that another user may have created when running the MAX(). Theoretically I should only need to find the MAX() of the records on the device, even if it not synced. However I am clearly missing something. 

[Jonathon · Accepted solution]

If I am understanding this correctly:

Max(
  SELECT(Daysheets[PreparedBySeq],
    [PreparedByCode] = [_THISROW].[PreparedByCode]
  )
)+1

Your select expression returns a list of all sequential IDs for the current logged in user, and you are grabbing the maximum value + 1. Assuming there's no implementation issues (e.g. [_THISROW].[PreparedByCode] is populating correctly), then your expression is about as good as its gonna get.

I have seen applications that have layers of virtual-column references that, when a record is saved or updated, can take ~10 seconds for the references to propagate. If you have something like that going on, it could result in duplicate records.

Also, if I'm understanding this correctly, you are using a UNIQUEID as your primary key, you're just wanting to maintain a human-readable sequential ID alongside it. This is much less of an issue, and also makes it possible for you to generate the ID's server side. The drawback here is it would take some time (~30 seconds) for the ID to populate in the app once its saved, but it should be more reliable.

[GriffAdmin]

Thanks for that. Your idea of generating the number server side sounds like it could help, but not sure how to implement that given the user has to have the ability to over-ride the number. Maybe I need to give more thought to see if I have "layers of virtual-column references" that could slow it down. Getting it to this point has been a 2 year evolution of the app with massive spec-creep so that may well be the problem. 

Thanks again for your input. I am reassured by your comment that my "expression is about as good as its gonna get." And your other comments have at least given me somewhere to look for a possible improvement.