Automated email 2 days in advance of a deadline

[Julie_Schirmer]

Hi everyone! I’m new to Airtable and would like to setup an automated email reminder that triggers 2 days before a given deadline date placed in one column of an Airtable base. Ideally, this date could either be a new record or an updated existing record, triggering the same email reminder 2 days before due. And the email reminder would include all the pertinent data from other cells in the deadline’s row.

I really liked a similar feature in Google Sheets called “Remind” but it’s not functional anymore.

Thanks for your suggestions@!

[AlliAlosa]

Hi there Julie!

I would recommend using Zapier for this. You can create up to 5 Zaps for free.

I would start by creating a view that shows only records with a due date of 2 days from today. Your Zap would be triggered by a new record added to that view. Airtable provides a helpful article on how to set up a Zap for this purpose here (Example 2 probably would be the best match):

Airtable - Support

Using Zapier to send email notifications

Zapier is a third-party service that connects Airtable to many other apps and services. This article is specifically designed to teach you how to create a Zap which will automatically send you an e...

One caveat… a Zap will only trigger once for a particular record. Let’s say your deadline is 2 days away, and the Zap triggers the email alert. Then, for whatever reason, you extend the deadline. Once the new deadline is two days away, you won’t get the alert for that record.

[Julie_Schirmer]

Thanks so much for the idea! I do need to find a solution where the deadline date can be revised/updated and the email triggers again, 2 day before the new deadline. Sounds like Zap wouldn’t do that. Bummer.

Appreciate any further suggestions!

[AlliAlosa]

I have found a workaround to this problem, but it is somewhat complicated and involves using two Zaps. If you’re interested, I can try and explain :slightly_smiling_face: Also, it might be worth looking into Integromat… It’s similar to Zapier but seems to be a bit more robust. I haven’t had much experience with it, but it might be able to trigger more than once on a particular record.

[Julie_Schirmer]

Awesome! I’m into trying your two Zaps workaround. I think you’re right about Integromat, but I had trouble setting it up. Thanks a bunch for the help.

[AlliAlosa]

Awesome! I remembered that I actually explained this to someone else in the forum once before, so rather than typing it all up again; I’ll direct you to this reply:

Daily recurring reminder emails formatting Formulas

OK! So here is a sample base that I hope will point you in the right direction. There is a TON of information here and I didn’t even spell everything out, so please don’t hesitate to ask questions! This workflow makes use of two different zaps: The first zap is still triggered on a schedule, every day at 5AM. Set it up to find the record “:briefcase: :briefcase: :briefcase: ” on the Junction table, and insert the value from the field {Alert Names}, into the linked record field {Email Alerts}.…

The above reply and sample base it links to is describing a situation where the tasks are due today, but it can easily be adjusted for tasks that are due two days from now.

I realize this approach is complicated, and I’m happy to answer any questions you might have!

[Tommasina_Mille]

Hi @AlliAlosa - I’ve been trying to implement your two-Zap solution for a few hours and thought I’d just reach out for help. Basically, I want to send an email reminder to organizations the day before we deliver their masks. I have a formula field on my master grid entitled ‘should get email today’, and per your suggestion, I created another formula field in my master grid based on that previous one to create the unique identifier, called ‘should get email today unique id’.

My first question is - for the first Zap, is the trigger starting from Zapier Scheduler? That’s what I’m trying and I’m a little confused. I have it so the steps are:

1. Every day (Zapier Scheduler)
2. Find record in Airtable (this is where I’m stuck because when I select the Junction table, and search by Name, as per your suggestion, the dropdowns are only showing me the single date record found within Zapier Scheduler for today, so I can’t search by my Airtable record field name.)
   
3. (I think this would be the Update record action but I’m not sure what it’s updating)

For the second zap, I’m guessing the trigger is Airtable’s ‘new record in view’ and the action is sending the email. What view in your example is triggering the email?

My third question is around the Junction table in general. Is the function of the first zap to automatically update the linked record for that day’s emails? Also, more basically, in your example base, Email Alerts is your linked record field, right? I guess I’m also confused on the difference between the Alert Names and the Email Alerts fields on the Junction table. I see the timestamp date is later on Email Alerts but I’m not clear on why or how that gets set.

I’m sorry I have so many questions for you! This just seems like such a good solution but I’m such a newb. I’m helping coordinate the distribution of 3.5M masks and anything I can automate for the coalition organizing this gives them more time to be on the frontlines.

Thanks for any help you can offer!!

[AlliAlosa]

Hi there, @Tommasina_Miller!

Sorry for my delayed response - haven’t been on the forum as much as I’d like to be lately!

Sounds like you’re on the right track!

Tommasina_Miller:

Find record in Airtable (this is where I’m stuck because when I select the Junction table, and search by Name, as per your suggestion, the dropdowns are only showing me the single date record found within Zapier Scheduler for today, so I can’t search by my Airtable record field name.)

For the above question, you can type the name of the record (whatever is in the Primary Field) into the Search Value field in Zapier. In my example, the name of the record was just three emojis ( :briefcase: :briefcase: :briefcase: ). Best practice wise, you should really use the Record ID, so that you don’t have to worry about updating this Zap if you ever add more to the [Junction] table or edit the name of that record. To find the record id, you can add a formula field to the [Junction] table with a formula of:

RECORD_ID()

Copy this value and paste it into the Search Value field in Zapier.

Tommasina_Miller:

For the second zap, I’m guessing the trigger is Airtable’s ‘new record in view’ and the action is sending the email. What view in your example is triggering the email?

Yes, the trigger for the second Zap should be “New Record in a View”. The view in my example that triggers the second Zap is called “To Be Sent”. The last step of the Zap updates the record on this view with the date the email was sent, so this view should be filtered to show only the records without that date filled in.

Tommasina_Miller:

My third question is around the Junction table in general. Is the function of the first zap to automatically update the linked record for that day’s emails? Also, more basically, in your example base, Email Alerts is your linked record field, right? I guess I’m also confused on the difference between the Alert Names and the Email Alerts fields on the Junction table. I see the timestamp date is later on Email Alerts but I’m not clear on why or how that gets set.

Yes, the function of the first zap is to update the field {Email Alerts} with the value from {Alert Names}. Essentially, {Alert Names} is just a dynamically updating list of records to be created. The formula function TODAY() allows the date to be dynamically updated each day.

This gets a bit complicated to explain, but essentially, it’s a useful trick to create multiple new records on a separate table. To demonstrate in a simple way… if you were to type: "A, B, C, D" into any text field in Airtable, copy it, and paste it in to a linked record field, this would create 4 new records on the linked table, called “A”, “B”, “C”, “D”, and each could trigger a new zap. The first zap, at the most simple level, is literally just copying and pasting {Alert Names} into {Email Alerts}.

This comes with a few caveats. If any records on the linked table are already called “A”, “B”, “C”, or “D”, then a new record wouldn’t be created - rather it would just get linked to the existing record. This is where the dates come in - to create unique identifiers to ensure that each time the first zap runs, you are creating new records to trigger the second zap.

The second thing to be aware of is that if the primary field on the linked table (in this example, [Alerts]), is a formulated field, this method will not work. It must be a plain text field to allow new records to be created.

I hope this all makes sense. If you have any more questions, please don’t hesitate to ask! Happy to do what I can to help with your cause :slightly_smiling_face: