Coda notifications based on work schedules
Only notifications on the day you work.
In my previous blog on notifications & getting the right dates we excluded the Saturday & the Sunday to avoid notifcations in the weekend. Put differently, no notifcations on non working days.
That was a relative easy exercise since they won’t change over time. In this blog we look into dynamic work schedules. This means that we have to evaluate the lists of possible notification dates against the work schedules which have two variables:
- Valid as of a certain date, thus changing schemas.
- The weekdays you work (i.e. Monday, Tuesday & Friday) as of this date.
Our example
I created three work schedules for myself and I am my own manager to keep the filter on the employee simple. In a more real world I should take the birthday of the employee and the working schedule of the manager. That is one line of code extra, but does not contribute to the essence of this blog: showing how to extract the right dates.
The first period started in sept 2022, this one will be replaced at Aug 01 and again at Aug 14. Below how that goes:
I work 2 days & 3 days per week. This highlights the question how many days we have to back to find a fitting date. I decided to go 10 days back.
August 15, the birthday is a Tuesday, that day there is no notification, it will be a Monday since the new schedule runs on Monday as of Aug 14. When we go one week back to Aug 8, we see that this is not a working day, the earliest working day is the previous Thursday.
How to get there?
Step 01 — get the corresponding work schedules.
We make use of the logic shown in the previous blog. I converted the date numbers to human friendly dates and so we see that we have two lists. One starting at Aug 15 and one starting at Aug 8.
Both lists (each 11 items) have to be checked for working days and that is what we do in step01. As you can see the the first two items are related to aug 15 and 14 and the rest to the previous working schedule.
The shown logic has been explained in this blog:
Our next step is to get the working days and next a check if the dates in our list are working days.
Step02 — filtering out on the working schedule
In step01 we got the schedules per date. In step02 we filter on the weekdays, these are the numbers related to each weekday. Sunday is number 1, Monday 2, etc.
We take the date ranges and we compare the weekday property of each date with the weekday from the schedules. This looks rather simple and straightforward once you see the importance of the applied order in this set up.
- First the date ranges
- Use these dates to get the schemas
- Filter the date ranges on the weekdays in the schemas.
We finish by adding the filter for holidays. In step03 we get the two dates we need.
Step03 — getting the dates
Once we have the filtered date ranges, we have to take the first item of each date range. This last and maybe simple looking task comes with some complications.
At least we observe that the outcome per list is what we wanted. On purpose I applied the FormatDateTime() function to show the date and the weekday name.
To isolate the the first date in each of the sub lists, I had to see through the wood of brackets, each referencing a list evaluation (ForEach) or a named function (withName). That is not easy. Below the solution.
ListCombine() was needed to merge the outcome into two lists and removes the List() of each dates. Once we had two lists, I asked for the first number and turned that one into a date we can understand.
In the screenshot you also see that I created an additional named function ‘daysBack’. If you do not understand a lot of this code snippet, this concept is maybe still understandable. It relates to the days we go back in time notification date. In our example we took 10 days.
List(thisRow.NextBD,thisRow.NextBD-7).WithName(root,
Sequence(1, root.Count()).ForEach(CurrentValue.WithName(dateNbr,
ToNumber(10).WithName(daysBack,
Sequence(
root.Nth(dateNbr),
root.Nth(dateNbr) - daysBack).
ForEach(CurrentValue.WithName(theDateRanges,
[DB Work Schedules].Filter(
employee.Contains(thisRow) and
fromDate <= theDateRanges).Last().WithName(schedules,
theDateRanges.Filter(
CurrentValue.Weekday().Contains(schedules.workingDays.theNumber) and
CurrentValue.Contains([DB Holidays].TheDate).Not())
)
)
).ListCombine()
).First()
).ToDate()
)
)This was a difficult exercise and shows how heigh the ceiling is in Coda.
When you use Coda to inform others about birthdays and other important dates in your organisation, this is relevant. Maybe you want to include vacation (time off) as a non working day as well. It complicates the code, but you can.
Maybe you also noticed that the code is rather straightforward. This aesthetic component does not guarantee the code is good, but it increases its likelihood. Below the template I used to compose the examples. As always, nothing to copy. You learn by doing.
My name is Christiaan and I support SMB with calculations (budgets and — Human Resource — planning) and I prefer using Coda to get the job done. More about me below.
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.
Coda comes with a set of building blocks ー like pages for infinite depth, tables that talk to each other, and buttons that take action inside or outside your doc ーso anyone can make a doc as powerful as an app (source).
Not to forget: the Coda Community provides great insights for free once you add a sample doc.
