How to Coda automations?

Set automations based on dates, not days.

In Coda the choice is twofold: either you have an automation active based on Row changed or it is Time-based. Once you select the Time-based option, you have a a few additional choices, but none of them is date related.

In this blog I’ll show how a condition can be used to deal with dates. First I repeat what I wrote earlier and second I’ll show how a formula can help you out when your dates are variable dates, living in tables and function as triggers to set something in motion.

Hard coded date logic

First we select all days of the week and second we define the condition to check if that day of the week is good to go. I call this hard coded because if you want to adapt the logic, you have to rewrite a code like below in the automation.

This condition is met on the first of January, April, July and Oct. The months are defined by their numbers, so are the dates. We do not care about the years. This rule is valid any year. Changing the dates means altering the numbers in this condition.

Today().Month().Contains(1,4,7,10) AND Today().Day()=1

Once you see this logic, you can easily play around with it and define other conditions.

My understanding is that coding Coda should remain as simple as possible. Not at least to trace back your own ideas that feel almost self-evident today, but may appear a bit obscure over time, certainly after longer periods.

Archiving rows after 30 days

In this example we want to archive only those rows that are 30 days old. We need two automations. First we AddRows() to the Target Table and once done, we delete them in the Source Table to keep the source table clean.

How to Coda this solution?

My initial idea was to add a condition like below in the “If part”:

What the formula does is checking for each item in the list (thus in the column To Archive) if it matches the date of today and if it does, it starts archiving. And it worked. However, not as I had in mind…

This rule archives ALL the rows because the condition is met. I want to archive only the rows that are 30 days old and not the others.

What I need instead is a filter that takes out the rows to archive and keeps the rest in place. It took me some testing time, but below the kind of code that works.

We create a filtered virtual index via Sequence() and this index is aligned with the filtered outcome we need whereby the CurrentValue refers to the number in the virtual index. The result is that we only have the rows added one by one (possible due to the Nth(CurrrentValue) that are checked, and this happens one time per day (at night at 03AM). As you see below we created a checkbox ‘Archive Date’ in the table ‘DB Found’ that gets checked after 30 days if it is still there, meaning that no match was found. We ‘control’ this value of 30 days via a controller that is placed on the page where the table DB Archive lives.

The order of action in step 3 is:

• Sequence()
• Filter()
• FormulaMap
• Action (AddRows()
• Distribute values over rows via Nth(CurrentValue)

Example of a code that adds selected rows to a new table

The next job is to remove the rows we added. This goes with the next step (4) in our automation using the same condition.

[DB Found].Filter([Checkbox Archive Date].Contains(true)).DeleteRows()

What we have is a time based logic that everyday (at 03.00 AM) checks if there are rows older than 30 days in the table DB Found. If so they are moved to the Table ‘DB Archive’.

That is all and for the simple job I had in mind, it was some real work. Not at least to match the right valus in the different tables.

Why not use a different view?

Why all the hassle with different tables while you can obtain a comparable result with one table that works with different views? In our example you can imagine that the Table Archive becomes a view of the table Found. The filter (shows only younger or older than 30 days) does the job. And deleting data (GDPR) is also possible. The result will be one table with two views and you keep all the data together which is good for reporting:

• The Found view
• The Archive view

Considerations to Automate Add & Delete:

• To keep the docs on speed. We split the tables in two. One with formulas (the source table) and one with only plain text and some images. This will not slow down the doc. Tables without formulas are easier to process than tables with for obvious reasons. The table with formulas will remain small, with only items less than 30 days old and thus fast.
• To keep it simple. This might sound a bit weird seeing the amount of work required to do the set up, but the result is simple to understand. One table with one kind of data and the second one with other data. Views can trouble user experiences in unexpected ways. What you see is what you get with two tables. Also important is that these separated data sets make it easier to set up automations and calculations.
• To keep data safe. In this example it is all rather straightforward. New items and older items waiting for their owners to be collected. However one can easily come up with cases that require a strict split of the data sets. Two tables on two different pages could be a smart way to deal with permissions.

It is a trade off. Will the tables remain rather limited in terms of rows, does it concern sensitive data and are additional actions needed? Views can be a simple solution and are often good to go. Reporting is simpler when data is living in one table.

Extra

For those interested, the DB Archive table itself has another time based rule telling the owner how many identity related papers are in stock each month that should be handed over to the gouvernement. Let’s say the system counts 3 on each first day of the month. With so little, it is an option to wait another month before making the effort to put them in an envelope and post them. Buttons are available to indicate that the identity papers have been posted.

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 Huizer and I am the owner of Huizer Automation. A company specialized in serving SME in harvesting data and keeping it aligned. I mainly rely on Coda, Mailjet, Zapier & Paperform to get the job done.

Huizer Automation on how to Coda date based automations