Comparing Two Lists Data and Send Relevant Emails Based on Comparison

[Anonymous]

I'm fairly new to PowerApps and Flow, but I'm trying to build a process based on a business process that has been designed.

I have a form that allows people to submit a request for a project, then shoots an email to a member of my organization to choose the teams that will need to work on the project. I have that Flow working successfully.

The next part gets tricky.

Based on the teams that this person selects, I need either PowerApp or Flow to compare those choices to a different control list which lists the specific person assigned to each team, who would need to receive an email based on relevancy. So if teams 1, 2, 3 are chosen then people A, B, and C need to receive emails (it gets a little fishier when person D is supposed to receive emails whenever 1 and/or 3 is chosen as well). If teams 1 and 3 are chosen, then people A, C, and D need to receive emails. If team 2 is chosen, only person B would receive an email.

I hope this makes sense. This is complicated and pretty confusing, but I've spent the better part of this week trying to figure it out. I am hoping someone can help me with a solution. I posted this in the PowerApps Community forum as well, hoping to find someone that has any ideas.

Thank you! 

[ScottShearer]

Here is a simple solution that may work. 

Create a row in your lookup list for each combination of possible teams.  You can have a boolean/checkbox column in the list for each team. Also include a person or group column that lists the people to be notified.

Have your Flow lookup in the list the row of data where the teams that have been selected match what the user selected and send an email to the names listed in the person or group column.

Since the person or group column has multiple vales, referencing it will automatically put you into a for each loop - this means a separate email for each addressee.  The really issn't a problem with that but you could always add each email address to a string inside of the loop and reference the string in "To:" email line.  If you use the string remember to put a semicolon between each email address.

To retrieve the row of data from the list, use an ODTA filter - you should only have 1 row of data returned.

If this meets your needs and you need an example, please post here.

If this solution meets your requirements, please mark you issue as solved - this will help others with a similar issue.

[v-xida-msft · Accepted solution]

Hi @Anonymous,

Could you please share a screenshot of your flow's configuration?

I agree with @ScottShearer's thought almost, I assume that the Person D would receive emails when Team1 or Team3 is chosen. I have created a SharePoint list on my side based on the different combinations of possible Teams:

Note: The Teams column is a Choice type column in my SharePoint list, which allows multiple selections. The available values of the Teams column is Team1, Team2 and Team3. The Recipients column is a Person or Group type column, which allows multiple selections. The Test1, Test2 and Test3 represents Person A, Person B and Person C, the Test4 represents Person D.

I have made a test on my side and please take a try with the following workaround:

Within "Initialize variable" action, Name set to UserSelected, Type set to Array and Value set to Person's selected (An array data).

Within Inputs field of the Compose 2 action of "Apply to each 2" action, type the following formula:

concat(items('Apply_to_each_2')?['Value'],';',variables('CombinationTeam'))

Within "Set variable 3" action, Name choose CombinationTeam and Value set to output of the "Compose 2" action. 

Within Inputs field of "Compse" action of "Apply to each 3" action, type the following formula:

concat(items('Apply_to_each_3')?['Email'],';',variables('Recipients'))

Within "Set variable" action, Name choose Recipients and Value set to output of the "Compose" action.

Within Inputs field of the "Compose 3" action of "Apply to each 4" action, type the following formula:

concat(items('Apply_to_each_4'),';',variables('UserSelectedString'))

Within "Set variable 2" action, Name choose UserSelectedString and Value set to output of the "Compose 3" action.

Within Condition, left input box set to UserSelectedString variable, right input box set to CombinationTeam variable. middle drop down set to is equal to.

Best regards,

Kris