Coda paid time off (PTO) — 8.0
Getting sick during your holidays
Sick days during vacation can be a headache for HR. In this post, I’ll show you how to simplify the process of canceling holidays, adjusting balances, and recording sick leave — all starting with the multi-purpose form below.
We’ve kept things simple. A single form handles multiple scenarios, and all requests are directed to the same table for streamlined processing.
Without intervention, this is what happens.
The sick leave has been processed correctly, but the overlapping holiday period needs to be canceled. How can we best achieve this?
We first need to cancel the overlapping holiday period. To do this, we’ll add a new row for each affected date. This row will have “holiday” as the PTO type and indicate a cancellation. The canceled time (half or full day) will be added back to the employee’s balance using our existing button.
We’ll use two filters: one to identify overlapping dates and another to isolate the holidays — if there are any — within that overlap. This filtered data will be used to generate new rows with adjusted dateEvaluation and balanceType values. See the example below:
All you need is a filter
Let’s say you book a holiday, cancel part of it, then fall ill during the remaining days and even beyond. These real-life scenarios showcase the power of our table setup. In the example below, only August 28th needs correction. The existing cancellations for August 26th and 27th, even the one for an unbooked holiday, won’t impact the balance thanks to our error handling.
The core of our logic lies in Coda tables’ ability to create a new row for each date and request type. This empowers us to compare dates, request types, and our date evaluations (e.g., normal booking, double booking).
Our code snippet below demonstrates this. Here, we only need to check the latest status for each date. If it’s a normal or double booking, we have something to adjust. This is slightly simpler than our previous exercise, where we compared the last two values — an approach that ultimately guided us to this solution.
Initially, we check for active bookings and any overlap with sick leave. Then, we filter for the rows needing adjustment and add new rows to modify their values (duplicating a row doesn’t allow this). Note the ‘root’ variable, which we use to update the values associated with the current record.
The final step — press the buttons
We have the button in the main table that transposes the date range into dates per row in our PTO request table. This button gets pressed by an automation after a form is filled out.
At this point in the process, we don’t have the precise row reference needed for btn02. As a workaround, we trigger all buttons, but only those with matching conditions will actually execute. This is primarily determined by the internal calculation within each button, not just its active/disabled visual state. The disable functionality serves as a helpful visual confirmation that the intended actions have been carried out correctly.
An issue with the balance:
I noticed a potential problem while reviewing the balance calculations. It’s reminiscent of the classic story about the bank employee who exploited rounding errors to siphon off tiny amounts of money, accumulating into a significant sum over time.
Similarly, our current setup could lead to small discrepancies. For example, canceling a full day after booking a half-day off results in a net gain of half a day.
This is something we definitely need to address in a next blog.
I hope this article shed some light on the topic and helped you solve a tricky problem! If you found it valuable, consider a donation to support my work.
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
