How to get values from the enum table into the other table?

Hi,
I have a table Opportunities having a column SeasonalityId (type number) that refers to the column Id (type number) in the table EnumSeasonality. In the table EnumSeasonality there are three rows and also the column Name1 (text) having 3 values: summer, winter, multiseason. → I need the column Name1 in the table Opportunities.

What I did. In table Opportunities, I changed the type of the SeasonalityId column into Ref and completed [SeasonalityID].[Name1] under Initial Value (I understand it is Dereference Expression). In the app, I expected the values from Name1 under the column SeasonalityId which obviously does not work.

Can you advise, please, what I did wrong?

I think it needs to be LOOKUP("Name1", LOOKUP(Opportunities[SeasonId], "EnumSeasonality", "Id", "Name1") =: lookup column “Name1” in table “EnumSea…” where “Id” equals “Opp…[Sea…Id]”?

I’m new here as well so there might be better ways of doing this.


According to this video: Dereference Expressions | AppSheet Help Center

You should be able to do: [SeasonId].[NameId] as long as SeasonId in Opportunities and EnumSeasonality ref each other.

The reason it might not work is if you forgot to make SeasonId in Opportunities a Ref type to Enum table.
Wrong, it does work both ways if you add it one way only. See:

Reverse References
For each Ref you add, the system automatically adds a reverse reference in the opposite direction.

Thank you for your reply elli-coop. Yes, this is the video that I tried to follow. The reference was added to SeasonalityId

The issue is that I am not sure were to put [SeasonalityID].[Name1] - at the moment it is under the Initial Value and also under formula but it still does not work for the app.

You put it where you want it. I’d put in a new virtual column if you want to display it or use that formula if you can directly specify it. Then you can “Show” this column independently.

It won’t go in initial value (although this will work), it’s better to put it in Formula that way it updates every time anything changes.

I felt the exact same way when first using AppSheet refs. :expressionless: