Automation "send Email" to specific records

[PhilZi]

Hi

I have a problem I cannot solve. 
There are three tables.
Table 1: Roles in a team
Table 2: Team members, who can have different roles (more than one per member)
Table 3: Team projects where different roles are needed (multiple roles per project)

Table 2 and Table 3 are Linked to Table 1. 
Now when a new Project (with different roles) is added, I want to send an Email to all members who are able to work im this particular role.

My Idea: I can see all roles that the projects needs in Table 1 as each record (role) has a Link to the project if the projects needs this role. In the same row I can see all members that have this role. 
I want to use the "Find records" action to get all Email addresses from the members that have the same role as the project. And I want to have every email address only once. 

Any help is appreciated 
Thank you! 

[TheTimeSavingCo]

Hi Phil, assuming your desired workflow is to create a team project, set the roles it needs, and mark a checkbox to send out the emails, then:

If you're okay with creating multiple automations to handle this, you could make one automation per role?  The trigger would be a checkbox in Table 3 or something, and when it ran it'd check if the project required a specific role, say 'Designer', and if it did, it would look in Table 2 for all the people that matched the role 'Designer', then a send email action with the list of emails it found?

If you're looking into doing this with a single automation then maybe try:

1. Creating a lookup field in Table 1 to pull all the emails over.  This would result in each record having all the emails of people who meet this role requirement
2. Creating lookup fields in Table 3 that will display all of the emails for every role
3. Creating formula fields in Table 3 that will check whether that team project requires a specific role, and if it does, displays all the emails of people that match that role
4. Create an automation that will trigger when the checkbox is marked that will use the formula fields created in the previous step and send out the emails
  - Because you're using the formula fields, if you don't need that role the email will be sent to nobody
  - To make the automation work all the time I'd just make it send to myself + all the role emails as I believe sending an email to nobody will cause it to error out

So if you had three roles: Role A, B and C
And you had a project that required role A and B

You'd end up with formula fields in Table 3 that would show the emails only for Role A and Role B, and your automation would have three action steps:
1. Send mail to all emails for Role A
2. Send mail to all emails for Role B
3. Send mail to all emails for Role C

Because Role C's email field will be empty, you're the only one that will get the email