Serial Numbers, If You Must

Danger Ahead!

In general, sequential numeric identifiers (i.e., serial numbers) are risky in AppSheet: if two users happen to add rows at the same time, both rows could be assigned the same serial number, which could lead to confusion (at the least) or data loss (at the worst). For this reason, serial numbers are strongly discouraged!

Basic Serial Numbers

One way to implement serial numbers is with a normal (not virtual) column named (e.g.) Serial of type Number and an Initial value expression of (MAX(MyTable[Serial]) + 1). Each new row will get a serial number one greater than the highest serial number already in use. The very first serial number will be 1.

Recycling Unused Numbers

If a row with a serial number is deleted, a gap in the numeric sequence will occur. To fill-in sequence gaps and reuse deleted serial numbers for new rows:

  1. Create an additional virtual column named (e.g.) Next Serial of type Number and with the App formula expression:

    ([Serial] + 1)
    
  2. Replace the Initial value expression of the original Serial column with:

    MIN(
      LIST(1)
      + MyTable[Next Serial]
      - MyTable[Serial]
    )
    

Then, each time a serial number is assigned, it will be the lowest unused number in the sequence. The very first serial number will be 1.

Read More

Concepts


Functions



8 Likes

I do have calculation Steve introduces as basic one. The danger to use this is not to place the serial number when app users go to offline mode. Just as additional cautions when someone use this in production apps.

3 Likes