Multiple Rooms in Coda → Part 1
Show only the rooms that fit your criteria
--
In our previous blog we focused on the date range and getting the available rooms to find a room that fits one criteria: the amount of people.
Multiple Rooms
Imagine that you want to book a room for 5 people. In case we have rooms for 2 and 3 or two rooms for 3 we have a solution. We humans notice this instantly. With only 3 rooms, we might not need an advanced system. This changes when you deel with multiple variables like availability, capacity and price. As of a certain level computers do a better job.
To help humans (read myself to check the outcome) I sorted the available rooms. As you see we do not sort on the Display Column (which is the standard sorting) but on a related column, in our case Size. It is a little trick that comes in handy in this case.
Finding the pattern → positions in a list
Initially I did what I do most, I looked at the puzzle and ‘let it happen’. This works very well for puzzels I am familiair with. This time it is different. I took pencil and paper to really understand the pattern as it is and not as I thought it should be. I am explicit about this because I noticed I applied techniques that worked in a different context very well, but failed here. I had to push my reset button really hard to allow myself to start from scratch.
The required algorithm should handle all scenarios and not only the easy ones (one room or all rooms).
Frankly speaking, I spend far too much time on finding an alternative for an already existing solution Joostmineur pointed out to me and which is written by Connor McCormick. First I assumed it was too heavy for my use case, but after wasting enough time I looked again and saw it’s great value. Merci Connor!
The first presented solution in his doc shows all combinations of a list, including the combination of an item with itself. This is more or less what I needed, but not exactly. I had to get rid of self referencing combinations like when looking for two rooms ‘room 1 and room 1’. I did not see the solution directly (although it is present in the doc …) , but happily Federico Stefanato helped me out. I looked again in the doc and noticed that the final result is what I need:
The Connor McCormick Solution
It is complex and not easy to follow but meanwhile brilliant, robust and fast. Speed matters. I tested it on a table with 365 days and 10 rooms in multiple variations (Random, using the Max Method). You don’t have an instant result, but still, it goes fast and by filtering on a day range, we can improve speed even further (next blog).
Anyway, it was only after finding a working solution I could see the logic for what it is and again, it is impressive. The basic steps are calculating all combinations using Sequence(), Power() and Log() and filtering out the combinations you do not need, like the doubles I mentioned. This art work solves many hard problems at once.
Applied to my challenge to get all available rooms it became the function as you see below in color.
The code part you can adapt according your specific needs meaning replacing the text boxes (TB_ variations) and the thisRow (green parts). It might take you 5 minutes but saves you a ton of work. I improved the code related to filters, but that is — sorry for me — only a fraction of its value.
Sequence(Power(thisRow.AvailableRooms.Count(), TB_Rooms.IfBlank(1)), (2 * Power(thisRow.AvailableRooms.Count(),TB_Rooms.IfBlank(1))) - 1).FormulaMap(CurrentValue.WithName(Number,If(CurrentValue = 0,0,Sequence(Log(Number, thisRow.AvailableRooms.Count()).RoundDown(), 0, -1).FormulaMap(CurrentValue.WithName(Step,RoundDown(Number / Power(thisRow.AvailableRooms.Count(), Step)).Remainder(thisRow.AvailableRooms.Count()) + 1)).Slice(2)))).FormulaMap(CurrentValue.Unique()).Filter(CurrentValue.Count().Contains(TB_Rooms) And CurrentValue.Sort(True).Contains(CurrentValue)).FormulaMap(CurrentValue.FormulaMap(thisRow.AvailableRooms.Nth(CurrentValue))).Filter(CurrentValue.Count().Contains(sequence(0,TB_Rooms-1)).Not() And CurrentValue.Size.Sum() >=TB_People and CurrentValue.Price.Sum() <= TB_Price)
I ask for all combinations and at the end I filter for the amount of people, the rooms and the budget. Of course you can filter for much more like side by side rooms (for families), beds for children, a king size bed, etc. It is all a matter of having the data available in the main table, the filters do the rest. The more you filter out and thus the less you show on the screen, the faster it goes.
Practical Solution
It is one thing to get the data right, it is something else to present it in a way a user can move on with it. I created the view below to make it easier to decide.
This was not so easy and I am glad I can share the logic behind it. We use the code part I showed above. Via WithName() we call this Base. From here we start templating as explained in this blog.
The combination of Format() and FormulaMap() only works because List() holds the Base values together. The rest of the work is not easy — peasy, but well doable if you are proficient with Coda and you see how we use the Nth() and fill it out with the outcome of the virtual index we created combining Sequence() and FormulaMap().
Well, here we are at the end of this blog. We have a solution for finding matching rooms and we can present it. In our next blog we have a closer look at how to book rooms and how to filter on following days to ensure that you can have a room for a few days in row. Filtering on a period (using date pickers) will also show to be relevant to keep the table fast. The smaller the data set the less time your computer needs.
I hope you enjoyed this article. If you have questions feel free to reach out. Though this article is for free, my work (including advice) won’t be, but there is always room for a chat to see what can be done. Besides you find my (for free) contributions to the Coda Community and on Twitter
My name is Christiaan and I support SMB with calculations (budgets and planning) and I prefer using Coda to get the job done.
Not to forget: the Coda Community provides great insights for free once you add a sample doc. Paid consulting is often not the way to proceed.
