Secondary Keys

AppSheet AppSheet Q&A
, ,
1

Here’s a little question about the use of Secondary Keys (Difference Between Primary Key And Secondary Key | Programmerbay).

I’m interested in forming REF relationships based not only on primary keys (which Appsheet currently allows), but also based on a modifiable unique secondary key column set by the user (Appsheet doesn’t allow choosing more than one column as your key column). Has anyone found a computationally lightweight alternative way to do this? I was thinking of a LOOKUP expression but this might be computationally expensive for large tables.

Perhaps related to this:

[How do I prevent duplicate rows? I have a ta](https://community.appsheet.com/t/how-do-i-prevent-duplicate-rows-i-have-a-ta/4871/7) Questions

@Steven_Coile Thanks Steven. I really appreciate your help. I am thinking of adding a new article about “Editable Keys”. @Terry_Holt is only the latest person to try to achieve this. In the article: 1. I will describe why the table key cannot be edited. I will suggest using a UNIQUEID() as the key field. I will suggest using the VALID_If expression to enforce uniqueness of the field they wanted to use as the secondary key.

2

A column value is validated by the column’s Valid If expression, which is only evaluated when the row is added or updated, so the computational load is limited to those events.

To my knowledge, there is no “lightweight” way to test for uniqueness other than using SELECT() or its brethren.

The pattern I use is:

ISBLANK(
  FILTER(
    "ref-table",
    ([_THISROW].[unique-column] = [unique-column])
  )
  - LIST([_THISROW])
)

But, because each app instance is working with its own local copy of the data, it’s still possible for duplicates to occur if two users use the same value in different rows before each has synced the other’s changes. There is no inherent way to avoid this possibility, but you can mitigate it with workflows and/or reports.

See also:

2 Likes