Dereference question

[RedVox]

Banging my head on a wall about this one...  I have three tables: members, boats, slips.   Every boat is owned by a member so there's a Related Boats column in members.  Each boat is assigned to a slip, so there's a Ref column in slips for assigning a boat to a slip. 

In a table view of members, I have a virtual column to display the boat(s) names.  I would also like to display the related slip names, but I can't quite figure out how.  If it was MySQL, it would look like this:

Select member_name, boat_name, slip_name FROM members
JOIN boats on boat.owner = members.id
JOIN slips on slips.boat = boats.id

What would be an appsheet formula that returns a list of slips associated with boats owned by the member?

[Suvrutt_Gurjar]

Please create a VC called say [Related Member Slips] in the Members table with an expression something like 

SPLIT([Related Boats][Related Slips],",")

Where [Related Boats] is the reverse reference column in the Members table and [Related Slips] is the reverse reference column in the Boats table.

Please ensure for the newly created [Related Member Slips] column's settings  - type List , Element Type; Ref , Referenced Table name : Slips

[RedVox]

Thanks Survrutt.  I gave it a try, but the new column comes up with no values.  I checked that the new column is a list with ref type set to Slips, and I checked that I have needed columns.  I also tried it without the Split() function, and it also returns no value.   I think your approach is on the right track, but it's not quite working for me.

[Suvrutt_Gurjar]

If you could share the column expression details and related column details, we could possibly look into.

[RedVox]

The new column:

The related boats column in members: 

The related slips column in Boats:

The boat column in Slips: 

[Suvrutt_Gurjar]

Thank you. It sounds that there is a space in the separator such as  " , ".

If so could you try as recommended without space, that is ","

SPLIT([Related Boats][Related Slips],",")

[RedVox]

There are no spaces, it just looks that way. 

[Suvrutt_Gurjar]

Okay, thank you. Could you please try 

SPLIT(TEXT([Related Boats][Related Slips])," , ")

[RedVox]

Okay, so with that change, the column now shows "related member slips(1)" in rows where I'm expecting one or more slip names to show.  When I change the column to a base type of text, it shows me the index field value for the slip.  So, that's closer.  

My original approach to this table was to simply use a MySQL view of the three joined tables.  I decided to try this approach so that I could also use multi-select action items on the table (which doesn't work when it's a mysql view, I think).  However, getting the multi-select actions I wanted from the list is also proving to be very difficult (eg send a csv for the selected records only).  So I think I will just abandon this design and return to the original.  And, for the multiselect items, I will hope an answer arises concerning how to make a select statement that works with a list of ref-rows.   My kingdom for a ForEach loop, or an export csv of selected records feature!  Thanks for your great advice!

[RedVox]

Actually, it looks like i can use multi-select on the mysql view, so I'm back to using the view, which eliminates this problem (though it's still something I'm curious about and wish to understand better.) 

[jkevz]

In order to display the details of each table you need to create only 1 reference value that is related to your parent table. On the other hand, make your table (Member) as the parent table, and then child table will be Boat and slip. In that way you may able to see the details of each table in the detail view of your parent table.

[RedVox]

Yes, if I was designing from scratch, I might take this approach.  However, I'm working with existing mysql tables that have worked fine for me in the past and I would rather not change the design as the tables are also used in other tools. 

[Uk]

To display the related slip names in a virtual column in your Members table, you can use the following AppSheet formula:

SELECT(Slips[Slip Name], IN([Related Boats], SELECT(Boats[ID], [Owner] = [_THISROW].[ID])))

[Suvrutt_Gurjar]

Hi @Uk ,

Thank you for your input. However I believe the part of the expression 

IN([Related Boats], SELECT(Boats[ID], [Owner] = [_THISROW].[ID])) 

will not work as IN() expects the first argument to be a single value element and not a list as in the above expression [Related Boats] is a list.

IN() - AppSheet Help

Also in general using a SELECT() in another SELECT() can be a sync time expensive expression. We typically avoid such double SELECT() expressions.

Instead, it is a better approach to use system generated reverse reference columns as suggested. These columns are anyway generated by the system and are not sync expensive.