Enforce uniqueness

[Jonathon]

It would be nice to have a toggleable button which would enforce uniqueness on record values.

I know you can do this with valid_if formulas such as:

NOT(IN([_THIS], SELECT(Customers[State], NOT(IN([CustomerId], LIST([_THISROW].[CustomerId]))))))

However, I think it is a common enough need that it may warrant making it easier to do.

[Marc_Dillon]

The more I work with AppSheet, the more I realize that one should ALWAYS use a (usually hidden) unique key/id column for all records on all tables. That’s why AppSheet has the UNIQUEID() expression.

[Jonathon]

Right; relational databases should have at least one unique identifier for a row. However, there are multiple instances where you want to have more unique rows, in addition to the primary key.

One example would be a fleet management application with unique truck identifiers. You may want a UNIQUEID() primary key, but also a unique truck_id. You may want to keep them separate so that the truck_id can be renamed without breaking relationships that were tied to it. This thought holds true to any unique record where the possibility of renaming exists.

Also, if you use a valid_if statement like the above, you can give users immediate feedback, in the form of an error message, when they enter a duplicate id. Otherwise, AppSheets built-in duplicate id message only shows up when you attempt to save a record. And even then, the message can be confusing and it isn’t immediately obvious what went wrong.

Suffice to say, I find myself using the above pattern frequently.

[Marc_Dillon]

Oh I gotcha now. Yah that does seem like it would be useful.

[Aleksi]

@Jonathon What that button should actually do?