Serial Numbers, If You Must

AppSheet Tips & Tricks
1

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

25 Likes
2

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.

4 Likes
3

Hi @Steve , this is super useful. Can I ask you for a modification of this that would help me to assign sequential numbers to a set of rows in a table? What I mean by that is, let’s say I have 2 tables on my app, the first is ‘Patient Bank’ that records basic patient details like the name, age, gender etc. The next table called ‘Follow Up’ records follow up details of each patient. So the ‘Follow Up’ table is a “child” table referenced to the ‘Patient Bank’, which means the ‘Follow Up’ table has a [Key] column with a UNIQUEID() expression for each entry, and a [Patient Bank Ref Key] column that fetches the relevant [Key] from the ‘Patient Bank’ table pertaining to the patient.
This ‘Follow Up’ table has a number type column called [Admission number], and I would like this column to record a sequentially incrementing number (like 1, 2, 3 etc.) when a new “Follow up” record of a patient is made, but that also means if a “Follow up” record is made on another patient, that patient’s [Admission number] should start afresh with 1, and then go up as 2, 3 etc. when newer “Follow up” records are made for that new patient.
Is this doable?
I wrote something like this, but it didn’t bring out what I wanted.

IF(AND(([_THISROW].[Key] <> [Key]),([_THISROW].[Patient Bank Ref Key] = [Patient Bank Ref Key])),

(MAX(SELECT(Follow Up[Admission number], AND(([_THISROW].[Key] <> [Key]),([_THISROW].[Patient Bank Ref Key] = [Patient Bank Ref Key])))))+1

,1)

Your advice would be much appreciated.
Thanks.

1 Like
4

Steve:

(MAX(MyTable[Serial]) + 1)

Ah, I’ve made a mistake in the expression I wrote. I removed the IF and it works fine now.

(MAX(SELECT(Follow Up[Admission number], AND(([_THISROW].[Key] <> [Key]),([_THISROW].[Patient Bank Ref Key] = [Patient Bank Ref Key])))))+1

Thanks for the inspiration @Steve .

3 Likes
5

Also combine letters with sequential numbers without having to utilize multiple columns.

This depends on max [_RowNumber] being the same record as the max serial number.

Below expression also assumes it is the key column that is holding the letters plus serials numbers, which are like: abc0001 abc0002 abc0003, etc. If the serial number is in a different column, you’d have to wrap MAXROW() inside a LOOKUP().

"abc"
&
RIGHT( 
  "000" & ( NUMBER( RIGHT( MAXROW( table , "_RowNumber" ) , 4 ) ) + 1 ) ,
  4
)
5 Likes
6

A little less cumbersome…

"abc"
&
RIGHT( 
  "000" & ( MAX(table[_RowNumber]) + 1 ) ,
  4
)
3 Likes
7

No I wouldn’t do that, as that introduces another possible error case.

Imagine you have 3 records:
rowNumbers: 2,3,4
serials: abc02 , abc03 , abc04

First, abc01 doesn’t even exist, because your column header are on row 1.

Second. Imagine you delete record abc03, and not just from the app, you actually delete the row from the sheet (or you have an auto-delete clean-up script that runs regularly). Max row number is now 3+1 again, but serial abc04 is already in use.

Either way, the user must pick where they need to draw the line of acceptable errors if they’re using sequential numbers.

3 Likes
8

Oh, yeah, I see what you’re doing now. My bad!

1 Like
9

Steve:

(MAX(MyTable[Serial]) + 1)

Super helpful.

2 Likes
10

Thanks a lot @Marc_Dillon You saved my day!

2 Likes
11

I do not want to offer a number smaller than 10000
That the (first) lowest number will be
10001
10002
10003
10004 …

I use it …

“”
MIN(
LIST(1)

  • MyTable[Next Serial]
  • MyTable[Serial]
    )

“”

12

Are you asking a question, or offering another solution? If the latter, you should clean up your posted expression.

1 Like
13

IM asking
Is it possible?

14

user6:

I do not want to offer a number smaller than 10000> That the (first) lowest number will be> 10001> 10002> 10003> 10004 …

user6:

Is it possible?

Sure.

Something like this should work (I’m sure there are other solutions as well).

MAX(  LIST( 10000 ) + MyTable[Serial] ) + 1
1 Like
15

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!

PLEASE DONT LEGITIMATE WEAKNESS WITH STUPID/UNACCEPTABLE REASON. IF APPSHEET IS THE FIRST IN THE EARTH, THEN YOU MIGHT TELL THIS.

2 Likes
16

Wow, “legitimate” (verb) is a real word. Who knew?

1 Like
17

Shah please calm down. It is just a formula. Breathe Look, this works for me: just count to 1000 until the anger has passed.
If you don’t get it, just remember: they are serial numbers too

please[Breathe] + 1
2 Likes
18

shahappsheet:

PLEASE DONT LEGITIMATE WEAKNESS WITH STUPID/UNACCEPTABLE REASON. IF APPSHEET IS THE FIRST IN THE EARTH, THEN YOU MIGHT TELL THIS.

I have no idea what this means.

3 Likes
19

Sorry Steve.

20

Would like to add to this post something I came across today.
Please keep in mind that if you were to edit a row with a sequential number (via App Formula), the sequential number would get updated.
In my app, I require the given serial number to stay the same.
So, I use this (MAX(salesdata[serial_id]) + 1) as an initial value instead.
There’s a high probability that a mistake will be made but this is the only way I know to work around it so far.
If anyone knows a much safer solution, please share.
Thank you.