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!
Solved! Go to 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
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!
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?
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
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]
)
)
)
)
Well done! @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.
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!
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!
User | Count |
---|---|
37 | |
30 | |
29 | |
20 | |
18 |