Dereference child rows, while also ORDERing them. Better way to do it?

AppSheet AppSheet Q&A
1

Parent: User_Session key session_id, each row is a game session that has 2 related players.
Child: User_Session_Player ref to parent via ref_session_id, each row is a player’s parameters, including a spirit they played, which is a ref to SI_Spirit table via ref_spirit_id

SI_Spirit key spirit_id, contains a link to an image [image_spirit] for each spirit as images/imagename.png

The challenge:

For a table-view of User_Session (the parent) I want to show the image for each character in a column, but for clarity’s sake I SORT alphabetically (by SI_Spirit[name]) so that the first column will always contain the spirit image that is earlier alphabetically.

The problem is currently solved with this code:

ANY(
SELECT(SI_Spirit[image_spirit],[spirit_id]=
 INDEX(
  ORDERBY(
   SELECT(User_Session_Player[ref_spirit_id],[ref_session_id]=[_THISROW].[session_id]), 
  [name]),
 1)
)
)

(INDEX,1) for the first column, INDEX(,2) for the second.

I was hoping to leverage [Related User_Session_Players] but the resulting expressions were either equally or more complex (but maybe more efficient?).

What’s the best way to achieve this kind of lookup?

2

I think your this understanding is correct. [Related…] columns are inherently generated by the system, so it is a better practice to leverage them rather than building expensive SELECT () expressions in a VC.

1 Like
3

Because I have blocked the user from editing the child records directly - they have to edit the parent, which recalcs - I’ve been able to make them physical columns, not VC’s. Regardless, let’s say it was a VC.

If I were to drop the ORDERBY, I still have a problem with this expression:

INDEX(
  SELECT(SI_Spirit[image_spirit],
    IN([spirit_id],[Related User_Session_Players][ref_spirit_id])
  )
,1)

while

[Related User_Session_Players][ref_spirit_id]

yields the correct keys for SI_Spirit, when i try to SELECT [image_spirit] (or any column) based on those keys, I get the entire column of SI_Spirit[image_spirit], rather than just the ones referenced by the keys.

I feel like I need to handle the result of [Related User_Session_Players][ref_spirit_id] differently, even though it appears to be a list of ref keys, as expected.

Any idea what I’m missing?