UNIQUEID - Changing when row updated

[Chloe_Walker]

I am currently using an app formula UNIQUEID to create an ID for each new row added. There are instances where a user may go back and edit a column (or multiple) and I would like the UNIQUEID to be unchanged but each edit is regenerating the UNIQUEID. As it is an app formula, the methods I have tried are not working.
Is there a resolution for this?
Ideally I would like a sequential number CC0001, CC0002, CC0003 etc but as we have many users, I do not want to risk duplicate ID’s.
Thank you.

[Lynn · Accepted solution]

Hi @Chloe_Walker Is your formula an “Initial Value”?

[Steve]

Chloe_Walker:

As it is an app formula, the methods I have tried are not working.

As @Lynn hinted, your UNIQUEID() expression should be in the column’s initial value, not in its app formula.

help.appsheet.com

App Formulas and Initial Values

How you can set app formulas and initial values to dynamically assign values.

[QREW_Cam]

Steve and Lynn nailed it on the head to move that ‘Formula’ expression into the ‘Initial Value’ field.

But regarding the sequential number portion:
For the Key Column I 100% suggest you used randomized strings (UNIQUEID()).

However if the user are more used to seeing IDs like CC0012 you could have another field that runs an initial value to create those sorts of codes that are visible to the user, but still use UNIQUEID() as the Key Column the user wouldn’t see.

This would insure the security and consistency of UNIQUEID() but also would allow the easy to adoptable UX/UI the users would be familiar with. Also keeping you from losing sleep in fear of people deleting rows messing up the Key Value.

[Sameera_De_Silv]

Hi QREW_Cam,

I am creating a lead app to enter and view regular company leads entered by all employees. When creating users and leads I need to generate an ID to use for reference for other tables and the unique ID is generating and saving perfectly with UNIQUEID() formula.

But the problem is every time anyone edit a user or lead, again its generating another ID and save to the sheet. Same happening with TODAY(). I just wanted to show the user or lead created date but every time when edit, its saving the today date to the sheet.

Is there any way I can save User ID and TODAY date when creating the record and just show it without update when anyone edit the other fields of lead/ User?

[Koichi_Tsuji]

Sameera_De_Silva:

Is there any way I can save User ID and TODAY date when creating the record and just show it without update when anyone edit the other fields of lead/ User?

Sounds like you are pushing expression into App formula instead of initial value. When the new row is added, then app formula will return the values and save to the source. Then the same row is updated, the expression is rerun and keep the latest value.
Just move the expression to initial value and set the same fields editable set up to “FALSE”.
Anyone can update that field by doing so.

[Fernando_Lopez1]

Chloe_Walker:

Ideally I would like a sequential number CC0001, CC0002, CC0003 etc but as we have many users, I do not want to risk duplicate ID’s.

Sorry, you can’t

[Chloe_Walker]

Thank you @Lynn, @Steve & @QREW_Cam that has solved my problem. The one thing I did not try.

Regarding the CC*** numbering, the user can live without that for now, so the UNIQUEID() will work just fine.

[QREW_Cam]

Sweet!

Didn’t want to leave that portion unanswered because I know we often run into some cases where we have to add hints of legacy solutions into AppSheet ones to connect dots for them!

[Sameera_De_Silv]

@tsuji_koichi

I tried the way you replied and it worked like a charm. Resolve the issue at once. Thx alot