Weekly Email with only Entries associated to a specific user

[Andreanne_St-Ge]

Hi everyone ! 🙂 

I'm running into an automation puzzle and would love to have a little help.

Take a look at the picture attached. I have a list of clients (column A) and each of these clients are linked to their account manager(s) (column D). Other columns are various infos about each client (status or region - columns B and C - I've kept these as examples).

I want to send a weekly reminder, by email, to each account manager, with all the clients they have to reach out to this week (according to a filter on which the view is based - Priority Status (not shown)). Things would then go like this : 

• Charles receives an email with a list of every bar for which they are account manager, so everything from line 93 to line 108
• Ines receives an email with the 2 bars for which they are account manager, so only lines 100 and 109
• (and so on, for every account manager)

I'd love to be able to do this without adding a new table to my board (i.e. without a separate table listing all the account managers), and by keeping the "Users" field type I'm actually using. I've seen a similar issue solved here : https://community.airtable.com/t5/automations/emailing-a-weekly-digest-to-separate-users/m-p/156341#.... Someone in the comments said a script would be needed? I'm not sure how I would start to set that up...

Any ideas?

Thanks in advance!

[TheTimeSavingCo]

We're really just trying to loop through all the account managers, look for their related clients, and then send them an email

I think the best compromise here would be create the table of account managers and create a "User" field there.  This would allow you to loop through the account managers and use a "Find Record" action to match the "User" field in the table of account managers against the "User" field in the table of clients.  Not 100% sure if this would work but I think it might?

Without the table of account managers, you could also attempt to set it up manually by creating an automation that will a set of actions per account manager, specifically:
1. "Find Record" action to look for clients for Account Manager 1
2. "Send Email" action to send the found records to Account Manager 1
3. "Find Record" action to look for clients for Account Manager 2
4. "Send Email" action to send the found records to Account Manager 2
5. etc etc

---
If not, we need to find another way to get a list of the account managers to loop through and using a script allows us to grab all the collaborators in a base and loop through that.  (That's not ideal either as the list of collaborators might not atch the list of account managers actually)

If you're interested in scripting this yourself, check out the API docs on how to get a list of the active collaborators: https://airtable.com/developers/scripting/api/base#active-collaborators

You'd then use that information to filter out the clients per collaborator by outputting the list and using it in a "Repeating Group" with a "Find Record" action

Hopefully someone has a more elegant solution!

[Andreanne_St-Ge]

Hey - Thank you so much !

I really didn't want to create a new table, but I sensed that your idea might work. So I managed to link another table from my HR base with only the account managers showing up. Then, I did :

• One search to come up with a list of Account Managers
• Repeat an action for every item on the list
  • Search for clients with the ID of each account manager and other specific conditions
  • Sort this list in a useful way
  • Send an email

That works quite well actually ! However, it looks like because I use the "Repeat" for every item on the list option, I can't use conditional logic. I would like for the email to be sent ONLY if there is something on the list (so, no email if the list is empty - i.e. if list length = 0).

Any idea of how I could achieve this?

(Automation attached for reference, but it's in French, sorry!)

[TheTimeSavingCo]

Thanks for the screenshot!  Ahh...yeah that's a tricky one.  Try:
1. Create a "Tasks" table
2. Make the repeating group action create a record in the "Tasks" table with each User's details
3. Create another automation that'll trigger whenever a new record gets created in the "Tasks" table
  - This automation will be the one that does a "Find Record" action to look for the "Client" records, and you can now use a conditional to only send the email if there's one record found or more
  - Put in a Run a Script step to delete the triggering record (Example here: https://community.airtable.com/t5/development-apis/delete-a-record-by-automation/td-p/112769)

[Andreanne_St-Ge]

Oh... thanks for the reply ! This is too much trouble lol. Unless someone else can come up with a more elegant solution, they're getting this email even if it contains nothing. 😅