Serial Numbers, If You Must

Steve · 02-07-2020 01:54 PM

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:

Create an additional virtual column named (e.g.) Next Serial of type Number and with the App formula expression:
```
([Serial] + 1)
```
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.

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.

djmalaka

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 .

Marc_Dillon

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
)

Steve

A little less cumbersome…

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

Marc_Dillon

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.

Steve

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

Tiramet

Thanks a lot @Marc_Dillon You saved my day!

ADAJALAN

I tried this, and the result I get is like this
abc0001
abc0003
abc0004
abc0005

where is abc0002 ?

Marc_Dillon

Looks like it ain’t there.

ADAJALAN

Yes, right

every 2nd row,

rmsmeltz

Super helpful.

user6

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]
)

“”

Marc_Dillon

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

user6

IM asking
Is it possible?

Marc_Dillon

Sure.

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

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

shahappsheet

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.

Marc_Dillon

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

stilllifemuseum

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

Steve

I have no idea what this means.

shahappsheet

Sorry Steve.

LengliimAdmin

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.

Marc_Dillon

Yes, definitely use Initial Value and NOT App Formula. You may also consider setting the Editable_If to FALSE to prevent any user-modification.

SkrOYC

Or Editable_If to ISBLANK([COLUMN_TO_PREVENT_THE_USER_INTERACTION]) if you need your users to see it but not touch it

SkrOYC

Yeah. Sequential is not intended for AppFormulas

LengliimAdmin

That’s pretty neat. Updated instantly.
Thank you very much!

Dan_Oliphant

Hi Steve,

With the serial numbers issue, and recycling old numbers, how do you make it so it is counting rows that share the same reference unique ID.

Right now this formula works perfectly for the whole data set..

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

but I need it to count rows that share the same reference. Can you help?

SkrOYC

@Dan_Oliphant wrote:
With the serial numbers issue

Which one?

Dan_Oliphant

I am trying to use the recycling old numbers solution. But that works for the entire spreadsheet. I need it work work that same way, but within the database with each set of rows that share a unique reference.

Dan_Oliphant

i believe this works. Been testing it and seems like it does.

MIN(
LIST(1)
+ (SELECT(bol_addcharges[Next_Serial], [_THISROW].[job_uid] = [job_uid]))
- (SELECT(bol_addcharges[misc_title], [_THISROW].[job_uid] = [job_uid]))
)

TotalSolutions

Brilliant!! Thank you so much!! 👏👍😊🙏

TotalSolutions

Very Helpful!! Thank You!! 😊🙏