cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Ignore duplicate email addresses when sending emails

Hi I've built a flow that looks through my spreadsheet and sends 165 managers a table of their direct reports and what training they haven't completed yet.

 

This is what the Excel looks like:

Manager EmailReportOustanding training
manager.one@hotmail.comBilly Bob1, 2, 3, 4
manager.two@hotmail.comJoe Doe2, 6, 7
manager.one@hotmail.comSue Moe1
manager.one@hotmail.comRob Thob1, 2
manager.three@hotmail.comLazy Bones1, 2, 3, 4, 5, 6, 7, 8

 

This is my Flow:

Lebene_0-1617918380331.png

The manager receives a table like:

ReportOutstanding
Billy Bob1, 2, 3, 4
Sue Moe1
Rob Thob1, 2

 

My issue is that "manager.one@hotmail.com" will receive 3 duplicate emails instead of just the one. How can I prevent this? I've spent over an hour trying things like Initialize Variable/Union but I just get errors for that.

 

I appreciate any help, I'm still a novice trying to climb onto beginner so I'd appreciate if you don't assume I know what everything means as I'm still hack-jobbing things and not entirely understanding 'why' things happen yet.

 

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Pstork1
Dual Super User III
Dual Super User III

Here's a sample set of screenshots

image.png

varmanager stores the last manager processed

varTraining is an array that stores the rows as they are processed.

image.png

Apply to Each processes each row.  If the varmanager is empty (first row processed) or the manager equals varmanager (last manager processed), then 

image.png

Append the row to the varTraining array and update the last manager processed (this is really just to set it after the first row where varManager is blank)

If the manager doesn't equal varManager do the No side

image.png

Convert the varTraining array into a table and send the email to the varmanager.  Then reset the varTraining array to the current row and varManager to the current row manager. (This is still inside the Apply to Each loop)

image.png

The Apply to each loop will now have sent emails to every manager except the last one.  So when you exit the loop Create one last html table and send it to the varmanager.  That will finish out the processing.

image.png



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

23 REPLIES 23
Pstork1
Dual Super User III
Dual Super User III

Sort the Excel data source by manager Email.  Then use a loop to process the entries.  For each loop check first to see if the manager email is equal to the one from the last loop.  If it is then process the entry (but don't send the email just append it to a string or array). If its a different manager then send the info in the stored string or array to the previous manager, save the current manager as the previous manager, and process the entry.  Loop and repeat.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Anonymous
Not applicable

Hi Pstork, thank you for your answer. Sadly, I just don't understand what it means.

 

Are you saying I should do several apply to each? I searched 'loop' and it didn't show anything. Where and at what stage am I doing all of this? I'm a novice, no humble facades here.

 

Thanks for your help

Pstork1
Dual Super User III
Dual Super User III

No I'm saying do one Apply to each after you have sorted the input by manager.  Then as you process each line check to see if the manager is different from the last line.  If it is then send the email.  If its not then store the information in some way and get the next line.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Anonymous
Not applicable

Thank you Pstork,

"Sort input by manager" - how? - I already have it sorted in the Excel file

"Process each line" - is this a component or a condition? How, where does it go in my screenshot I've provided?

"Store the information some way" - this is a manual operation or how is this done?

 

I've been working on this part for over 1 hour but I've been working on this Flow for the past 6.5 hours almost non-stop.  I would really appreciate a short guide or screenshot because I don't understand what you're saying stemming from my inexperience and fatigue.

 

Thank you again

Pstork1
Dual Super User III
Dual Super User III

1) its not sorted in the file you displayed.  the first and third row are manager.one and the second row is manger.two. There is an option to sort by a column when retrieving the rows from the Excel Table.

2) I'm assuming you are processing each row in the apply to each in your flow now.  I'm just explaining what conditions and actions you need to apply to each row.

3) You can either store the information by appending it to a string variable or adding it to an array.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Anonymous
Not applicable

Ok, I see. That table is just an example I made in this forum to display the duplication and data examples. It is sorted in Excel.

 

The apply each is sending the email

Lebene_0-1617930372865.png

 

I don't understand number 3.

 

I don't really think I'll understand what you're saying without a demonstration, you are speaking theoretically on what I could do, but as I don't possess the knowledge to make sense of it, it's really just many words for me but limited meaning. I struggle with instructions without examples in general, especially when I am lacking basic knowledge on the matter. Even a typed flow demonstration would help me, as I just don't understand where you want me to put these, at what stage, within where and with what syntax.

 

The flow I have now is:

Man. trig. flow -> List rows in table -> Select -> Create HTML Table -> Compose (with HTML table styling) -> Apply to each from excel value (Send an email v2).

 

Thanks again Pstork and I understand if you'd rather leave it here.

 

 

Pstork1
Dual Super User III
Dual Super User III

Here's a sample set of screenshots

image.png

varmanager stores the last manager processed

varTraining is an array that stores the rows as they are processed.

image.png

Apply to Each processes each row.  If the varmanager is empty (first row processed) or the manager equals varmanager (last manager processed), then 

image.png

Append the row to the varTraining array and update the last manager processed (this is really just to set it after the first row where varManager is blank)

