Cause action (from Zapier) when time from created date elapses

Stoober_Pickup · ‎Feb 21, 2022

I’m trying to send an email (using Zapier) to a contact once a record has been on the page for 1 week. for example:
someone submits a report- record is added.
Once 1 week elapses a field changes to trigger a Zap to send an email to a contact.

Any ideas of how?

Ben_Young1 · ‎Feb 21, 2022

Hey @Stoober_Pickup! Welcome in!

If you’re using Zapier as a way to manage the delay between record creation and the seven-day mark, there is actually a simpler way for you to do this within Airtable!

But no matter which automation method you use, the key to trigger it is to have a condition that must be met in order for an automation to run.

You’ll want to make sure that you have some sort of timestamp of the record creation.

For your use case, the Created Time calculated field seems like it would work best.

Next, you’ll want to create a formula field that will calculate the date that the alert should be sent to the contact.

The formula we’ll want to leverage is the DATEADD() formula.
Syntax help and additional context on this formula can be found here.

Here’s how I wrote the formula foundation:

DATEADD(
    {Created},
    7,
    'days'
)

Here’s how it appears in the table:

Okay, now that we have the time/day that marks one week out, we need to find a way to trigger the automation.

In this case, we will want to probably rely on a conditional change for the trigger to fire.

Here, I can either choose to:

Create a new formula field that looks at whether or not the calculated date is before or after today and then use that information to spit out a value that will set off the automation.
or…
Compile the two fields from #1 into a single formula field.

Method One:

Here’s the result of using this method:

If you focus on the second record, you’ll actually notice that despite the dates being the same in the One Week Alert field, the values in the Alert Status field are different.
I used a quick workaround to change the result so that you could see the difference.

Here’s the formula behind the new Alert Status field:

IF(
    {One Week Alert}
    IF(
        {One Week Alert} <= TODAY(),
        "✅ 7 Day Milestone",
        "⌚ Waiting for Milestone"
    )
)

The first layer IF statement just keeps a value from ever appearing in the formula if the One Week Alert field is blank.

Method Two:

This method just condenses the two formula fields into one.
If it matters to you, the drawback here is that you will not be able to see when the alert is due to be sent.

You can, however, very easily tweak the formula to give you the scheduled time as well.

In order to trick the formula, I’ve converted the calculated created field into a manual input DateTime field.

And here’s the formula behind it:

IF(
    DATEADD(
        {Created},
        7,
        'days'
    ) <= TODAY(),
    "✅ 7 Day Milestone",
    "⌚ Waiting for Milestone"
)

The Fork: Zapier or Airtable?

Now that you have a field that dynamically displays when a week has passed, you can now use this as your condition to set off the email automation in either Airtable or Zapier.

Feel free to reply and let me know if you have any questions or are curious about anything.