Many to many relationships & refs

Hello all,

So this year I am making a brand new app for our clients to use. This will have information on it about plants, their pests, how to harvest, etc.

Right now I am trying to set up some refs between the tables so that clients can easily navigate between associated information. For example, if they are on the Kale plant information detail view, I would like them to see refs in that view to kale pests, kale harvest info, kale recipes, etc.

Obviously, I have different spreadsheets for Plant information, Bug Information, Harvest Info, Recipes, etc.

Right now, in the Bug Info spreadsheet, I have a column titled [Plants Affected]. In each row is a list of plant families that are effect by the bug. There are mulitple plants within each plant family. And each bug affects multiple plant families. I would like to tell appsheet that if they find a plant family WITHIN this list, to form a ref to the plants that are in that plant family. In other words, I don’t want to have to give a new column to each plant family that is affected by the bugs because it would just be too long.

I have experimented with all sorts of virtual column expressions like SELECT, IFS, EXTRACT, etc., but my feeble brain can’t figure it out.

Also, just to make things more complicated, the plant [Family] is NOT the key in the Plant Information spreadsheet. The key is the [Plant]. So this whole thing will probably need to involve some derefs or something…

Happy to share more info. The main thing that is confusing me is the fact that there are many plant families listed within the same cell, and I am asking appsheet to separate them all out and form those info refs.

Many thanks!
Miranda

A VC in Plant table of type List, element type Ref.
FILTER("BugTable", IN([_THISROW].[Family],[Plants Affected]))

Assuming [Plants Affected] only includes possible [Family]s. Substitute to actual table names and column names as needed.

Thank you! Not working yet thought. See attached screen shot

.

What type of column is [Plants Affected]?

Text. I tried to make it List, but it said that was invalid, only a VC can be a List.

True. You could use an EnumList. Or you can leave it as text and use
SPLIT([Plants Affected],", ") which will get the editor to stop yelling at you. That is assuming, of course, that you have separated each item with a comma and a space. Basically, you want to split by whatever is exactly between the last letter of one item and the first letter of the next, to make sure there are no errors. Which would mean you want to make sure you also always enter them the same way.

1 Like

Ah, that is a helpful tip, thank you. I THINK I got it to work??? Honestly not sure. The Refs are showing up at the bottom of the other view, but they don’t say anything under the header.

I would love if it listed each Bug right there, and even better, if there could be a little Bug image with the name. Can you control what that Ref looks like? Or is there no control over it?

Did you point the Ref to the Bug table?

Also, “Bug Images” table. Is that the name of the table that contains all the bug information?

Hi Miranda,

Normally the best way to represent many to many relationships is by adding a table that represents that relationship. Here is a response I wrote to another customer on this subject.

Many-To-Many Relationships

Let’s assume you have two tables that you want to relate via a many-to-many relationship.

I will assume these two tables are Books and Authors with fields like the following.

Books

Title (text)
Subject (text)
Price (price)

Authors

Author (text)
City (text)
State (text)
Age (number)

You would keep the many-to-many relationship in a third table I will call BooksAuthors, having fields like the following.

BooksAuthors

Title (ref having ReferenceTableName=”Books”, ReferenceType=”Name”, ReferencedTypeQualifier=””)
Author (ref having ReferenceTableName=”Authors”, ReferenceType=”Name”, ReferencedTypeQualifier=””)

You could add more fields to the BooksAuthors table, if you wish, provided they concern the ‘BooksAuthors’ relationship.

The BooksAuthors table contains one row for each relationship between a Book and an Author.

Assume the Book table contains three records with the titles “BookA”, “BookB”, and “BookC”. Assume the Authors table contains three records with the Authors “Adams”, “Baker”, and “Claridge”. If “BookA” was authored by “Adams” and “Baker”, then BooksAuthors would contain two records for “BookA”, namely { “BookA”, “Adams”} and { “BookA”, “Baker”}. If “BookB” was authored by “Adams” and “Claridge”, then BooksAuthors would contain two records for “BookB”, namely { “BookB”, “Adams”} and { “BookB”, “Claridge”}. If book “BookC” was authored by all three of the authors, then BooksAuthors would contain three rows for “BookC”, and so forth.

Each of these table would be stored on a separate worksheet in a single workbook. Fill the three tables with the sample data I described above. This will help AppSheet figure out the proper data types when it reads the tables.

I would name each worksheet with the name of the table it contains. In AppSheet you would use the Advanced editor to add each of the three tables in turn. When you add each table, you must tell AppSheet which worksheet to use. That is why I suggest naming the worksheets with the table names. It makes it easier to keep the worksheet straight.

Once AppSheet reads the worksheets and generates the application, you will need to edit it in the Advanced Editor. You need to update the BookAuthors table to turn the “Title” and “Author” fields into “Ref” types. I provided the values you need to enter for the “Ref” fields under “BooksAuthors” above. Click the “Save” button and you should have a working application.

2 Likes

Ok, so I made a table to link the plant names and the bug names.

However, I still cannot figure out what expression to use to get the referenced views to show up how I want to.

Here is a pic of my linking table.

Here more of a look under the hood.

Try using this in the Related Bug Images formula SELECT(Plant Bug Merge Table[Bug Name], [Plant]=[_THISROW].[Plant])

Not working. Nothing showing up even though it is valid.

I want the Plant Information stuff to show up under the Bug Images in a referenced row. With the image.

Can you post a screenshot of the results of testing the expression? I feel like something easy is getting over complicated somewhere.

The expression is accepted and is shown as valid, but nothing appears in the app.

I understand that the expression is valid, but I want to know the TEST results to make sure it’s actually grabbing the correct information. As far as getting it to display, let me see a screenshot of the column where you put the Select expression.

So for example, in the Bug Images view, at the bottom I would like some refs of all the Plants who are effected by this Bug. (Listed in the Plant Bug Merge Table). But I don’t want to see the Plant Bug Merge Table, I want to see the info from the Plant Information spreadsheet of that correlated plant.

And then I want the reverse to happen as well. So when I am in the Plant Information view, at the bottom of the Plant’s detail view, I want to see refs to all of the bugs that effect that plant. I don’t want to see the info from the merge table, I want to see info from the Bug Image table.

I have a feeling I need to use some kind of filter expression, but nothing is working - it always says something is invalid.

The point of the your Merge table is solely meant to hold the relationship between Bug Images and Plant Information. It doesn’t hold any other data. That’s what Refs are. So what you want to do is super easy, but I need the correct information I previously requested.

1 Like

Is this what you are looking for?