Coda.io & Happy Birthday
Having a mechanism that informs you in time that someone has her birthday in the days to follow, would be great for many people. At least one could timely order chocolate and flowers!
This blog shows how you can use automations in Coda to notify you days before the BD. This blog is not about GDPR and privacy rules related to the question if you have the freedom to share the BD date with colleagues for example. In the Coda blog about BD, GDPR alike rules do not seem to apply. All we need is a birthday date and some related info like the name.
In Coda, most data lives in tables. We created a specific BD table for this example. Most likely lookups towards other HR tables will provide you the basis info you need in the real world.
In my gallery you find a Coda Doc you can copy for free to play with it.
The next Birthday
Birthday related questions appear often in the Coda Community. I provided the age of someone with the formula below:
Today().Year() - thisRow.birthday.Year() - If(Date(Today().Year(), thisRow.birthday.Month(), thisRow.birthday.Day()) > Today(), 1, 0)The next BD is a variation on the above one. While sharing some observations on issues I came across with Joostmineur he helped me out in many ways. First he wrote a variation on the above for the next BD and second he showed me his approach to get the notification logic right. Although I created my own variation and altered the concept a bit, most credits in this article go to Joostmineur
First we need the formula that provides us with the next BD. Day and month always remain the same. It is the year that changes so we focus on the relation Today() and Year(). We need an If statement to ask for one year extra in case the BD is or Today or this year before Today. If that is not the case, we take this year. All together not too difficult once you see the pattern.
Date(Year(Today()) +
If(Date(Today().Year(), thisRow.BD.Month(),thisRow.BD.Day()) <= Today(), 1, 0),
Month(thisRow.BD),
Day(thisRow.BD))Please note that in the EU birthday format we have 03/04 and by that we mean the third of April. When we set up the formula it gives us 4/3 in the part we write the formulas, which is the same, but reads for me as the 4th of March. This standard US notation can be confusing, at least it is for me. As we will see later on, we cannot get a EU notation directly in the notification we want to send out, we need a work around. This issue is not a bug, but a point of improvement for the Coda team which they are well aware off. For the moment we have to deal with it as it is.
Once we have the next BD, it is time to decide how many days in advance we want to have the notification. This requires two small additional steps. We first define the days to go via thisRow.[Next BD]-Today() And second we create a controller above the table where we fill out the number of days in advance we want to see the notification. If you go on holiday for a couple of weeks, you may want to put it on 15 days without going into the formula, while normally it is about 7 days. The number in this controller (which is a filter on the canvas) will be the trigger. We link to this controller via the name of the controller. You can name all your functions on the canvas and reference that name throughout your document. We renamed this controller : Controler_Days To Next BD . You can also define the text you want to show in the search bar and add an icon.
The automation
We activate a time based automation rule to check daily if that day fits the criteria. To do so we select days in the menu. This function runs every day. The time we put at 08 AM, any time is good here.
We do not need any additional condition because the formula that checks the content of the notification, checks also the condition.
The next step is the selection of the users in the document that should receive the notification.
One, a few, or all, up to you.
A text message like “somebody has his or her birthday in 7 days” is not good enough.
We want to mention the person and maybe a few other things as well. Let’s have a look how to get this done
You see in the text above the formula the outcome already mentioned . In the example two people have the same BD. To have a readible notification, we added the Character(13) and Charachter(10) This combination generates a so called CRLF and that stands for Carriage Return Line Feed, thus some white space between the lines. This is an important part I overlooked first since I applied .BulletedList() . This should generate a — as you expect — a bulleted List and adds automatically the white space between lines and it makes the formula shorter, thus easier to read and adapt.
Below how I had it in mind. However when the notification ran around 03.00 AM I noticed that it became one line. So what seemed to work, did not. It is because of this unexpected behaviour I applied the Character(13) and Charachter(10).
[BD Table].Filter([Days To Go] = Controller_Days_To_BD).FormulaMap(Concatenate([full name], " has BD in ", [Days To Go], " and wil be ", Age, " years old at ", [Next BD].WeekdayName(), ", ", [Next BD].MonthName(), " ", [Next BD].Day())).BulletedList() // fails - > instead use Character(13), Character(10)Below the formula. This one you can copy paste in your document to get started. Most important is that you notice the filter that links to the controller. My learning was that the CurrentValue is assumed by Coda when you reference the name corresponding to the days we filtered on. Once you see the filter logic in combination with the FormulaMap, the rest is secondary. Side note: add the Character(13), Character(10) at the start of your Concetenate().
[BD logic].Filter([Days To Next BD] = [Controller_Days To Next BD]).FormulaMap(Concatenate(Character(13), Character(10), Name, " has BD in ", [Controller_Days To Next BD], " days and will be ", age, " at ", [Next BD].WeekdayName(), " ", [Next BD].MonthName(), " ", [Next BD].Day(), ))The date in the notifciation
In the example above you may notice that I do not use the date value of the Next BD. Because if I would reference to the table, the formatted output will be the US ´date format. This is confusing because 03/04 and 04/03 are not the same to me. Normally I use FormatDateTime() to get the date according EU standards. I tried this in the notification, but it failed. Joostmineur was so kind to note that Coda does not yet support this. What is neither possible is adding an image to the notification, only plain text is accepted.
The alternative is something like the above when I use the day and the day name. What I certainly do for French speaking clients is creating a table that links the names of the weekdays and of the months to the French version and use these French names in such messages for most French speaking people get easily confused when reading English.
The Coda doc I mentioned before has also a French version, you can check that one out if you like to see how using a button I permit the user to select the language.
A more advanced option would be to relate the language choice to the notified user.
Long story short. Notifications are a powerful way to communicate with your team. It also takes the pressure off the one that is ‘always asking for’ , you can ask the bot to be your neutral messenger.
BD logic & Meetings
This BD logic can be used for meetings as well. Once you have the meeting moments in a calendar, thus the dates, you can ask the bot to inform every participant some days in advance. No more excuses about forgetting. Simply a message that asks for some preparation related to the meeting. One step further is that the bot also checks the agenda and action points and integrates these in the notification as bullet points. You can even use formulas to tell which user to inform. Instead of putting them manually, we can check if the user is linked to the agenda, thus an active participant. Maybe you have your own scenario you want to work out!
Date related automations offer a wide range of opportunities. Whatever it is you plan to do, always check the format of the date you get. Anything else is merely a matter of loops (FormulaMaps). And as you saw above, that is not too difficult once you see that everything is a list in Coda.
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.
My name is Christiaan Huizer and I am the owner of Huizer Automation. A company specialised in serving SME in harvesting data and keeping it aligned. I mainly rely on Coda, Mailjet, Zapier & Paperform to get the job done. I live in Ghent and serve the BeNelux market.
