How to Coda paid holiday leave?
Registering paid and unpaid leave
In our previous blog we focused on counting the days per month an employee takes sick leave. Happily most people enjoy paid holidays to stay home and they consume their days over a year in agreement with the employer.
Accumulating paid holidays
The basic business rule is easy. Each year you start fresh with 20 days off. In case you arrive in the new year while you still have not yet consumed paid holidays of the previous year, they will be transferred to this year.
The Main Issue
Let us look into the issue that troubled me for a few days. In the table DB Balance Days I apply an AddOrModifyRows() logic to make it easy for the user to update values by simply pushing a button.
Before adding rows I ask if the year of the month I am working with is already available in the DB Balance Days. First it is not, because I still have to add the rows and second, once added, the years are there. So when I push the button for the second time I may have a different outcome because the calculation deals with the just added rows. This logic — I applied first — results in two possible outcomes and that is not what you want.
Below you see the issue and maybe you see the solution as well. I was so occupied with figuring out the business rules that I had lacked a clear view. This delayed the process of implementing the solution for quite some time. I am explicit about this to show that even when you work daily in and with Coda, once you take the wrong turn, it might take a while before you notice it. It can happen to the best, so don’t feel bad when you worked out the wrong solution. Next time it will go faster.
The solution
The solution is that when we check if a year already exist in this table for this month, we take all the months for this employee and we filter out the month we are actually working on. Simple as that, at least in theory.
The coding part is a bit more complicated. I’ll show you in a parts of the steps I needed to straighten out the calculation. I skip the parts that relate the employees and the dates.
The filter that solved my issue, is a simple one. I did a check on the table that contains the paid holidays (consumed and open) and I avoid to take into consideration the new added months by filtering on last date of the month. The last day of the month you either get via something like AllDatesThisMonth.Max() or via EndofMonth(). Both work fine.
That is it. Well, almost.
Once we have the data linked to previous months, we can start manipulating. Below a part of the code that handles important checks:
The moment I figured out about the filter, it only took me about 2.5 hours to set up the complete calculation. In the above screensheet you see how I split the calculation in different parts and name them via WithName(). This is an efficient way of coding, it also helps to keep the code readable.
Over time I’ll publish a doc in my gallery that will demonstrate how this code works. This doc won’t be for sale. The code snippets shared are part of a more encompassing HRM approach made for Small Businesses all over Europe we serve in their own language.
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.
