New PTO complexity in Coda
Compensation days part II for Belgian employees
In my previous work on paid time-off management, we tackled the complexities of flexible work schedules. We even dove deep into using the modulo operator (%) to address specific scheduling challenges. But solving one problem often leads to another, and today, we’re facing a new hurdle.
Belgian law dictates that national holidays falling on weekends require compensation, meaning employees receive a day off in lieu. However, if someone is already scheduled to work on a weekend day that includes a national holiday, they must take the day off without additional compensation. This presents a unique challenge for our system, which previously granted a blanket day off to everyone when a holiday fell on a weekend, assuming no one was working. Now, we need to refine our logic to account for these specific scenarios and ensure accurate compensation.
It’s fascinating to look back at past blog posts and trace the evolution of a solution. In my post from July 19th last year, I detailed the initial routine for handling holiday compensation when it falls on a weekend. Revisiting that post now, I realize how crucial those notes have become. Without them, I would have spent countless hours trying to decipher the logic behind my own code.
As some of you know, I’m not a fan of excessive in-code documentation. I believe that well-written code should be self-explanatory. If you need a manual to understand your own work, it’s likely too complex. However, I’ve found that using my blog posts as a form of external documentation is incredibly helpful. It provides a historical record of my thought process and allows me to revisit and refine my solutions with fresh eyes.
For example, in my previous code, I filtered for Saturdays and Sundays, which perfectly suited our needs at the time. While it’s possible to optimize the automation to run only on weekends, I’ve decided to keep the code as is. Its clarity speaks for itself, whereas altering the automation settings would require additional explanations.
The above code could be written shorter. Using theNextDate = Today() instead of the shorter .isNotBlank() ensures even those less familiar with Coda's formula language can understand this logic. It's an example of "Clarity over Cleverness" in action, where readable code is more valuable in the long run.
Now, let’s move on to the new challenge at hand: adapting this system to account for employees who are already scheduled to work on a weekend when a national holiday occurs.
This code dives into the heart of our employee data, specifically targeting the CD People table. CD is the abbreviation for Cross Doc and it’s contains all the people working at the organization. We filter this list to identify employees who meet certain criteria, which will become important later on.
The goal is to automatically generate time-off records in our main PTO table for eligible employees. A key detail here is the concatenate function, which creates a clear and informative note for each time-off record. I've enhanced this to provide a more detailed explanation.
Speaking of which, I’ve made use of the FormatDateTime() function, which, although not officially supported yet, is a crucial tool for date manipulation. It works seamlessly behind the scenes, and I'm hopeful that Coda will officially add it to their library soon.
The creation of new rows
Now, let’s turn our attention to the DB PTO Requests table. This was called the HLPR table in my blog post from last summer, but I've since renamed it to reflect its crucial role in our set up.
This is where we’ll focus our efforts to address the compensation issue for people working in the weekend. Our strategy involves a two-step process:
- Generate all potential requests: First, we create time-off requests for all employees who meet the initial criteria, regardless of whether they ultimately qualify for compensation.
- Filter for eligibility: Next, we’ll implement a filter to pinpoint exactly which employees are entitled to the extra day off, ensuring accurate compensation based on individual work schedules.
Summary so far
While the code itself might seem intricate, the underlying logic is quite straightforward. A weekend-triggered automation identifies potential candidates, generates requests, and then applies a filter to ensure only eligible employees receive the benefit.
Filter for eligibility
Testing time-based logic can be tricky! To validate our holiday compensation system without waiting for a real weekend holiday, I devised a workaround.
By creating a “mock” holiday on a Tuesday (which is the day of my testing) and temporarily adjusting the code in a duplicated automation to treat Tuesday as the “weekend,” I could simulate the exact scenario needed for testing. Finding an employee who isn’t working on Tuesday and lives in Belgium provides a real-world test case to ensure the system accurately grants the compensation day.
To efficiently and accurately manage holiday compensation, we’ll focus on the DB PTO table, as shown in the image. This table acts as a central hub for all our date-related actions. Each button represents a specific task, and we'll be honing in on the "Transpose BE compensation days" button. To ensure this button only activates for employees who are off on the weekend of the holiday, I've added a new column that calculates their weekend work status. This calculation will then control the activation of the button, streamlining the process and preventing unnecessary calculations. This targeted approach not only maintains clarity and efficiency but also guarantees that only eligible employees receive compensation days.
While I’m confident this logic will work flawlessly, I’ll be conducting thorough tests over the weekend to be absolutely sure. My testing strategy involves a “fake” employee who works on the weekend, ensuring they don’t receive the extra day off while their colleagues (who are off) do. For this purpose I create a second mock holiday for the upcoming weekend to further validate the logic in a real-world scenario.
Now, let’s peek under the hood of the compensation logic itself. As you can see in the screenshot, it’s quite intricate, involving a series of checks to determine if the current working week aligns with the employee’s individual work schedule. This might seem a bit cryptic, but it builds upon the concepts of the modulo operator and the broader scheduling system I detailed in my previous blog post.
For those interested in diving deeper, I encourage you to check out that post for a comprehensive explanation. But in essence, this code snippet verifies if the employee is scheduled to work on the specific week of the holiday, factoring in their potentially complex work patterns (every third week, every fourth week, etc.).
This level of granularity ensures that our compensation system is both fair and accurate, accounting for the diverse schedules within our organization.
Before we wrap up, there’s one more layer of complexity to address: calculating compensation hours for part-time employees. As I explained last summer, the compensation hours are linked to the number of hours worked on the next working day following the weekend holiday.
For example, if an employee normally works Wednesday, Thursday, and Friday, with Wednesday being a half-day, their weekend holiday compensation would be based on that half-day Wednesday schedule. This means they’d receive half a day of compensation instead of a full day.
To further complicate matters, we need to factor in those complex work schedules based on the modulo operator magic we discussed earlier. If someone works every second or third week, this needs to be reflected in the compensation calculation.
This brings me to a crucial realization: while we can achieve this level of complexity within Coda, it might not be the most practical approach. Pushing the limits of Coda’s automations, buttons, and calculations can make it challenging to maintain an overview of the system and its logic.
The limitations I’m encountering aren’t necessarily about Coda’s technical features, but rather the level of support and documentation available inside the doc to explain the intricate workings of such a system.
But more on that in another post! For now, let’s finish this blog post by telling about our next task: determining the next working day and corresponding hours for weekend holiday compensation. It is again all related to the code snippet we recently developed.
Hopefully, this post has shed some light on this common Coda challenge and empowered you to create more interactive and insightful data experiences. On a personal note, creating these in-depth posts takes a lot of time and effort. While I love sharing my knowledge, a little support goes a long way. If you found this helpful, what about a donation or sharing this post with your fellow Coda enthusiasts? Every bit of encouragement helps fuel the next 51 blockbusters!
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.
