REF_ROWS question

[MikeJones]

I have 2 tables, "Bookings" and  "Staff"  For the "Staff" table AppSheet has auto generated a list column in the "Staff" view with the following formula  REF_ROWS("Bookings", "Skipper")   ie Show me all the bookings where the Skipper field matches the Staff ID

There is another field on the Bookings table called "Crew"   Is there a way to get all the bookings where either the skipper field OR the Crew field matches the Staff ID

[dbaum · Accepted solution]

There may be more elegant solutions depending on your table relationships, but one basic approach should be:

[Related Booking IDs] + SELECT(Bookings[Booking ID], [Crew] = [_THISROW].[Staff ID])

[MikeJones]

Thanks dbaum ... that pointed me in the right direction .  Turns out I can just use 

REF_ROWS("Bookings", "Skipper")  + REF_ROWS("Bookings", "Crew") 

[dbaum]

And, if you already have another column (e.g., [Related Staff IDs]) with the expression REF_ROWS("Bookings", "Skipper"), then in your new column you can just reference that existing column's value instead of repeating the function's calculations--for example:

[Related Staff IDs] + REF_ROWS("Bookings", "Crew")