Create an automation based on Time is NOW?

Scott_Heliker · ‎Mar 30, 2021

I am looking to set up an automation based on when my date and time field are NOW
Does not look like the automations date filter allows for NOW or takes formula code on it so how could I use a calculation field to look for any date that is now then tick a box or mark another field as True so that I can then base my automation filter off a column marked True?

Not sure of the code that when time is NOW mark this fields as True etc…?
Tried this but now working
IF({Due Back Time Reminder} = NOW(), “True”, “False”)

bitgiggy · ‎Apr 01, 2021

I did something very similar so I think I can help.

Firstly, I added a new column where I inserted the following formula:

IF(NOW()>{Posting Date},1,0)

This will display a 1 when the current date/time (NOW) is greater than the posting date (or rental due back in your example). You can then set up an automation based on this calculated field instead. For example, executing the automation when the field is equal to 1.

The reason you have to input NOW()> is because the NOW() formula does not update in real-time. If you only include NOW() in your formula, your automation probably wont fire as you’re relying on Airtable updating at the exact time your field is set to.

My automation now works flawlessly, so let me know if you need further help.

Scott_Heliker · ‎Apr 01, 2021

Very nice. So this will only run one time when it changes to the number 1 correct?
Couple questions

Do you always have to refresh the screen to see this change? I noticed I had to do a hard screen refresh and wanted to make sure you did not have to be editing the screen and refreshing iit to make it trigger.
Is there a way to get line breaks in the return I get from my mail hook?

Here is the whole setup. Thank you very much for the help.

Scott_Heliker · ‎Apr 01, 2021

So from testing looks like I will have to adjust my time about 10 min minue because it takes about 10 minutes for the 1 to register in the celll and receive my automation.

Do you have a suggest for empty rows and if you are manually adjusting a time I noticed it switched the calculation field to 1 while you are editing the time on the same day as today and seems to have sent the trigger when you adjust. It does not wait for you to get done so swiitches the field to 1 and then back to 0 when you are done.

Thanks.

ScottWorld · ‎Apr 01, 2021

As @bitgiggy mentioned above, unfortunately, NOW() will never update in real time.

As you discovered, if you’re actively using your database, NOW() will update every 10-15 minutes.

If you’re not using your database, Airtable will only refresh NOW() every 1-2 hours. It is also very unreliable. It often goes 24-48 hours without updating at all. I’ve been running daily tests on this for months now, so I have the sad data on this. Sadly, it can not be depended on.

If you need accurate timing, I would turn to Integromat, which it looks like you are already using. Have Integromat run on a schedule and search for the records that you need to take action upon.

Airtable also recently introduced running Automations on a schedule, although you typically need to write your own custom JavaScript to handle Airtable’s scheduled automations. (If you’re not a coder, Integromat is the better solution since it’s a low-code/no-code point & click solution.)

You also discovered the other big problem, which is that Airtable won’t wait for you to finish updating a field… so you may want to rely on a checkbox field instead. Use the checkbox field to indicate that you are done editing the record.

Scott_Heliker · ‎Apr 01, 2021

Thanks for that info. .Yes sometimes with these apps you don’t know these things unless you exhaust testing… Not really documented most places. I has same issue with G Sheets and Integromat webhook addon. It only works for manual / human changes to a cell. So was going to have a script written but heard that possibly will not work.

So I think I am going to result to a cheap account here.

I built a small app for it in integromat but you can hook it to a http module pretty easily and this runs wittiin a minute of the cron job you create. You can pass in the parameters you want it to return to you on the date / time then use that data how you please. We are basically just looking to hold this time so that we can send an SMS message to a customer 1 hr before their rental is due back.

If you have use for it , I can share the app I built for it in integromat.

Thanks