Getting the most recent RELATED rows into a slice

AppSheet AppSheet Q&A
1

I have two tables:

  1. WAITING_LIST with columns: waitingListID , memberFirstName , Related VISIBLEs <VC, list>
  2. VISIBLE_LIST with columns: visibleID , userID , waitingListID <ref to WAITING LIST, IsAPartOf>, visible <Y/N>

The intention is to use VISIBLE_LIST to constrain a slice of WAITING_LIST such that if the user has expressed a visibility preference for a waiting list item (ie visible = TRUE) it is included in the slice.

VISIBLE_LIST can contain none, one or many entries per userID per waitingListID. I only want to take the most recent entry for any userID / waitingListID combination (ie row 00B).

My challenge is I can’t figure out how to correctly constrain the WAITING_LIST slice. I’ve tried lots of MAXROW, SELECT, IN, INDEX etc options but they either don’t return a Yes/No or don’t return the desired rows.

Could someone help guide me in the use of Related tables, how to refer to the “child” values and how to select from multiple child values etc. I feel this is core to Appsheet and should be something - after many months of using it - I should understand more clearly… but, it’s just not landing for me. Thanks in advance!

WAITING_LIST

waitingListID memberFirstName Related VISIBLEs
001 John
002 Tom
003 Harry

VISIBLE_LIST

visibleID userID waitingListID visible
00A 001A 001 FALSE
00B 001A 001 TRUE
00C 002B 002 TRUE
2

I think we may need more details on possible combinations of TRUE and FALSE in the VISIBLE_LIST table.

But based on current understanding, you may want to try a slice filter expression in the WAITING_LIST table as follows

INDEX([Related VISIBLEs][visible], COUNT([Related VISIBLEs]))

Assumes [visible] in the VISIBLE_LIST table is a Y/N type column.

3

Again, thank you. This moves things forward.

What is the best way to include other conditions in the slice filter expression? ie such it can only include [Related VISIBLEs].[userID] .

I quickly tried the following to update the Slice, but it didn’t seem to give the expected result (although I’m double checking I have the userID part correct in the USER table). Am I heading in the right direction?

AND(
INDEX( [Related VISIBLEs][visible], COUNT( [Related VISIBLEs] ) ),
IN( INDEX( myProfile[userID], 1), [Related VISIBLEs][userID] )
)

4

Is slice filter of only

INDEX([Related VISIBLEs][visible], COUNT([Related VISIBLEs]))

not working?

5

Sorry, yes - that works fine.
However, my intention is personalise the filter/view to the user.
Your slice filter (correctly) returns the most recent entry from [Related VISIBLEs].
but I need to refine that to return the most recent entry for the current user only.

The VISIBLE_LIST includes [userID] and [visible] columns. I need to refine the slice filter expression to return a row where [userID] = INDEX(myProfile[userID],1) . myProfile is a slice of my user table returning the current user profile only.

For example if I expand the VISIBLE_LIST table a bit and we consider waitingListID = 001…

visibleID userID waitingListID visible
00A 001A 001 FALSE
00B 001A 001 TRUE
00C 002B 002 TRUE
00D 003C 003 TRUE
00E 002B 001 FALSE

For userID=001A, the slice filter should return TRUE (row 00B)
For userID=002B, the slice filter should return FALSE (row 00E)

Hopefully that helps clarify my challenge. I need to include multiple conditions in the slice filter.

6

Oops. User specific slice is definitely additional requirement that we did not discuss before.

May we know how the Waiting_List table ties with the Users table?

7

This generally seems to be in the correct direction.

8

You may want to try a slice expression as below in the Waiting_List table to get the filter to show only logged in user’s record.

AND(

INDEX([Related VISIBLEs][visible], COUNT([Related VISIBLEs])),

INDEX([Related VISIBLEs][userID],1) =INDEX( myProfile[userID], 1)

)

1 Like
9

There isn’t a direct tie between the two tables.
I’m following the guidance on conforming apps to users here.
There’s nothing to link the tables.

10

I think this is just what I’ve been trying to get to… a way to adjust the slice based on several criteria.

Thank you AGAIN for your expertise and generous time.

1 Like
11

You are welcome.