How to automatically send a list of records to individual emails each week

Hi Folx! Here is my scenario:

I have an online event business and about ~100 contractors that are assigned events in custom software. All of the events are synced to table 1, and all the contractors are synced to table 2. The contractors are associated with their event(s) via a linked record field. All of this is done automatically and works like a charm.

I would like to send a summary each week of the list of events the contractor has worked. I need help writing an automation that will:

  • look up records from view for the previous week
  • create a list of the records associated with each unique email address
  • send the list of records to only that email address

Where I am running into trouble is trying to have airtable send only the records associated with Contractor A directly to Contractor A. It seems like I could create a list of all records and send them to all contractors but that is not the desired outcome.

Thanks in advance for any help!

I accomplish this within my team by making a view for each contractor (easy to hide these away under an “automations” tab in your view list) and then having one automation trigger each view sending an email to the person it’s specifically filtered for. Of course, with AT action limitations you can only have 12 contractors where this will work (it’s a step to make the digest and a step to send it, so 2 steps for each).

Yeah but I have 100 contractors and that would require one automation per view. So it would exceed the max automation per base

Here is one method …

In the [events] table, have a {IsInPreviousWeek} formula field that determines if the event was in the desired date range (the previous week).

In the [contractors] table, have conditional rollup that rolls up information for linked events that are in the previous week.

In the [contractors] table have a {TriggerWeeklyEmail} formula field that checks if (1) it is the day to send your weekly email and (2) there were events in the previous week. (It is possible to combine this formula with the rollup, but I recommend starting with different fields unless you are very comfortable writing complex formulas.)

Finally, have an automation for “when record meets conditions” that triggers based on the {TriggerWeeklyEmail} formula field.

If you need a nicely formatted table of events, you will then have a “find records” action, followed by the “send email” action. Pull the email address from the triggering record in the [contractors] table, and the table of events from the “find records” action.

If you do not need the nicely formatted table of events, you can skip the “find records” action. Send the information from the rollup field.

One automation, no filtered views, no scripting.