How to pick the next available number?

AppSheet AppSheet Q&A
,
1

Every record I enter should have a auto assigned unique ID number that is up to 2 digits. In this case, the possible numbers would be anything between 1 to 99. Here is the problem I am trying to solve for:

If I have up to 50 records each with a unique number from 1 to 50 and I go back to delete any records, how can I have it pick the next available number rather than the max number when I enter a new record? Meaning, if delete records with IDs from 11 to 39, I want the next record to be assigned number 11 instead of 51. Right now, because of the max()+1, the number it assigns is 51.

Thank you in advance for your help.

1 Like
2

Use Select expression under count function

Tbl: Order, Key: OrderId

Count(select(Order[OrderId],True))+1

Place it in initial value of Key Column

3

Add a virtual column to the table called Next ID with the expression, ([ID] + 1). Set Initial value for the ID column to ANY(SORT({1} + Table[Next ID] - Table[ID])).

2 Likes