I’m trying to figure out how to track inventory of bedrooms in a conference center so that when you add a group (booking) for X number of rooms for a date range, the stock of rooms for a specific date is reduced. Essentially I’d like to be able to look at a particular date and see how many rooms are available and how many will be occupied. Basically hotel room management. Wondering if this is too complicated for Coda?
It should be fine, just depends on the level of depth you need to go into, and what views you need to be able to see of the data.
I knocked up a super quick example for you which helps to show what kind of thing Coda can do:
Thanks. I’m trying to flag a booking if any one of the dates in the booking range will put me over the inventory of rooms.
See the “Guest Services” view. Right now I can select a date to see the sum of rooms booked on a particular day, and reservations active on that day are highlighted in blue. I’d love to be able to have conditional formatting that highlights an entry in the table when any of the days in the reservation range puts the sum of rooms occupied over 50.
This is a bit of a weird solution, but have a look at this:
In a new “Configuration” section, I’ve added two things:
- A MaxRooms variable (50)
- A giant table of every calendar date (in your real environment you’ll add a few years of dates - tip: use a spreadsheet app with Fill Down functionality to quickly generate them all and paste in)
This table keeps track of the total rooms scheduled on each calendar day. There’s also a checkbox that gets checked when the day is over capacity.
Then, back in the Events table, we do conditional formatting based on “Are there any dates in the date range of the event that are over-capacity”?
1 Like
This is awesome, thank you. Now I’m having another formula issue in conditional formatting. Need to indicate which lines in the Events table have attendees that are more than the capacity of the public spaces selected. Capacities are noted in the Public Spaces table. It can filter fine using the string “Library” but not when I try and have it match the public space selected in the Events table.
So, try this - [Public Spaces].Capacity.Sum() or [Public Spaces].Capacity.Max() to get the sum or max capacity. Let me know if this is what you are looking for.
I don’t want the sum of all the room’s capacity, but a specific room designated by the “Public Spaces” column in the Events table.
This formula works:
Which Room in Spaces Booked do you want to pick for conditional formatting when you have two or more of them?
The one with the lower capacity (i.e. if a group wants to use two spaces, they must be able to fit into the smaller one).
Trying to understand why I could write out “Library” in the formula but it wouldn’t work when it referenced the “Space Booked” variable which had “Library” as a value in some rows.
When you say Space Booked then the result is a list ( with one or many items) and it is hard to compare a value to a list.
SpacesBooked.Capacity.Min() will return the space with Min capacity.
For the comparison between the list and the text, can you use in()?
Something like
PublicSpaces.Filter(In(thisRow.SpaceBooked.Room, Room))
Which based on my understanding means filter things where the room name from Public Spaces table (“Room”) can be found “in” the list of spaces that are booked (“thisRow.SpaceBooked.Room”).
Untested at this point, just hoping to put you on the right track. When sketching in the embed above it seemed to work with single-selection situations but not multi-selection? Maybe @mallika can shed some light.
Edit: or Contains()? I can never keep straight when In() and Contains() are appropriate