Ignore duplicate email addresses when sending emails

[Anonymous]

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 Email	Report	Oustanding training
manager.one@hotmail.com	Billy Bob	1, 2, 3, 4
manager.two@hotmail.com	Joe Doe	2, 6, 7
manager.one@hotmail.com	Sue Moe	1
manager.one@hotmail.com	Rob Thob	1, 2
manager.three@hotmail.com	Lazy Bones	1, 2, 3, 4, 5, 6, 7, 8

This is my Flow:

The manager receives a table like:

Report	Outstanding
Billy Bob	1, 2, 3, 4
Sue Moe	1
Rob Thob	1, 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

[Pstork1]

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.

[Anonymous]

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]

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.

[Anonymous]

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]

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.

[Anonymous]

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

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 · Accepted solution]

Here's a sample set of screenshots

varmanager stores the last manager processed

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

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

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

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)

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.

[Anonymous]

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]

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.

[Anonymous]

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.

[Anonymous]

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

[Pstork1]

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.

[Anonymous]

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

1.

2.

3.

4.

5.

[Anonymous]

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]

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

[Anonymous]

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 

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]

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.

[Anonymous]

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]

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.