Creating a sample in Coda
generate booked and free rooms per day
In my article on the creation of dummy data I focused on getting random turn — over values and a list of dates. This blog elaborates on these concepts and focuses on the question how we can get a table filled out with available and reserved rooms per date.
The creation of a table with dates is — as show — simple and goes fast, see also my previous blog.
If(Dates.Count(),DeleteRows(Dates),Sequence(Concatenate("01/01/2022").ToDate(),Concatenate("12/31/2022").ToDate()).FormulaMap(Dates.AddRow(Dates.Name,CurrentValue,
Dates.WeekDay,CurrentValue.Weekday(),
Dates.WeekNumber,CurrentValue.WeekNumber(),
Dates.WeekdayName,CurrentValue.WeekdayName())))
Once we have a table with dates and in my example it covers a year, we need to find a way to have an overview of reserved and available rooms. The solution I propose is rather basic, it is not a higher level of coding Coda and I bumped into it almost by coincidence.
The idea is simple. You create a table with dates. Next you create a new column and you use the value for new rows. In this column you reference a table that contains rooms.
The DB Rooms has 4 rows, the last is an empty one, this one we need to have a room that is not yet reserved. I got it empty based on a checkbox as a condition for “ ”.
Well these 4 rows we need in our formula that lives inside the new value for rows and this formula has a function that per row randomly selects a room, including the empty one (row 4).
Once we have a column filled out with this information (room 1, 2, 3 or none — 4 ) we create a second column by duplicating te first one and we select the option: Duplicate as empty column.
What happens next is a bit of magic. Due to the function living in the ‘value for new rows’ the column gets filled out with rooms. Since we have 3 rooms we repeat the trick. Once we have three columns we bring the data together, see below:
We filter the list of rooms on two criteria. First we get rid of the blanks to avoid the ugly comma that you get when you only work with a list and <-Unique(). Next we ensure we have only unique values.
I checked if it was random enough meaning that each time the distribution should be about 25% and that seems correct. It also makes it possible to have days without any room reserved; this time I have two which is on the low side.
The result is a table we can use for further experimenting with reservations based on people per room and date ranges. I am not sure yet how to proceed with a booking system, but I am confident that once I allow myself to let ‘it happen’ solutions will appear without too much effort. This practice blends deep focus with a relaxed state of mind trusting my inner capabilities.
A second option
I needed some larger data sets and did not like the partly manuel way as described above. I had a look at the solution defined by Max and applied it in a table, together with a second column. Once set up, this goes very fast. I created more rooms and executed the below.
Second via Slice(3,3) I got the first number of out this random number and used it in my formula to define the amount of rooms. Note that first the column type of rooms is text and after the rooms are created, I turn it into a lookup and I allow for multiple selections.
The random number generated via the Max Method is more random than via the one below using RandomInteger() .
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 am mainly 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.
