Many to many relationships & refs

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?

And…

Click the Test button, and screenshot that.

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.

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

Still nothing is showing up.

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

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.

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:
image
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?

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?

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.

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

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.

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.

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.

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

1 Like