Smallest missing consecutive number.

[Gustavo_Eduardo]

Guys, today I have a question that could be useful in many contexts. Let's assume a table with 10 rows and a column [index] that contains a natural number.

As expected, these numbers should go from 1 to 10, so each row will be numbered sequentially:

Row 1: 1

Row 2: 2

Row 3: 3

...

Row 10: 10

However, suppose I delete row 4, leaving 9 rows.

I want to add row 4 back and need a way to calculate that missing 4. One option (valid but from the caveman era) is to create a table with a list of numbers from 1 to whatever, all consecutive, and then filter to find the missing number. But I won't do that because it's inefficient. I believe there should be a more efficient way to achieve this.

The method should find the smallest missing consecutive number. Has anyone done this?

[mohssin365]

To ensure that a new row in your AppSheet application uses the smallest missing number as its identifier, you can follow a systematic approach that involves creating a virtual column to detect missing numbers, setting up an action to add new rows with the missing identifier, and configuring an automation to trigger this process. This method ensures that your sequence of identifiers remains consecutive and fills any gaps created by deletions.

[AleksiAlkio]

Is MIN(LIST(1,2,3,4,5,6,7,8,9,10)-Table[Index]) too complex?

[Gustavo_Eduardo]

I haven't tried it, but I'll try. I will comment on this post when @AleksiAlkio tries it. Thanks very much!

[AleksiAlkio]

Another way.. add a virtual column to check if the next number exists or not and add that number only if it doesn't exist. Then you can find out the smallest missing number from that column.

[TeeSee1]

This question relates to the subject of generating sequential numbers which have been discussed extensively and found to be impossible or very complex at best unless your app is a single user app. 

Just wanted to mention it.

[イルカのえっちゃん]

You may find this post useful.

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

[Gustavo_Eduardo]

Friends, thank you for all your responses. As you can see, I usually consult well in advance of needing to solve a problem to have time to review responses and execute the solution. In the coming days, I will be trying out and putting into practice what you have suggested to decide whether to implement it or if it becomes too complex. Anyway, I will keep you updated.