How to Coda monthly absence Part II
Registering sick leave and counting employers contributions
In our previous blog we ended with functions to check if the first working or the last working day of a month is marked as sick leave.
In case there a sick leave on the first working day of the month and on the last working day of the previous months and both are not holidays, than consider this period as one.
This rather simple statement presumes quite a bit of work. The code you see I did not write on a lazy afternoon, it took me quite some time. Coding Coda permits for extreme smart solutions. But when the complexity of the business rules increases, so does the work inside Coda. Let’s have a look.
The end of the month
What the below function does is counting the days we need to transfer to the next month. We first take all dates in the selected month and take away the weekends and the holidays. This results in ThisMonthDates
Next we check if there is a sick day that matches the first working day of the month and we name this ‘MonthStart’.
The last code snippet is maybe the most complex. We check if the last working day matches a sick day and if so we bring the practical rule of 3 paid days into play. Since max 3 days are for the employer to be paid we make sure that we only transfer max 2 days to the next month via the part in which we evaluate the last clean period. I guess you have to look twice before you see through in this case and that is fine. Solutions like this require a bit of time to evolve. Initially this function was split, but I noticed that it was easier to bring the calculation (3 minus) into play instead of dealing with it separately.
The result is that per employee we have a number that expresses the amount of days we have to add to the first sick day, in case this is the first working day of the month. We store this number in a table to be used for the next month, in case it is required.
The value is stored in the Column NextMonth you see below in the function we developed to pick up these values in case needed. This function is valid under the condition that the we have two following months. April 22 links with May 22, but not with May 23. We check this via the function first date of this month minus one which should match the last date of the previous month. If there is no value available for an employee last month, we take a zero.
This is only an intermediate step. Before we use it, we want to have all the days related to the periods we found in the this month, but not for the first period (this one we have to evaluate apart).
Let us start at the end. The first period — in case it starts on the first working day — we take away in two steps. We add the MonthStart and turn it into negative number by multiplying it with -1. The MonthStart is number that represents the first working day(s) of any month, thus 1, 2, 3, 4 .
As you see in the screenshot, the MonthStart is 2 days and this number I have to subtract from the total amount of sick days. In this example there is no effect from the previous month and thus the value of MonthStart is the same as the negative number.
The first part of the function counts the periods and asks for the days per period. However I noticed that in case one by mistake checked a weekend day as sick day, it was marked as 1, because it is a period that represents 1 by definition. I had to find a way to get rid of this fake 1 and this is possible by filtering (again) all the dates by ‘ThisMonthDates’ which is a collection of filtered days. The fake 1 got replaced by zero and this explains the filter on greater than zero that follows afterwards. The Listcombine() flattens them out and permits for the integration of the negative number.
I have to admit that I did not notice all these elements directly, it was only after rigorous testing that I had to implement fixes which now feel obvious, but weren’t at the time of creating the solution.
All Sick Days
We are closing in. The last major part before we can bring everything into a table is a complex one. In case someone is sick multiple periods and one or more of these periods it is more than 3 working days, you have to limit the employers contribution to 3 days per period. That is what you see in the first part of the ListCombine() We count the periods that contain values greater or equal than 3 and multiply it by 3. Next we take the sum of all periods for periods that contain less than 3 sick days. Finally we bring into play the first period of the month combined with the last period of previous month.
So here we are, we have all the ingredients to fill out a table with relevant info.
This has been quite a journey.
Working with lists and sub lists is never easy and certainly not when all the data is calculated in a canvas button. However the canvas button is important to keep larger tables fast (100 + employees). That having said, editing in the button becomes difficult. The screen is small and the calculations in the back ground slow down everything you try to do, on top you have auto suggests you don’t want nor need.
It feels like scratching the frontiers of Coda in terms of performance.
Over time I’ll publish a doc in my gallery that will demonstrate how this code glued all together works. This doc won’t be for sale. The code snippets shared are part of a solution developed in collaboration with Jean Pierre Traets and 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.
