EnumList Reference Column for Multiple locations

[Suzie]

I currently have Table PlantList with column Zones set as a reference to Zones Table.  I also have other tables using the Zones table as a reference.

I would like the Zones column in Plantlist to be an EnumList with the Zones table as a reference.   When I try that, it breaks the reference to the other tables and I've not been successful in pulling the data with formulas.

I've tried a million things and just can't figure out what I'm missing.  

[Suzie]

I have made some progress but I need help filtering out the data that doesn't match.

I created a virtual column in the Zones table "Plants In This Zone" as a list with an APP formula of PlantsList[PlantID].  This pulls in every plant. 

I tried Validate if using SELECT(PlantList[Zones],[Zones]=[_THISROW].[MyZone]) but of course it throws an error can't compare text to list.

How do I get only the values that match Zone to MyZone?

[Steve]

https://help.appsheet.com/en/articles/4575708-list-dereference

[Suzie]

I've tried [MyZone][Zones] in Valid If

and got this error:

Column Name 'Plants In This Zone' in Schema 'Zones_Schema' of Column Type 'List' has an invalid data validation constraint '=[MyZone][Zones]'. Column 'MyZone' is used in a SELECT or list dereference expression and should be a List/EnumList of Refs

I also tried the Select example on that Dereference page and received an error also.

[Steve]

Please post screenshots of the complete expressions used to compute the values for the MyZone and Zones columns. Please also post screenshots of the configurations of those two columns, down to and including the Type Details section.

[Suzie]

I keep getting an error trying to upload the images so I'll have to do 1 at a time.  😞

[Suzie]

Zones Table

[Suzie]

t

[Suzie]

t

[Suzie]

I don't think this is going to work since I want to use the Zones table as a reference for other tables.

What's the best way to address wanting multiple zones for a plant?  A column for each zone? Another table?

I did try a separate table using an action to add the zones to it but I had trouble pulling all the info I needed that way, but I don't remember what the issue was.

[Joseph_Seddik · Accepted solution]

Welcome to the community!

I understand that you have many zones in a Zones table, and many plants in a planList table. One zone can contain many plants, also a plant can belong to many zones. 

In a relational database, this is what's called Many-to-Many relationship. To model this kind of relationship in a database, rather than an EnumList column, you should have a third table listing plants against zones. This table should have an independent key, a Ref column to the plantList table, and another to the Zones table. The latter should be marked with Is Part Of option. 

Once you have this setup correctly, you should be able to retrieve any data using simple dereference expressions. 

First please study this guide carefully and follow the links inside:

Data: The Essentials | AppSheet Help Center 

Tell us if you face any difficulty. 

Enjoy your AppSheet journey 🙂

[Suzie]

Thank you so very much!