If the manager doesn't equal varManager do the No side

image.png

Convert the varTraining array into a table and send the email to the varmanager.  Then reset the varTraining array to the current row and varManager to the current row manager. (This is still inside the Apply to Each loop)

image.png

The Apply to each loop will now have sent emails to every manager except the last one.  So when you exit the loop Create one last html table and send it to the varmanager.  That will finish out the processing.

image.png



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

Anonymous
Not applicable

Hi Pstork, thank you for this detailed explanation.

 

In this run-through, this is the behaviour I experienced:

  1. My email has 4 duplicates but it sent no emails, ignoring them altogether
  2. My 2nd test inbox had 2 duplicates but sent 4 duplicate emails

 

I would want both inboxes to receive 1 email (there are 165 people who need to receive their individual email with varying number of reports).

 

I have attempted using this method: https://www.flowjoe.io/2020/12/17/remove-duplicates-from-a-power-automate-array-with-one-action/ and https://flowaltdelete.ca/2020/06/03/power-automate-expression-union-return-unique-values/ but although I manage to get it to run, the duplicates still occur.

Pstork1
Dual Super User III
Dual Super User III

I'm not sure how to help you further.  I created an excel table with the output you provided in your post and my example works perfectly for me.  I would go back and check your logic again.  You haven't followed the example I provided.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Anonymous
Not applicable

This is what I attempted in another method, not your method. It errors when I use the duplicate output as saying it does not find the row with the email or does not recognise the array.

screencapture-emea-flow-microsoft-manage-environments-Default-4473892f-71e0-46fc-8dec-273902b51349-flows-c66f260d-62ca-4d2d-a8dc-432eb4861cf7-2021-04-09-13_29_19.png

Anonymous
Not applicable

screencapture-emea-flow-microsoft-manage-environments-Default-4473892f-71e0-46fc-8dec-273902b51349-flows-c66f260d-62ca-4d2d-a8dc-432eb4861cf7-2021-04-09-14_40_38.png

 

I thought I did... is the last two HTML and Email in the wrong place?

Pstork1
Dual Super User III
Dual Super User III

It looks like the first condition you didn't add a blank value to the check on varManager.  It still says choose a value.  You need to check that against an empty string.

 

And in Create HTML table 3 you are processing varManager.  You should be processing varTraining just like you do inside the Loop.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Anonymous
Not applicable

Sorry about that, didn't realise the microsoft forums doesn't accept full screen screenshots. I will use the snipping tool:

1.

Lebene_0-1617976086642.png

2.

Lebene_1-1617976122409.png

 

3.

Lebene_2-1617976141030.png

 

4.

Lebene_3-1617976169601.png

 

5.

Lebene_4-1617976210680.png

 

 

Anonymous
Not applicable

Oh right, I misunderstood that as your screenshot also says 'choose a value' so I thought I leave it blank, how do I create the blank value - do I use 'null' in the expression? Also, why am I doing that?

 

I tried the null expression but my condition comes back false so nothing sends. I guess I'm trying to understand why my varManager would be blank, as I don't have any blank rows in my table.

 

Pstork1
Dual Super User III
Dual Super User III

Just use '' in the expressions tab to get a blank string



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Anonymous
Not applicable

Great thank you. I made those two changes but the behaviour I mentioned before still occurs.

 

I'm not sure what else could be the issue. My Excel table layout is exactly as I mentioned, albeit the training titles are actual words separated by commas, but that doesn't change anything even if I changed them to the numbers like my example.

 

Thanks a lot for your patience Pstork. I will pore over your screenshots and see if I missed anything else.

I also get this error for some of them 

Lebene_0-1617978668196.png

When I look at every instance of the run, all 6 was relating to the test inbox, not once of them related to my email address or the test reports in there.

 

I'll keep trying. Thanks again.

 

I wasn't supposed to do any advanced settings in my Listrows segment right?

Pstork1
Dual Super User III
Dual Super User III

Try making these changes.

1) When you initialize varManager initialize it to something like 'First'.  Then in the first condition check for that instead of null.

2) Look at your SetVariable2 again.  It doesn't match the text in my screenshot.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Anonymous
Not applicable

Ok, so in the value part of my Initialise Variable, I type ["First"]?

 

My SetVariable2 is your SetVariable - I can't see what the difference is, they both say varTraining in the Name, with: 

[
{
"Report": @{items('Apply_to_each')?['DirectReport']}.
"Outstanding Training": @{items('Apply_to_each')?['RemainingTraining']}
}
]

 

My SetVariable3 (your 2) both says varManager and ManagerEmail?

Pstork1
Dual Super User III
Dual Super User III

Did you change the condition to check for  ["First"]?

 

Also, the screenshot for your set variable 2 doesn't match mine.  Your screenshot shows

[
{"Report": @{items('Apply_to_each')?['DirectReport']}.
"Outstanding Training": @{items('Apply_to_each')?['RemainingTraining']}}
]

Instead of 

[
  {
    "Report": @{items('Apply_to_each')?['Report']},
    "Outstanding Training": @{items('Apply_to_each')?['Oustanding training']}
  }
]

whitespace makes a difference.

 

 



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Helpful resources

Announcements
Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,755)