Editing columns which are computed in Key Column

[Escapshion]

Hi everyone,

I have the following columns in my AppSheet app data, which is being fetched from Google Sheets:

1. _Rownumber
2. ID
3. Particulars
4. Due Date
5. Status

In Google Sheets, the "Status" column has a prefilled value that acts as a default dropdown selection. Also, the "ID" column is populated automatically via a formula in the sheet, which looks like:

=IF(LEN(CELL) > 0, "ID", "")

Initially, I set the "_Rownumber" column as the key, but I noticed that multiple empty rows are appearing because of the prefilled values in the "Status" column. To resolve this, I decided to use the "ID" column as the key instead. However, the issue I'm facing is that when adding a new entry, the "ID" column needs to be filled by the user, which overrides the formula, and I don’t want users to be able to edit this column.

I also tried the following workaround:

1. Created a virtual column
2. Applied an app formula: IF(LEN([Particulars]) > 0, CONCATENATE("UNIQID-", [_ROWNUMBER]), "")
3. However, the "Particulars" column can be added in new entries but cannot be edited in existing entries.

In short, key columns can be created as new entries but cannot be edited.

What I would like to achieve is creating a unique key based on the row number in a virtual column, but only when the "Particulars" column is filled by the user. Otherwise, I want the key to be removed.

Is there any workaround to achieve this? Here are a couple of things I’m considering:

1. Perhaps columns used to check conditions (like "Particulars") do not inherit the behavior of a key column.
2. Or maybe the app crashes only when the key column has multiple non-unique values.

Any suggestions or solutions would be greatly appreciated!

Thanks in advance!

[Marc_Dillon]

You should not have values in the status column if there is not a record there. Configure the dropdown in the app itself with those values.

Get rid of any formulas in the gsheet itself.

Don't use rownumber as the key, or as part of a concatenated key value. Just use UNIQUEID().

A record can't exist without a key value, there is no "only make the key value if a particular column is filled".

Key values inherently can only be edited at initial record creation, this is basic behavior of the system.

Also you posted in the wrong forum, this is Tips & Tricks, you should have posted in Q&A.