Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Dec 06, 2019 11:20 AM
Hi guys,
I have a formula that I want to update automatically if a condition is met -
IF({U/C Date}=TODAY(), “yes”, “no”)
Based on the answer to that formula being “yes” I am trying to use integromat to send an email.
It is my understanding that the today() function will not update until the base is reloaded or a record is edited, therefore my question is - can I use zapier or integromat to update the base on a schedule each day and therefore have the today() function update without the need for a human to open Airtable?
Any suggestions would be really helpful.
Thanks,
Dec 06, 2019 02:32 PM
Welcome to the community, @Theo_Field! :grinning_face_with_big_eyes:
Probably, though it’s kind of redundant. If you build this in Integromat, for example, you’re running a scenario to force-refresh Airtable, which would then lead to another Integromat scenario being triggered to send the email.
It would be cleaner to just have a single Integromat scenario set up to run once a day and send emails based on records found where the {U/C Date}
value matches today’s date. The scenario would begin with a “Search records” action, using a filter formula of {U/C Date}=TODAY()
. The rest of the scenario would be set up to send the email as you wish.
Dec 06, 2019 03:35 PM
Thanks @Justin_Barrett - I wasn’t sure if it would still catch the match if the sheet hadn’t been updated!
If I set up the integromat scenario as suggested, will I need to worry about time zones? I am in New Zealand and I’m having some issues in Airtable with the today() function matching to the {U/C Date} field.
Dec 06, 2019 05:08 PM
When using the search action in Integromat, it always captures Airtable data in its most updated state.
Airtable’s TODAY()
function uses GMT for comparison. What you might try is making a formula field named something like {Today Local}
that manually shifts TODAY()
based on your offset from GMT. From what I see, New Zealand is GMT+12, so try this for {Today Local}
:
DATEADD(TODAY(), 12, "hours")
Then in Integromat, you’d set up your Airtable search action to use the following filter:
{U/C Date}={Today Local}
FWIW, if you ever find yourself using Integromat’s own “today” option in a field calculation, make sure that you’ve set your timezone in Integromat’s settings.
Dec 07, 2019 11:33 PM
Thank you for your suggestions @Justin_Barrett - I think I am getting somewhere based on what you suggested, however I was still having some issues with the dates matching and allowing the Integromat scenario to run.
However I am testing using the native today() within Integromat - it looks like it will read the date in the {U/C Date} field and compare it to my local date (set within my profile settings).
The other option I was testing was to extract the day as in integer using Day() from both fields and then using {U/C Day}={Local Day} in Integromat. That may be a little more complicated than needed, but it seemed to be working.