How to control inline views when many-to-many relationships are involved

[_pete_]

I have 3 tables: Artworks, Tags, and a "Link Table". The Link Table exists because each Tag may have many artworks, and each artwork may have many tags. I created the relationships between these tables by setting a Tag Id and Artwork Id in the Link Table to ref, and then pointing each to their corresponding table. This generally works really well, except that I can't figure out how to make an Artworks detail page show tags as a deck view, while a Tag details page shows artworks as a gallery view. Here's what I mean:

Tag details looks right:

Artwork details does not look right:

I want it to look like this:

Which I can do by using a SELECT() function instead of REF_ROWS() in a virtual column in the Artworks table. But when I try to add a tag from the inline view using this method it takes me to the Tag form and I don't get a dropdown of possible values (like I do when I use REF_ROWS instead). In fact if I create a new row this way it's easy to create duplicate tags. Anyone know the right way for me to show tags as a deck view in my Artworks detail page. Grateful for the sage advice I always seem to receive here. 🙂

[イルカのえっちゃん]

This video may be helpful.
https://www.youtube.com/watch?v=BtY7ljzD8hI

[_pete_]

That video was helpful, but I still can't seem to quite get it right. This is my setup. I can't figure out what I'm doing wrong.

 

 

 

 

 

 

 

 

 

 

[_pete_]

I now have it working. Here are the steps I followed (with the video's help):

1. Create the following tables with the following columns
1. Items
• Item Id
• Image
• Description
2. Links
• Link Id
• Item Id
• Tag Id
3. Tags
• Tag Id
• Tag
2. Create an appsheet app from these tables (via Extensions in Google Sheets)
3. Add all three tables, and create a view for the Tags table and delete the view for the links table
4. In the data section, for the Links table ensure that
• The Item Id column is a ref column to the Items table
• The Tag Id column is a ref column to the Tags table
5. For the Items table select the image as a label 
6. For the Tags table select the tag column as a label 
7. For the Link Table_inline view, set it to deck and use the Tag Id for the primary header
8. Create 2-3 entries in the Items table
9. Use the inline Add to add some tags to the items after you have created an item
10. Create a slice of the items table with no formula in it
• Call it Gallery view slice
11. Duplicate the Link Table_inline view
• Choose the Gallery view slice for this date
• Set the view to gallery
12. In the Tags table, add a virtual column
• Name it Related items
• Use this formula: REF_ROWS("Gallery view slice", "Tag Id")
• Turn off (deselect visible checkbox for) Related Link tables
13. Voila. Now the Items detail view shows related tags as a deck view, and the Tags detail view show Items as a gallery.

[_pete_]

I thought the solution I posted above was the answer, but it's not. The inline gallery view is showing all the items instead of just those items with a Tag Id that matches this Tag. I'm having a very hard time figuring out what expression to write for the Gallery view slice so that it only returns those items who have a corresponding entry in the Link Table which also contains the Tag that I'm currently looking at. Does anyone have any pointers?

[_pete_ · Accepted solution]

Okay, I've tested it and I think this is the correct solution

Create the tables and the app

• Create the following tables with the following columns
• Items
• Item Id
• Image
• Description
• Links
• Link Id
• Item Id
• Tag Id
• Tags
• Tag Id
• Tag
• Create an appsheet app from these tables (via Extensions in Google Sheets)

Set column-specific information

• Add all three tables, and create a view for the Tags table and delete the view for the links table
• In the data section, for the Links table…
• Set the Item Id column is a ref column to the Items table
• The Tag Id column is a ref column to the Tags table
• For the Items table select the image as a label 
• For the Tags table select the tag column as a label 

Configure the first inline view

• For the Links_inline view, set it to deck and use the Tag Id for the primary header
• Set the Main Image to None

Populate some data

• Create 2-3 entries in the Items table
• From a detail view of one of the items, use the inline Add to add some tags to the items after you have created an item

Create virtual columns & Slices

• In the Links table create a virtual column called Linked image with this formula: [Item Id].[Image]
• Create a slice of the Links table with no formula in it
• Call it Gallery view slice

Create a view

• Duplicate the Links_inline view
• Choose the Gallery view slice for this data
• Set the view to gallery

Create one more virtual column

• In the Tags table, add a virtual column
• Name it Related items
• Use this formula: REF_ROWS("Gallery view slice", "Tag Id")
• Turn off (deselect show checkbox for) Related Link tables
• Voila

[njordan]

Does this approach allow you to easily relate existing elements? I.e. can you add an existing tag to an item from the item details or item form page? And vice versa from the perspective of a tag? 

[_pete_]

What I've found is that the resulting user experience is what I would call "passable." I can add tags to an artwork pretty easily. I haven't had great success with removing an artwork from a tag. Apologies if this response is confusing; I'm very loosely in touch with whatever it was I was doing here.