Use UNIQUEID() as REF

AppSheet AppSheet Q&A
1

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

3X_f_f_ff3a2dab53fcb1c13e4b98ac4ac1ff71528f1ae3.png
3X_f_f_ff3a2dab53fcb1c13e4b98ac4ac1ff71528f1ae3.png1826×958 68.4 KB

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?

2

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

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

3 Likes