Time based automations in Coda
How can I configure my doc to support me in keeping track of my monthly and yearly payments? I was looking for a system to help me to avoid automatic renewals of notebily yearly subscriptions. What if I could send myself myself each quarter an email to tell me when the next payment was due and for which amount. Based on this info I could check if I still need this subscription.
My basic idea was simple:
- Show the renewal date for each subscription
- Indicate yearly or monthly and the amount
- Define next payment date in the overview
- Define alerts like 7 days before a monthly payment and 21 for a yearly one.
This did not work
Initially I created a table with the above mentioned data. Besides I had a second table with a button. This button was linked to my Gmail Pack and got activated each quarter, thus January first OR April first OR July first OR Oct first. I used a simple formula asking for the day (always the first) AND for the months if 1,4,7 or 10. See below the If() I wrote to trigger the button.
If(thisRow.[date of today].Month().Contains(1,4,7,10) and thisRow.[date of today].Day()=1,True(),False())I tested the set up with fake dates, the checkbox went on, the automation ran and the email was sent during the testing phase. To be sure I put the check on the day after and I expected an email in my inbox the next morning. Nothing had happend.
I asked for advice ín the community and got the feedback that automations like this won’t work for certain reasons. I assumed it was game over for this kind of time based automation.
This kind, because Coda offers time based rules for automation. You can select the day (not the date) and the hour. This combination then provides rules like each Monday at 13h. Though interesting, this was not what I needed. I wanted each first day of any fresh quarter.
I got it working. Below the configuration for Today (Feb 17, 2021).
This works
Tim Burns provided a simple solution for this issue by taking a look at the IF() function.
If we just need to trigger an action the first Monday of the month (vs. enumerate all first-Mondays), isn’t it as simple as adding a Time-Based Rule to run on Monday and then a check if it is the first 7 days of the month:
Once we see this condition (the IF())as our friend, we have a robust set up for any time driven solution that is out of the ordinary, thus differs from the proposed hours and days.
Before we enter the If() we first put all days as active and select an hour. All days because the dates we have in mind can be on any day. The hour is according your preferences.
In our case the first day of every quarter we simply pick up the previous formula and modify it a bit. See below.
Today().Month().Contains(1,4,7,10) AND Today().Day()=1This is only True() 4 days a month and once True() the button is pushed that is responsible for sending the page with the up-to-date information. And so it goes.
If we want only to set a different time (like 13h31) this is not possible. The round hours we standarly can select do the job.
After I wrote the document I created a rule to send myself an email and as expected, it came in. But not after I had to fix an unexpected issue. The test gave the result I was looking for so when I saw the error below, I was surprised. By the way, I only saw this message after hovering over the error.
The email came once I created the action (send email) as part of step 3. Previously I had one extra step — push button — and this logic did not work. We now have a confirmed working flexible solution for sending pages with up-to-date information on any date or selection of dates.
This logic is important for bookkeeping and related tasks.
I hope you enjoyed this article. If you have additional questions, feel free to reach out. Though this article is for free, my work (including advice) won’t be.
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 AirTable, Coda, Mailjet, Zapier & Paperform to get the job done.
