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?
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
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
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.
In my formula [PictureID] is the key column of the picture table
Create a virtual column in ITEMS with an App formula expression of:
[Related REMARKS][Related PICTURES]
See also: Build list dereferences - AppSheet Help
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!
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.