Hi Power, yeap, doable but, in my opinion, sorta tricky without scripts
Some considerations before we get into it:
- What plan are you on, and are the people you’re emailing collaborators in the workspace / base?
I ask this because, on the Free plan, automations can only email collaborators. On the Pro plan, automation emails can be sent to 100 unique emails a day
- Are you comfortable with scripting, and are you on a Pro account?
I ask this because, if you’re comfortable with scripting and on a Pro account, the workflow will be somewhat simple and straightforward as we’ll utilize an automation script. If you’re not comfortable with scripting or are not on a Pro account, I have a solution for you which I tested and works, but is a bit of a pain to set up
Non scripting approach details:
The idea is to create an automation that runs once a month, that will generate an individual record for each person that you need to send an email to. We would then have another automation that triggers off each of the records created in the previous automation.
I’ve linked a version that should work here. You can duplicate the base by clicking the title of the base at the top of the screen, followed by the three horizontal dots on the right, and then the “Duplicate Base” button. You’ll then be able to see all the formulas, how the fields are set up, automations, etc
Quick summary of how it works:
- Every month, an automation grabs all the records in
People
that have at least one linked Submission
record from last month
- It then creates individual records in
Email Tasks
for each People
record that it found in the previous step by creating a record in Monthly Email Task Compiled
by inserting a comma separated value list of unique into a linked field
- When a record gets created in
Email Tasks
, an automation gets triggered that will look for all the submissions linked to that People
record from last month, and will also grab the email from the People
record.
- It then sends the email to the found email address, with the found records in a List / Grid format of your choice
If you were doing this via a script you’d let the script handle all the record creation and linking, and just use automations to send the email itself
Thanks, I will play around with this! The people are not collaborators but we do have a pro plan, so that should not be a problem. I do have all of the reports linked to the individuals in a version of the “People” tab, but I couldn’t figure out how to reference the information included in those linked records when I set up the email automation, but I’ll try your approach.
I have done a little bit of coding, so I was tempted to start dabbling in scripts, as this seems like something that a simple loop function could do quickly (go through this list of people one at a time, find all records that contain the name in this other list and batch the output). I did not look too much into it, but I was having a hard time comprehending how to do it in the Airtable environment, namely how the outputs of certain functions feed into inputs of others. I’m a total beginner here though, so I’m sure there are some obvious things I am just not understanding.
Thanks, I will play around with this! The people are not collaborators but we do have a pro plan, so that should not be a problem. I do have all of the reports linked to the individuals in a version of the “People” tab, but I couldn’t figure out how to reference the information included in those linked records when I set up the email automation, but I’ll try your approach.
I have done a little bit of coding, so I was tempted to start dabbling in scripts, as this seems like something that a simple loop function could do quickly (go through this list of people one at a time, find all records that contain the name in this other list and batch the output). I did not look too much into it, but I was having a hard time comprehending how to do it in the Airtable environment, namely how the outputs of certain functions feed into inputs of others. I’m a total beginner here though, so I’m sure there are some obvious things I am just not understanding.
Hm Power, apologies, the solution by @kuovonne is much much more elegant than mine, and I suggest you use that method instead
I’ve put it together here for you.
As suggested in kuovonne’s post, I have made it trigger the automations via a formula field in the People
table, leading to a much more elegant flow that accomplishes what you’re looking for