Join three tables

[MaximBroos]

Hey Guys,

Trying some basic SQL but failing to get it working in Appsheet.

I have three tables: ITEMS, REMARKS, PICTURES. The idea is that an ITEM can have multiple REMARKS and REMARKS can have multiple PICTURES. 

Now I want to show all PICTURES that belong to an ITEM. In SQL i would just do two joins, but not sure how to achieve this in Appsheet. I can't get the formula of the REF_ROWS to work. 

Any guidance that you guys know? 

[1minManager]

Not exactly sure in SQL.  But if this was in Google Sheets you'd create a column called Pictures[Item] with a formula something of [Remarks].[Item].  That will cause a virtual column called Item[Related Pictures] to appear.  For this to work I'm asssming that the Ref column in Pictures which points to the parent table (Remarks) is called [Remarks].  Ditto with Remarks[Item]

Simon, 1minManager.com

[1minManager]

Or in Items create two virtual columns

Item[RemarksID] = [Related Remarks][RemarksID]
Item[Related Pictures] = Select(Picture[PictureID],IN([PictureID],[_ThisRow].[RemarksID]))

Item[RemarksID] will probably default to a Ref.  If this doesn't work try changing it to List

Simon, 1minManager.com 

[MaximBroos]

Thank you for your help! Yet, somehow I can't get that to work ( although your solution makes sense in my head)...

Am i correct in assuming that in the following code :

Item[Related Pictures] = Select(Picture[PictureID],IN([PictureID],[_ThisRow].[RemarksID]))

...the "Picture[PictureID]" can/should be changed to the default Appsheet key column of "Picture[Row ID]"?  (This still doesn't work though...)

I'll keep digging, if you know any tips in the meantime, happy to hear them. If I find a solution, I'll post it as well.

[1minManager]

In my formula [PictureID] is the key column of the picture table

[Steve · Accepted solution]

Create a virtual column in ITEMS with an App formula expression of:

[Related REMARKS][Related PICTURES]

See also: Build list dereferences - AppSheet Help

[MaximBroos]

Awesome, that is a frustratingly easy answer...

Now I need to figure out how to get the image to show instead of just getting the RowID of those pictures. But I'll look into that later this week. Thanks a lot Steve! 

[Steve]

First, make sure the virtual column created as above is of type List with an Element type of Ref and a Referenced table name of PICTURES.

If the virtual column added as above is named Related PICTURES, add a second virtual column with an App formula expression of:

[Related PICTURES][picture]

Replace picture with whatever the name of the column in the PICTURES table contains the Image you want to display. Make sure this virtual column is of type EnumList with a Base type of Image and that Allow other values is ON.