How to check if even one room is already booked in a multiple-room booking system?

[rainnic]

Looking on the web I found a solution that works only for a single Ref room. And here it is:

AND(
[Start] < [End],
ISBLANK(
FILTER(
"Room Booking",
AND(
[Room].[ID] = [_THISROW].[Room].[ID],
[Start] < [_THISROW].[End],
[_THISROW].[Start] < [End]
)
)
)
)

But if I use an EnumList of the same Ref in order to select multiple rooms, I obtain this error:

Column Room in the expression '[Room].[ID]' does not contain a reference.

In fact, the expression has become a list of IDs. But I would like to avoid the booking if even a single room was already been reserved. How can I fix this?

Thank you in advance!

[Joseph_Seddik · Accepted solution]

I'm not familiar with that solution, but I understand the idea.

Yes; this line is a constraint, so you can just remove it and you'll get the list of all applicable rooms. For this use SELECT with [room ID] instead of FILTER. You'll end up with two room lists: your EnumList and the list returned by SELECT, and you'll be able to compare. 

ISBLANK([Your List] - [returned room list]) ---> all rooms available

[Joseph_Seddik]

1. Make a slice of reserved rooms.
2. Check whether the room to be reserved exists in the slice. 

[rainnic]

Thank you @Joseph_Seddik, I have tried, but I don't know how to put the slice in the Data Validity inside [Start] and [End] table. There is a manner to change the single room code [Room].[ID] = [_THISROW].[Room].[ID] into something like a for loop?

[Joseph_Seddik · Accepted solution]

I'm not familiar with that solution, but I understand the idea.

Yes; this line is a constraint, so you can just remove it and you'll get the list of all applicable rooms. For this use SELECT with [room ID] instead of FILTER. You'll end up with two room lists: your EnumList and the list returned by SELECT, and you'll be able to compare. 

ISBLANK([Your List] - [returned room list]) ---> all rooms available

[rainnic]

Thank you @Joseph_Seddik, it works well! Following your suggestions I re-wrote it:

AND(
[Start] < [End],
ISBLANK(
SELECT(Reservation[MultiRoom],
AND(
[Start] < [_THISROW].[End],
[_THISROW].[Start] < [End]
)
)
)
)

[Joseph_Seddik]

Well done! @rainnic 

[rainnic]

Hi Joseph! I am here again with another question: is it possible to exclude the current reservation from the validation? Because if I want to edit and change it, the system considers the rooms already booked and I have to overlap the time.

I have tried to add another condition like: ([Key] <> [_THISROW].[Key]). But it doesn't work. Do you have any idea?

Thank you in advance!

POST EDIT:

AND(
[Start] < [End],
ISBLANK(
SELECT(Reservation[MultiRoom],
AND(
([Key] <> [_THISROW].[Key]),
[Start] < [_THISROW].[End],
[_THISROW].[Start] < [End]
)
)
)
)

  The Key is the row with the ID. Now it seems to work as expected.

[Steve]

https://help.appsheet.com/en/articles/4575802-list-subtraction

[rainnic]

Hi @Steve, finally I have understood your tip and I re-wrote the code like this:

AND(
[Start] < [End],
[End] < [Start]+1,
NOT(IN( [_THISROW].[MultiBuilding],
SELECT(Reservation[MultiBuilding],
AND(
([Key] <> [_THISROW].[Key]),
[Start] < [_THISROW].[End],
[_THISROW].[Start] < [End]
)
)
)
)
)

I have to test it again, but probably now it works as aspected. Thank you!

[rainnic · Accepted solution]

New revision: My last solution works with just one Room/Building at once. If you want to add more and check if they are all reserved or bookable, I have fixed the code in that manner:

AND(
[Start] < [End],
[End] < [Start]+1,
ISBLANK(INTERSECT( [_THISROW].[MultiBuilding],
SPLIT(SELECT(Reservation[MultiBuilding],
AND(
([Key] <> [_THISROW].[Key]),
[Start] < [_THISROW].[End],
[_THISROW].[Start] < [End]
)
), ",")
)
)
)

 Hope it helps and if you have new and more efficient code, you are welcome!