Use UNIQUEID() as REF

leeca
Participant II

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?

0 1 710
1 REPLY 1

Aurelien
Participant V

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

Top Labels in this Space