Automation based on a row change by a formula

1

I hope someone will be able to point me in the right direction.

I have been trying to figure out the easiest way to use a row change automation based on a date and time column.
The scenario:
WHEN a date and time in the Project end date column is equal to now() and
(IF) the checkbox is set to true()
THEN a notification is sent.
The checkbox is set to true at the right time with a formula. However, the automation is not picking up the row change. I also tried using a select status instead of checkbox but the result is the same. Is this because the automation is based on a formula?

I found a workaround posted by Shishir from March 2019 that utilizes a button being pushed at a set time each day, which probably will work fine, but I would prefer to avoid this step if possible.

In Airtable I could set up an automation without the use of a checkbox to signify a row change. Is there a way that my scenario can work the way I present it without using a button automation?

Thanks! :sunglasses:

2

Hello @Kjell_Pettersen
My first reaction is that what you want is impossible the way you describe it.
Automations run on a time or row-changed base and the chance of now() being exactly equal to a time in your row is close to zero.
Automations also occasionally fail to run, or don’t run exactly on the hour, so working with a time range is not good either.
If the notification is not extremely time critical (now() gives a date and time) I would use an automation that runs every hour (or every day if that would be sufficient) and check that now() is greater than the time in your row. If that is the case, send the notification and reset the checkbox.
Greetings,
Joost

3

Hey @Kjell_Pettersen ,

@joost_mineur is right about the timing of automation, but you can still do it as you want:

Set up another checkbox in your table with the formula

Now() >= Project End Date

When setting up the row-change-based automation, make sure that you don’t select a specific column as trigger.

The if condition of your automation then should be checkbox1 AND checkbox2.

Best regards
Marius

4

I believe that using now() in a column formula will eventually kill the performance of your doc.

1 Like
5

Thanks, @joost_mineur and @M_Schneider! Fortunately, the function I am looking for is not critical, though it would be great to have an automation based directly on a row’s date and/or time column. The checkbox is switched to true at the expected time, the only issue is that the row change is not picked up by the automation unless it is done manually.
Do you think today() will yield the same result as now()?

I will play around with both of your suggestions. Thanks!

6

Hello @Kjell_Pettersen ,
Today is a lot better, but I would stick to my suggestion rather than having a date() function in a column. If you have at one point hundred or thousands of rows, the function runs that many times every day. I would run the automation and check against a date field and set the appropriate checkboxes as done/completed or whatever it is you are trying to accomplish.
Greetings,
Joost

1 Like
7

Thanks, @joost_mineur, I appreciate it.
Greetings from Ecuador

8

I would consider writing a notification queue as a separate table and then each day have it pull in any notifications that need to be sent and have the automation running against that table. I haven’t tried this yet but I’m pretty sure it can work. And it avoids the notification engine having to spool through your entire table all the time which will probably crash either your doc or Coda’s server farm.