Auto Generate Code for Key Column without UNIQUEID()

AppSheet AppSheet Q&A
,
1

Dear Appsheeters,

Is it possible for us to create a custom generated ID for a key column, that depends on the Transaction Date, for example I want to create a Sales Quotation Number with custom format like this

SQ-2024-0001, where I need to grab the SQ Date for creating the Year Number within the SQ No.

I managed to do it, but, I cannot regenerate the initial value every time i change the SQ Date,

is it possible to reset the Initial Value before saving? I know that initial value of a key column doesnt have the “Reset on Edit” feature.

2

Not for key column…

Sequential = “impossible” to keep track… well it will work* is you use auto submit feature.

It wont work if user enter 2+ forms and then update. In most cases it will follow sequence numbering but eventually will break.
You could try sequence with Appscript. I never did.

I did use date as UNIQUE ID for sequence numbering.

CONCATENATE(“SQ-”,Right(TEXT(NOW(),“yymmdd”), 5), Left(TEXT(NOW(),“ss”), 1), RANDBETWEEN(001, 999))

Result = SQ-501171809

where 50117 is YMMDD
“SQ-50117”&

All depends of how many users will input on daily basis.

is it possible to reset the Initial Value before saving? I know that initial value of a key column doesnt have the “Reset on Edit” feature.

You could try with App formula but numbering will be even messier.

1 Like