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.
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.
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.
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.
As @bitgiggy mentioned above, unfortunately, NOW() doesn’t 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, your base will “go to sleep”, and then Airtable will only refresh NOW() sporadically.
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 data on this.
Although Airtable recently introduced running Automations on a schedule, so you can use this to “wake up” your database from sleep on a regular basis.
However, if you need more reliable & accurate scheduling/timing, I would turn to Make's advanced automations for Airtable, which it looks like you are already using.
You can have Make run on a schedule and search for the records that you need to take action upon in Airtable.
Make also has much more advanced scheduling options than Airtable.
For those people who have never used Make before, I’ve assembled a bunch of Make training resources in this thread. For example, here is one of the ways that you could instantly trigger a Make automation from Airtable.
You also discovered another problem in Airtable’s automations whenever you use the trigger “when a record is updated”. Airtable won’t wait for you to finish updating a field… so you may want to rely on a checkbox field or a single select field instead. You can use one of those fields to indicate that you are done editing the record. Check out this thread for more information on this.
Hope this helps!
If you have a budget and you’d like to hire the best Airtable consultant to help you with this or anything else that is Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld
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.
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
This post has several years now, but similar posts with the same issue/concern are created at least on a monthly basis!
Sharing a video on this matter in case it helps future readers.
Mike, Consultant @ Automatic Nation
YouTube Channel
Another great video from @Mike_AutomaticN!
I highly recommend that everybody subscribe to his YouTube channel!
- ScottWorld, Expert Airtable Consultant
Lol @ScottWorld 😂😂. Make it sound true at least! 🤣
@Mike_AutomaticN im having trouble with a table in one of my bases is there a way to connect?
Hey @J.Ali,
Sure. Would you mind sharing some more details and even screenshots? I’d be happy to help!
@Mike_AutomaticN Which part didn’t sound true? I was trying to give you a compliment. 🤷♂️
@Mike_AutomaticN Which part didn’t sound true? I was trying to give you a compliment. 🤷♂️
I know! I was jk. Really appreciate that 🥹
@Mike_AutomaticN Ahhh… LOL!! Perfect!! 😝
@Mike_AutomaticN I’m not sure how to add a net profit table that calculates the difference between the income table and expense table interactively
