Use UNIQUEID() as REF

[leeca]

Hello all, new to AppSheet - trying to implement the below DB Model in AppSheet:

In AppSheet, I have set up the relationship / reference between Contracts and Nomination as follows (Contract is input by the user as TEXT type, and is a primary key in the Contracts table)

Contracts (One)

• Related Nominations
  Type: List
  Formula: REF_ROWS("Nomination", "Contract")

Nominations (Many)

• Contract
  Type: Ref
  Formula: None

---

Now, I am working on setting up the relationship / reference between Nominations and Loading (Nomination ID is AppSheet generated using UNIQUEID() in INITIAL VALUE, and is a primary key in the Nomination table)

Nominations (One)

• Related Loading
  Type: List
  Formula: REF_ROWS("Loading", "Nomination ID")

Loading (Many)

• Nomination ID
  Type: Ref
  Formula: None

This is causing the error(s):

• Column Name ‘Nomination ID’ in Schema ‘Nomination_Schema’ of Column Type ‘List’ has an invalid ‘Initial Value’ of ‘UNIQUEID()’. The type of the Initial Value does not match the column type. Consider using ‘’ instead.

• Column Name ‘Nomination ID’ in Schema ‘Nomination_Schema’ has a ‘List’ type. Only virtual columns are currently allowed to have the List type.

• Key column ‘Nomination ID’ in Schema ‘Nomination_Schema’ cannot use an app formula. The app formula will be removed.

Is there any way for a key column to use UNIQUEID(), and use that key column as a REF to another table?

[Aurelien]

Hi @leeca

Column Name ‘Nomination ID’ in Schema ‘Nomination_Schema’ of Column Type ‘List’ has an invalid ‘Initial Value’ of ‘UNIQUEID()’. The type of the Initial Value does not match the column type. Consider using ‘’ instead.

You may consider changing the Nomination ID Type as Text, because UNIQUEID() returns Text.

Basically, you should have:
Table1 (Parent Table):

• keycolumn_t1, Type : Text, InitialValue formula:UNIQUEID()
• other columns on Table1

Table2 (Child from Table1):

• keycolumn_t2, Type : Text, InitialValue:UNIQUEID()
• related_table1_item : Type : Ref, Source : Table1
• other columns on Table2

In practice, you don’t need to create the virtual column

`REF_ROWS(“Loading”, “Nomination ID”)

It will be created automatically when saving, after you picked the source table in the Ref column of your Child Table.

Column Name ‘Nomination ID’ in Schema ‘Nomination_Schema’ has a ‘List’ type. Only virtual columns are currently allowed to have the List type.

A Virtual Column can have List Type ; a real column can have either Enum Type, or EnumList Type (if you are looking for “List” kind of type).

Key column ‘Nomination ID’ in Schema ‘Nomination_Schema’ cannot use an app formula. The app formula will be removed.

Here, because key-column are supposed to be absolutely unique, you cannot use a formula that may affect its value. So, you can only set a Initial Value Formula (and…typically UNIQUEID() )

For further informations, please have a look to

help.appsheet.com

References Between Tables

Learn about references, when to create them, and how to use them so your data makes sense.

help.appsheet.com

UNIQUEID()

Pseudo-unique ID

Can you please let us know if you succeed into this realization ?
Thanks