yellow triangles question

[coldcoffee]

Hello!

Background: I am building my app with a preexisting database. Two tables reference each other: Lakes and LakeChains. A Lake can occasionally belong to more than one chain, and a Chain (by definition) has more than one Lake in it. I cannot for the life of me get the ref to pull an enumlist of Chain Names from the Chains table without those yellow exclamation marks.

Question: (this might be where my confusion creeps in) when i create a new row for a new lake in the Lake table and choose a chainname from the enumlist drawn from the Chain table, is it required to save the chain Key (uniqueID) to the Lake table, rather than the label(name of the chain)? Do I need to go into the Lake table in the existing spreadsheet and change all the Chain Names to the value of the Key instead?

[AleksiAlkio]

When using Ref column, you need to play with the key column. If you write the Valid_If like Table[Name], it gives the list, but they are wrong as they are not key values. You need to write it like Table[KeyColumn].

[coldcoffee]

Now it is only displaying the Key column values (random unique ID) rather than the Label column

[AleksiAlkio]

Then you need to set a proper column as a label in your source table.

[coldcoffee]

I'm so sorry, I don't understand 'proper column'

i have text-type columns set as Label, containing the lake's and chain's actual names in each table, and both tables have a uniqueID-type column with the random numbers, set as Keys

[AleksiAlkio]

When using Ref or Enum/EnumList base of Ref, the app shows the value from the source table's label column. It shows it in the dropdown or in every view.

For example.. if you have a TableA with a Enum (base of Ref) column, and you are reading values from TableB, the dropdown shows you values from the label column, instead of key column's value. This means you need to write a formula to Suggested Values field for example like SELECT(TableB[KeyColumn],[Date]=TODAY()). This will give you a list of key values, but it shows label values. Because the key column's value neeed to be unique, good practise is to use UNIQUEID() with the initial value. But if you would show the key value in the dropdown, it would be impossible to select the correct one. That's why the app shows you the label as you could show a value that has more meaning.

[coldcoffee]

Thank you. Yes in table1 (lakes) I already have the uniqueID as key and the lakenames set as label..

however the spreadsheet I am working with was given to me already complete with data,  and in table2 (lakechains), the corresponding column for lakename has the actual lake name written in that column field  rather than the lakename’s uniqueID.

I am sensing that I need to edit the data and manually change everything in table2’s lakename column, to table1’s key value rather than the corresponding text that the key represents.

[AleksiAlkio · Accepted solution]

Yes that's true if you want to use Enumlist base of Ref.

[coldcoffee]

This was my solution - I had to manually change every entry of the value to the code in the key. it was previously entered as the text value that the key represented

[coldcoffee]

thank you

so to make sure i understand: i make the column type 'enumlist', and the base type 'Ref', and in the valid_if statement i would write ChainTablename[ChainKeyColumn] or ChainTablename[LakeKeyColumn] ?

[AleksiAlkio]

You need to use your source table's key column name.

[arslanbutt]

When relating tables in a database, it's advisable to save the unique identifier (Key) rather than the display name (Label) for consistency and accuracy. In your app, when creating a new Lake and selecting a Chain Name from an EnumList, save the unique Chain Key to establish a reliable relationship. Although you store Keys, you can still display Chain Names using expressions. This practice ensures robust linkages and prevents issues if Chain Names change. Consult your app's documentation for specific syntax or functions based on your development environment to implement this approach effectively.