Daily recurring reminder emails formatting

[Ryan_Lee2]

Hello,

I would like to send daily recurring reminder emails to my team members. I would like the email to be formatted to include the task due and also include specific fields that I have created including multiple select fields and due date.

For example, within a table, “tasks”, I have a view which is filtered to include a single team members tasks grouped by due date and a multiple select field identifying the “importance” of a task, either “low, moderate, and high”.

I have a formula column which prints the text “send reminder” if the due date field is today’s date:

“IF({Due Date}=TODAY(), “send reminder”, “”)”

I have set up a Zapier trigger using the “scheduler” zap, which is scheduled to trigger every morning at 5am.

Next, I set up a Airtable action, “find record”, to search for the value of “send reminder” in the table.

I then set up an action for gmail to send an email to the team member.

I want to format the contents of the e-mail to contain:

The name of the tasks due today, along with multiselect categories I have created, such at the degree of “importance”, low, medium, or high, the due date, and the name of the task. I formatted the email using HTML and the “find record” embeds.

However, when I test the email, it only sends a single record, as opposed to all of the tasks that are due on that given day.

This seems like an aweful lot of effort to do such a simple and mundane task. Why has Airtable not integrated this functionality into the app yet? Is there an easier way to accomplish this using views and zapier?

Any help is so much appreciated it. This has been driving me crazy for days and I still cannot figure it out.

I know this would help many folks out there. Thanks to anyone with insight!!!

Ryan

[AlliAlosa]

Hi there!

Zapier is set up to only trigger on one record at a time; especially using the “schedule” trigger. Compare this to a “new record in a view” trigger, where several records could appear and trigger the Zap several times in a row (but always separately).

This can quickly get complicated and confusing, but it can work. I am more than happy to help - in fact I just did this myself in a similar base where I send out weekly emails with task reminders. However, I just tried typing out step-by-step instructions and I even confused myself.

I will work on putting together an example and share it ASAP :slightly_smiling_face:

[AlliAlosa]

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!

Airtable

Sample Tasks - Airtable

Explore the "Sample Tasks" base on Airtable.

This workflow makes use of two different zaps:

1. 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}. This creates new records on the [Alerts] table, which serve as the trigger for the second zap.

2. The second zap should be triggered by a “new record in a view”, in this case, the new records on the table [Alerts], in the view “To Be Sent”. The first step of this zap should be to find the corresponding person on the [People] table using the field {Full Name} on the [Alerts] table. The second step is sending the email, and the third step should update the record on the [Alerts] table with the date sent, as well as link it back to the [People] table.

Check out the field {Tasks to Send} on the [Tasks] table. Here I’ve wrapped the fields you want in your final email within table cell tags, and then rolled them up with table row tags on the [People] table. This creates the body of HTML table that can be inserted into your email. Check out Airtable’s blog post on this here (I was one of the March formula contest winners with this submission - thanks Airtable! :slightly_smiling_face: )

The email body in your zap should look something like this:

<table border="0" cellpadding="1" cellspacing="3" class="table" style="width: 100%;">
<tbody>
<tr>
<td><b>Task</b></td>
<td><b>Description</b></td>
<td><b>Importance</b></td>
<td><b>Due Date</b></td>
</tr>

***Rollup field {All Tasks Due Today} from [Alerts] table****

</tbody>
</table>

Final table mock-up:

I hope this is helpful! Happy to explain further :slightly_smiling_face:

[Shruti_K]

Hi, Does this work on repeat reminders also ?
Example- recurring tasks like birthdays or something that happens every Monday or Sunday etc.?

Created time and modified time in non compute fields will always remain same…so there is no trigger as a new field in the airtable view.

Hence, there is no recognition of same row data as new - hence no repeat email is sent!
How to do this with zapier or integromat ?

[AlliAlosa]

Hi there!

This is my workaround for repeat reminders :slightly_smiling_face: It will definitely work for what you’re describing. Just set the trigger of the first zap to be on a particular day of the week.

Another important thing is to make sure that you’re using a unique identifier for each alert. Take a look at the field {Alert Names} in the Sample Base I linked to above. Each alert in the rollup has a prefix of today’s date (using the TODAY() function) to ensure that when the Zap is triggered on a particular day, it creates new records on the [Alerts] table.

Hope this helps!

[sourav_kumar]

Hi @AlliAlosa

I have a similar problem where i would like to auto send emails to patients, reminding them of their daily medicine dosage.
Below is the medication schedule for the entire week

& filtered view

I won’t be making any changes to this data. In the filtered view i only get a single record, for which i want to send an email, & it automatically changes every day showing the plan for that respective day.

Since there are only two trigger points in Zapier ‘New record’ & ‘New record in a view’ the trigger acts only one time & after that, it shows ‘No new record found’
Please help.

[AlliAlosa · Accepted solution]

Hi there @sourav_kumar!

The above steps I posted should work for you as well. You’ll want to make sure all of the records which could trigger an alert are linked to the same record on another table. I usually call this table [Junction] or something to that effect.

Next, create a formula field on the medication list table, with a formula that matches your filters for the view which would normally trigger the alerts.

For example, you could write…

IF({Notes} = "Have today", DATETIME_FORMAT(TODAY(), 'MMDDYYYY') & " - " & RECORD_ID())

This will result in a unique identifier for the record which should trigger the alert. Next, rollup this new field on the [Junction] table with the aggregate function:

ARRAYJOIN(ARRAYUNIQUE(ARRAYCOMPACT(values)))

This will give you a field on the [Junction] table with a list (or in your case, just one record), of the records in your [Medication List] that should trigger the alert today.

Then, follow the rest of the steps I laid out above. The first Zap should run on a schedule, take the value of the new field on the [Junction] table, and insert it into a linked record field that links to a new table, called [Alerts] or something like that. This creates a new record on the [Alerts] table to trigger your email. Use rollup fields on the new [Alerts] table to pull the info you need to use in the email across from [Medication List] -> [Junction] -> [Alerts] (or, use the Record ID to find the info as a step in your Zap).

Hope this helps!

[sourav_kumar]

Hi @AlliAlosa
Thanks for the solution.