Solved: Secondary Keys

Sean_Lim · 05-30-2020 05:55 PM

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:

Steve

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:

View solution in original post

Steve

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.