Serial/Secuential Numbers

[MiguelPilo]

Create SERIAL NUMBERS with appsheet it's possible!

When a friend @jaichith ask me for help with this situation. I thought that it was so simple to solve, but when multiple users are ussing the app at the same time, things start to be complish.

Despite the limitations that appsheet has, it seems to me that there is always a way to solve problems, that is why appsheet is the best tool for non-programmers.

Community, let me share with you this application to create sequential numbers. I hope this could be helpful.

SERIAL NUMBERS by PILO's Portafolio 

[JuneCorpuz]

Hello-

Can you please explain how does the formula in Virtual SL NO works?

[MiguelPilo]

Hi @JuneCorpuz of course.

MAX(SELECT(TEST[SL NO],[_RowNumber]<[_THISROW].[_RowNumber]))+

IF([_THISROW].[SL NO]-MAX(SELECT(TEST[SL NO],[_RowNumber]<[_THISROW].[_RowNumber]))>1,[_THISROW].[SL NO]-MAX(SELECT(TEST[SL NO],[_RowNumber]<[_THISROW].[_RowNumber])),1)+

COUNT(SELECT(TEST[SL NO],
[_RowNumber]<[_THISROW].[_RowNumber] ))-
COUNT(UNIQUE(SELECT(TEST[SL NO],
[_RowNumber]<[_THISROW].[_RowNumber] )))+

[Record of deletes]

Remember that normal column [SL NO] just is a reference to help [Virtual SL NO] with calculate.

LINE 1:  Look the max [SL NO] (Look just in previous rows); [SL NO] was previously written by a bot for each added row.

LINE 3: Sum 1 to the max [SL NO], or if a rows were deleted sum the difference between [_ThisRow].[SL NO] and the max [SL NO]. For example; if you have 10 rows and you delete number 8&9 the max [SL NO] will be 7 but you need that the [Virtual SL NO] continue with their value=10 so, instead to sum 1 sum the diference betwen 10 and 7= 3+7 =10.

LINE 5: Sum duplicate values. when multiple users are adding rows at same time it's possible that [SL NO] written by the bot duplicate numbers. So, count how many rows there are previous this row and check how many of these are different. If you have 10 rows [SL NO]{1,1,2,3,4,4,4,5,5,6} but just 6 of them are diferent it means that you have 4 duplicate values. Sum duplicate values to max[SL NO]; 6(Max)+4(Duplicates)=10

LINE 10: Sum deleted rows (But just for duplicate values that were deleted).
Line 3 can't identify if a duplicate value was deleted because does not affect to sequential numbers. For example, if you delete the second row of this list [SL NO]{1,1,2,3,4,4,4,5,5,6} LINE 3 won't sum any value, or sum 0.

So, it's necessary register deleted rows for duplicate values. For this, the bot "RECORD DELETE". The condition of this bot acts when a duplicate [SL NO] is deleted. and sum 1 for each deleted row in all following rows to the one who was deleted. That's way initial value for [Record of deletes]=MAX(TEST[Record of deletes]).

It's important to hide [SL NO] and [Record of deletes] columns to your users in your app, to avoid they made changes on them, becouse [Virtual SL NO] could be affected.

[Adbadb]

Any thoughts on using something like this for ID?

TOTALSECONDS(NOW() - "01/01/2024") 

[OptimiX_XcrY]

It's a very interesting method.

I for one, still use in most apps the =arrayformula(row(a2:a)-1) in A2 cell,in google sheets, and it works ok even with more than 20.000 rows and 50 users. Every few months I move and adapt the formula at the current row, and the above rows I copy/paste values to conserve them.