This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
“To get the auto-sequential number” without duplicates is one of the frequent question and ask here in the community, but there is no native and perfect solutions available for the time being.
This is not a perfect solutions, and I m not strongly recommend to implement this tricks into your production app as I m not sure how it will be stable in terms of the performence, but at least, my quick test showed positive result to generate the sequential number dynamically, using Googlesheet formula.
Please bear in mind the row ID is generated “uniqueid()” expression. Apart from row key, i generate the sequential number ID fields, where the value will be auto filled by Googlesheet formula. So this is backend task. When the app user add new row at the same time, those concurrent request to backend data base, i.e. Googlesheet will NOT conflict with others. It should be servived basis “first in first come”? Even the app user delete the row, the given sequencial Id stay same, and unaffected.
The blog from Googlesheet specialist, Mr. Ben Collins inspire me to test this with Appsheet, and hopefully it will work.
Once the sequential number is generated, then pass the same value to appsheet action and workflow to generate another sequential something, such as multi digit sequential ID and numbering using Concact expression in Appsheet.
Again, in general, Appsheet is not recommend to user Array formula in spreadsheet, so unexpected miss behaivior might happen. The use of this trick is at your own risk, please.
For this sample and screenshot as above, I placed the expression into B2 cell.
From master of Appsheet (@Aleksi) who gave a great advice and tips once again. We are able to push the same expression into B1 cell which is column name row. Read table on Appsheet editor. It wont take the spreadsheet expression, but take as a column value (name). Then app is ready to go.
HI @Koichi_Tsuji , Thanks for this spreadsheet formula tip works like a charm!
I see on your database that _rownumber 7 and 8 are deleted data, is there a possible way to add the new data to the blank _rownumber (7 and 8 for the next 2 new data, 17~ for the third new data)?
I’m a newbie in here. Your Tips are helpful. But How to update the Text Column in spreadsheet? I used the Text function in spreadsheet. It is not working. I tried to use initial value in appsheet, but no luck.
My scenario is generating Customer like C000001. The function above can provide ID in number format. If in spreadsheet, I used “TEXT” function, not like “Sequence” function, it will not be copied in new record. If in AppSheet, I try to do it in Initial Value, but cannot success as well.
In Appsheet I have a separate app to generate client numbers…a small and fast one, that refreshes on save, ang showing the number afterwards. My collegues use it without the fear of duplicates
Thanks for both assistance. I had solved the problem. I use the arrayformula in spreadsheet as
“=ArrayFormula(“C” & Text(SEQUENCE(MAX(ROW(Customer!A2:A)*(Customer!A2:A<>”"))-1),“000000”))"
I am not familiar with array formula, is it possible to have year & month as prefix, like YYYY-MM-000 for the sequential number created by array formula? Is array formula the only way on my situation?
May @OptimiX_XcrY elaborate how a separate app will help? Thanks in advance!
LinkedIn
Twitter
