Create a calendar view when all rooms booked

[Germán]

Hi! I'm a new user of appsheet, I recently find about the existance of appsheet when I started to need a booking app.
It's really easy to start and i have accomplished severeal easy features but i can't achieve this:
I want to create a calendar that I can share where it shows availability but not room by room, I want an event to be created only when all rooms are booked. I tried different approaches but I can't combine the different dates.

room[RoomID]
room[Room]
room[Capacity]

reservation[reservationID]
reservation[Check-in]
reservation[Check-out]
reservation[Nights] VC
reservation[Room] Ref
reservation[Guest] Ref
reservation[Check-in time] VC
reservation[Check-out time] VC

guest[]... various data guest

[WillowMobileSys]

---

@Germán wrote:

but I can't combine the different dates

---

I am not sure what you mean by this.

To confirm if all rooms are booked, I would recommend using the INTERSECT() function and compare the list of reserved rooms against all available rooms.  If the intersection is empty, then all rooms are booked other wise they are not.

The expression might look like this:

INTERSECT(Room[RoomID], Reservation[Room])

If you are checking if all rooms are booked on a certain date then the expression might be like this:

INTERSECT(
          Room[RoomID], 
          SELECT(Reservation[Room], AND([Check-In] <= [Compare Date], 
                                        [Compare Date] <= [Check-Out])
          )
)

NOTE:  The explanation and expressions are just examples.  You may need to change some of the details to make it work for you.

If your intention is to check if all rooms are booked at anytime during some new reservation entry, this will be a little more challenging.  You cannot perform the COMPLETE check on the fly while filling out the Form.   It will need to be broken up into parts.

What I would recommend is pre-determining if there are any Dates where all rooms have been booked.  I would probably do this using a Bot that runs after saving each Reservation.  The Bot would check each date of stay in a loop, using the second expression above,  to find out of all rooms are booked on a certain day.  Each date found that is  fully booked would be saved somewhere in your app.  

As users are entering a Reservation in a Form, the app can check the dates of stay against the "Unavailable Dates" list (again INTERSECT() can help here).  If any of the Unavailable Dates occur during the reservation period, then "No rooms available"!

I hope this help!