Many to many relationships & refs

Miranda_Lubarsk · 01-09-2020 02:35 PM

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

Bahbus

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.

Miranda_Lubarsk

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

.

Bahbus

What type of column is [Plants Affected]?

Miranda_Lubarsk

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

Bahbus

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.

Miranda_Lubarsk

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?

Bahbus

Did you point the Ref to the Bug table?

Bahbus

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

Phil

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.

Miranda_Lubarsk

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.

Miranda_Lubarsk

Here more of a look under the hood.

Bahbus

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

Miranda_Lubarsk

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.

Bahbus

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

Miranda_Lubarsk

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

Bahbus

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.

Miranda_Lubarsk

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.

Bahbus

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.

Miranda_Lubarsk

Is this what you are looking for?

Miranda_Lubarsk

And…

Bahbus

Click the Test button, and screenshot that.

Miranda_Lubarsk

Miranda_Lubarsk

It seems like something KIND of right is happening with the Plants being listed as a ref under the Bug Images, HOWEVER, it is not being filtered. So instead of showing me only the plants that are effected by the Carrot Rust Fly, for example, it shows a complete list of all the plants, even ones that have nothing to do with the Carrot Rust Fly. So it is not filtered at all.

Bahbus

Ok, so, now change the table to Bug Plant Merge Table[Bug Name] instead of
Bug Images[Bug Name] and test it.

Miranda_Lubarsk

Still nothing is showing up.

Bahbus

What does the test show though? Does the test come up blank?

Miranda_Lubarsk

Why wouldn’t it show up on the app though? Seems to be doing something correct in the test thingy. I don’t really know what the test is for…I’m unfamiliar with that part of appsheet until now.

Bahbus

Alrighty, so the expression is grabbing the right info. Let’s check why its not displaying.

First check to make sure have the column of the expression we just worked on set to Show.
Make sure the column is type List of Refs:

Have defined specific columns to show on the Detail view for the Plants?
Are you using a Slice as your datasource? If so, have you included this column in that Slice?
Are the Refs in the Bug Plant Merge Table pointing to Bug Images table and Plant Information table, respectively?

Miranda_Lubarsk

The element type is Name, but every time I try to change it to Ref and save, it reverts back to Name. Any thoughts on that?

I’m not using a slice and the detail view should not limit the columns shown.

Including screen shots of the join table structure. It should all be good. Thoughts?

Bahbus

I bet that issue with it reverting to Name type is the main culprit. But even then, it’s not quite describing why nothing is being displayed. We got it pulling the right information now. And things seem to be lining up correctly. The merge columns look good. Even as List/Name it should still show them in the view, they just wouldn’t link anywhere.

The column where we put SELECT(Plant Bug Merge Table[Bug Name], [Plant]=[_THISROW].[Plant]), let’s look at all the settings for that column.

Miranda_Lubarsk

Bahbus

I guess let’s try deleting the column completely and recreating it with the same settings except Element type as Ref.

Miranda_Lubarsk

Ok, I just did that, and put a new virtual column, and the same darn thing happened all over. Reverted to Name.

There has to be something else going here…ugh.

Bahbus

Seems to be. I would send an email to support@appsheet.com and tell them about that, and maybe include the link to this thread so they can kind of get up to speed quickly.

Jan_Lambrecht

Hi,

I’m having exactly the same problem as in this topic: I have connected two tables A and B with many to many relation (with bridge table C in between).
I want to display the referenced records of table A as a nested table column in the deck view of the table B. Cannot create a list with element type ref.

Somebody an idea how to create a nested table view of a table that is connected with a many to many relationship?

Thanks in advance, Jan.

MultiTech

Just dropping in a link to a summary post I made, which contains a sample app, about how to accomplish this.