Checking for open slots in Coda
Apply advanced filtering via options inside relations
In my previous blog post about meeting rooms, I shared some code snippets that output a list of available time slots. Each time slot is represented by a list with two elements: the start time and the end time of the room’s availability. For example, if a room is booked on Monday, November 4th between 9:00 AM and 10:00 AM and again between 3:00 PM and 4:00 PM, the open time slots would be: midnight to 9:00 AM, 10:00 AM to 3:00 PM, and 4:00 PM to midnight.
This is the code snippet that generates the list of open slots you see here:
[DB Reservations].Filter(room.Contains(thisRow.room)).Sort().WithName(root,
ListCombine(
root.startDate, root.endDate).Sort().WithName(theDates,
Sequence(2,theDates.Count()).ForEach(CurrentValue.WithName(nbr,
SwitchIf(
theDates.Nth(nbr) - theDates.Nth(nbr - 1) > 0,
List(theDates.Nth(nbr - 1), theDates.Nth(nbr)))
))).WithName(pairs,
Sequence(1,pairs.ListCombine().Count()).ForEach(CurrentValue.WithName(nbr,
SwitchIf(
[DB Reservations].Filter(
startDate.Contains(pairs.Nth(nbr).First()) OR
endDate.Contains(pairs.Nth(nbr ).Last())
).IsBlank(),
List(
pairs.Nth(nbr).First(),
pairs.Nth(nbr).Last()))
))).Filter(CurrentValue).WithName(periods,
SwitchIf(
root.Count() = 1 and periods.IsBlank(), // only one slot on one day
List(
List(Today().ToDateTime(),root.startDate.ToDateTime()),
List(root.endDate.ToDateTime(), [DB Dates].Name.Max().ToDateTime())),
periods.IsBlank(), // no slots at all
List(Today(), [DB Dates].Name.Max().ToDateTime()),
// all the rest
List(
List(Today().ToDateTime(), root.startDate.First().ToDateTime()),
periods.ListCombine(),
List(root.endDate.Max().ToDateTime(), [DB Dates].Name.Max().ToDateTime())
))))What I didn’t show you was how to filter these open time slots to find specific availability. That’s exactly what we’re going to explore today! This is an advanced topic that delves into filtering using relations and requires a good understanding of the Coda formula language. If you’re a beginner, this might be a bit challenging, but stick around — you might learn something new!
Some people wonder why I share all these insights for free. The truth is, even with all the information I provide, becoming a Coda expert takes more than just this type of knowledge, but that’s a topic for another blog post. For now, let’s dive into the logic of open slots and how to use them to check room availability.
Step by step
Let’s say we’re looking at a room with various time slots booked on November 11th. To find available times, we’ll first retrieve all the open slots for that room (assuming you’ve already calculated them using the method from my previous post, see the code snippet above). Then, we’ll dive into this list of lists and analyze each time slot step-by-step to see what’s available on November 11th.
below the table with reservations and you can see we have two slots booked on November 11.
Step01 — counting the open periods
First, we’ll examine the available time slots for this virtual room. As today is November 4th, the earliest available booking date is today. Tomorrow, the earliest available date will be November 5th, and so on.
You might notice that the available slots are grouped into three larger sets, indicated by the square brackets. Essentially, we have a list of lists.
We can identify three distinct lists. To process each list, we could assign them index numbers (1, 2, and 3, as shown). However, this indexing isn’t necessary for our purpose; I’m simply highlighting it to contrast it with the next step.
Step 02 — counting the individual slots
Next, we’ll use the ListCombine() function to flatten the list. This combines the three sub-lists (containing 2, 8, and 2 items respectively) into a single list with a total of 12 items.
Step 03 — checking for the date
With all the time slots in a single list, we can apply the ‘from-until’ logic. Each odd-numbered item represents the start time of an available period, and the following even-numbered item represents its end time.
To determine whether to display the full 24 hours or just a portion of it for a given day, we need to check if that day’s date is included within our 12-item list. This allows us to accurately reflect the available time slots.
In programming terms, we can conditionally determine the display of time slots. If the selected date is present in our list of available time slots, then we’ll display only those specific portions of the day that are open. Otherwise, if the date is not found in the list, we’ll display the full 24 hours.
As you can see, this logic follows a typical ‘if’ statement structure, which we’ll implement in what follows.
We begin by implementing a simple check to handle the core logic of our framework.
Following this, we’ll incorporate the start and end times of the day to complete the functionality.
With these date specific time slots in a single list, we can apply the ‘from-until’ logic.
We’ll revisit and refine this starting point later in the blog. For now, let’s move forward with the current implementation.
04 — using an index of odd numbers
This is where things become slightly more complex. Up to this point, we’ve been working with advanced filtering, which was relatively straightforward to follow.
Now, we need to evaluate the time slots to essentially say: ‘It starts here, it ends there, and give me everything in between.’ To achieve this, we’ll create a virtual index. We’ll generate this index using a sequence with a ‘by’ option, incrementing by 2.
The beauty of this virtual index is that it creates a list of odd numbers. Remember we had six time slots? Normally, you’d number them 1 through 6. But by incrementing by 2, we get 1, 3, and 5. This is key because it lets us pinpoint the gaps in our time slots.
Think of it like this: each odd number in our index points to the start of a time slot. By adding 1 to each of these numbers, we find the end of that time slot. So, 1 plus 1 equals 2, which corresponds to the end of the first time slot. Then, 3 plus 1 equals 4, marking the end of the second slot, and so on.
This clever indexing method allows us to easily identify the start and end points of each available period within our complete list of time slots.
05 — filtering the list of time slots
Ideally, we’d directly link time slots to the date — time value type (the column type) Coda offers, but there’s a catch. When using a date-time format, you can select any date as many times as you like, but you can’t easily connect to previous or next time slots within that same list. This is because the list itself isn’t designed for that kind of relationship.
To overcome this, I created a separate table specifically for times, similar to what I did for dates earlier. This table, called “DB Time,” contains all time slots from midnight to half an hour before midnight, in half-hour increments (e.g., 1:00, 1:30, 2:00, and so on).
Now, with this table in place, we can use our virtual index to pinpoint the time slots we need. We do this by creating a filter that essentially says: “Give me all the times that are greater than or equal to the time slot indicated by the current index number.”
Here’s where it gets a bit tricky. Because of the way the data is structured, we have to explicitly specify that we’re working with time values, even though the color-coding in my system already suggests this.
To capture the full ‘from-until’ range, we actually use two conditions in our filter. The first condition, as mentioned, grabs all times greater than or equal to the time slot from our index. The second condition does the same but uses the next number in the index (index plus 1) to get the end time of the slot.
This might seem a bit convoluted, but it’s how we apply the ‘from-until’ logic we established earlier.
And with a minor tweak I had to make at the beginning, it works perfectly! We’re almost there!
06 — Fixing the date range for today
In the final stage of building this system, I encountered an unexpected hurdle. Everything worked perfectly until I reached the last time slot, which extended past midnight into the next day (12:00 AM). Despite trying various solutions, I couldn’t get it to display correctly.
It dawned on me that the issue stemmed from how I defined the end of the day. Instead of treating midnight as the beginning of the next day, I needed to consider it as the very end of the current day.
To fix this, I had to revisit the initial step where I created the list of time slots. I adjusted the last time slot from midnight to 11:59 PM — just one minute shy of the new day. This seemingly minor change was the key to resolving the issue and ensuring all time slots, even those spanning two days, displayed accurately.
It was a tricky and somewhat frustrating problem, but ultimately, I found a solution. This experience highlights how even small details can have a significant impact and emphasizes the importance of thorough testing and careful consideration of edge cases.
07 — no need for an extra If statement
The elegance of this solution lies in its ability to seamlessly handle both scenarios: days with bookings and days without. While it might seem like we need separate logic for each case, the formula itself cleverly adapts to the situation.
The formula inherently recognizes whether there are bookings for a given date. If bookings exist, it automatically applies the ‘from-until’ logic to pinpoint and display the open time slots. If no bookings are found, it gracefully defaults to showing all available times.
This streamlined approach eliminates the need for explicit conditional checks within the formula. It adapts dynamically, providing a concise and efficient solution that enhances the overall elegance and usability of the system.
08 — The last time slot
Our focus so far has been on identifying any available time slot on a selected date. For instance, if we take November 11th as an example, and there are a few meetings already booked, our system allows us to pinpoint all the open slots.
Imagine you want to schedule a meeting at 5:00 PM. You could potentially book it until 10:00 PM if that slot is free. However, if there’s another meeting scheduled at, say, 6:00 PM, you can only book until 6:00 PM.
The initial approach of simply selecting all time slots after the starting time won’t suffice for this scenario. We need a more refined strategy to accurately determine the end time.
It might seem obvious to us that we have two booking options starting at 5:00 PM, but how do we make the computer understand this?
First, we gather all the available time slots for that day. Then, we create a virtual index — a kind of helper list — using a sequence that starts at zero and increments by two. This gives us the numbers 0, 2, and 4.
These numbers act as pointers to the beginning of each time slot. Zero points to the start of the first slot, two points to the start of the second slot (1:00 PM), and four points to the start of the last slot.
Now, we have something to compare against! We take all the time values from our ‘DB Time’ table and apply two conditions:
- Show us all the times that are later than the starting time we’ve already chosen.
- Show us all the times that come before the very next time slot (the first available one).
This two-step process effectively pinpoints the end time of each available booking slot. And that’s precisely what’s happening in this function!”
Conclusion
In this blog post, we explored how to identify open time slots on a given date. We examined the complexities of determining both the starting and ending times of these slots. A crucial step in this process was creating a list of all available time slots for the specific date. Once we had this list, we could strategically decide how to navigate and evaluate its items.
We demonstrated the effectiveness of using a ‘by 2’ increment to pinpoint relevant time slots. My preferred method involves focusing on the starting time logic. I find this approach (theOddIndex + 1) to be both elegant and efficient.
I hope you gained some valuable insights from this exercise, as I certainly did. While documenting the process in detail, I realized I overlooked explaining how to create the start and end dates. This aspect, while seemingly simple, presents its own set of challenges. Perhaps we’ll delve into that in a future post.
I hope this article was informative and helpful. Did it help you to solve a problem you unlike would have solved other ways? What about a donation?
My name is Christiaan, and I regularly blog about Coda. While this article is free, my professional services (including consultations) are not, but I’m always happy to chat and explore potential solutions. You can find my free contributions in the Coda Community and on X. The Coda Community is a fantastic resource for free insights, especially when you share a sample doc.
