Happy Birthday Notifications in Coda.io

Coda.io & Happy Birthday

Christiaan Huizer
Mar 20, 2021 · 8 min read

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

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),

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.

Add a Control to your BD Table

The automation

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)
formula to trigger the notification(s)

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

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.

Tables to communicate in the language of your client

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

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.

Huizer Automation on how to automate birthday reminders in Coda.io

Nerd For Tech

From Confusion to Clarification

Nerd For Tech

NFT is an Educational Media House. Our mission is to bring the invaluable knowledge and experiences of experts from all over the world to the novice. To know more about us, visit https://www.nerdfortech.org/.

Christiaan Huizer

Written by

Coda Expert | Huizer Automation

Nerd For Tech

NFT is an Educational Media House. Our mission is to bring the invaluable knowledge and experiences of experts from all over the world to the novice. To know more about us, visit https://www.nerdfortech.org/.