How to create a relationship between a list of referenced elements and just one in that list

[rainnic]

Following this article, I am trying to create a MultiRoom booking system that allows me to reserve more rooms than just one. An EnumList of rooms let me select one or more rooms into a reservation table.

Now I want to create a reference between the room table and the reservation in order to show a list of reservations starting from a Room. It works if I have just a Ref of one room:

REF_ROWS("Reservation", "Room")

But with the EnumList?

I added this virtual row to the Room table:

SELECT(Reservation[Key], IN([_THISROW].[Key], Reservation[MultiRoom]))

But It doesn't create a correct reference that links a room to the reservations that contain that room. Does anyone have an idea to solve this problem?

Thanks in advance!

[Joseph_Seddik · Accepted solution]

Yes, REF_ROWS won't work here. Instead use this: 

FILTER( Reservation, IN([_ThisRow].[RoomID], [MultiRoom]) )

[rainnic]

@Joseph_Seddik  you saved me again! Thank you very much!

Yesterday, I tried a solution similar:

FILTER(Reservation, IN([_THISROW].[RoomID], Reservation[MultiRoom])

I need to study it deeper, sure for me, Google Apps Script is easier to understand than Appsheet!

[rainnic]

Hi @Joseph_Seddik, recently I added another condition to that filter:

FILTER(
Reservation,
AND(
IN([_THISROW].[Key], [MultiBuilding]),
[Start] >= TODAY()
)
)

Because I want that the app shows me only the future reservations. I don't understand why I can see only 5 reservations when the app counts that I have more than this number for a specific room. Where I am wrong?

Thanks in advance!

[Steve]

The table in your screenshot is an inline view. The number of rows included in an inline view is controlled by a setting in the detail view that contains the inline views:

To view the rows that don't fit in the inline view, click View:

[rainnic]

Thank you @Steve!

[Joseph_Seddik]

Your expression will either return all values of the key column in Reservation table, or none.

In an algorithm concept, you can think of a FILTER() statement like this:

Foreach (Row in Table) do {
  IF (column values in the Row fulfil condition) then {
    Add Key column value of the Row to the Returned List
  } else {
    Skip to next Row
  }
}

SELECT() is similar, but it lets you choose which column value to add to the Returned List, if condition is fulfilled. 

So the idea is to test each Row in the Table against the condition, but your condition is not testing any value of the Row. You are using Table[column], which is a list of all column values in all Rows, where you should instead be using [column]. 

Tell me if you have doubts. Thanks.