Calculating & Writing Sequence Number which has to reset with the change of month

[Neeraj_Malik]

Looking for a solution to have sequence number that has to be written on three-digits like 001, 002 etc and the sequence has to be reset with the change of month. Also the same has to be prefixed with defined characters along with month & year of the day.

I could think of the prefix logic using concatenate but not abe to figure out the way to add 3 digit sequence number having reset mechanism with the change of month

CONCATENATE(“MB-INV”, TEXT([Date], “yymm”), _______)

Any suggestion?

[LeventK]

Due to the nature of the platform, you cannot provide sequential numbering. When 2 or more users are creating records at the same time, there is strong probability that the app will generate the same number for these users. Therefore you shall avoid using sequential numbering.

help.appsheet.com

Sequential Keys

A technical rationale for why AppSheet can't support sequential keys.

[Neeraj_Malik · Accepted solution]

I figured out a way to do this where I wanted to have the value of column [Rececipt Number] in sequence and the sequence shall reset to 1 at the change of month.

1. use the following or similar expression in google sheet

CONCATENATE("" &"MB/PMT/" &text(A3,"yymm") &TEXT(IF(MONTH(A3)=MONTH(A2),VALUE(RIGHT(N2,3))+1,1),"000"))

2. Regenerate the structure of the respective table
3. this formula would become the value of Autocompute Spreadsheet formula
   
   
4. And then every row would have a different value in sequence and the following result is achieved
   

The only catch is that the value of 1st row would show as kind of error, which I personly handled by manually setting the value to MB/PMT/2012000. This value in above snapshot has been set manually

The issue of same value by two users would be handled through the key as UNIQUEID()