Apply to Each Send out Multiple Email for a SharePoint list

[ppei]

I set up a Flow to send out quarterly feedback report for a SharePoint list. I don't want to send out the report if a department doesn't have any feedback, but after I set up a condition that "'Related Area to be notified' is not equal to Null", an "Apply to Each" is added to my Flow. The Flow works correctly not sending out any emails if there is no feedback for a department. However, if there are several feedbacks for a department, multiple duplicated emails will be sent out. How can I just send out only one email with all the feedbacks for a department? See a screenshot of my Flow below.

Your help is greatly appreicated.

Thanks in advance.

Pei

[edgonzales]

@ppei 

Hi there.  Is it possible to move your "Send email" step further down (outside of the "Apply to each" loop)?

That should resolve the issue.  Keep us posted.

-Ed-

If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

[ppei]

@edgonzales 

Thank you for your suggestion. Actually if I don't add the condition, the Flow will only send out one email. If I move the "Send email" outside Apply to Each, it will be the same as I don't have the condition. The Flow will send out blank emails to some departments even if there are no feedback for those departments. So the problem could be the condition. If anyone has any idea to use a different condition to provent send out blank emails, it'll solve my issue.

[edgonzales]

@ppei -

Ok, looking at this more closely now...  For sure the "Send email" will need to be outside of the "Apply to Each" loop to keep from getting a new email with each row.  

As you have it now, it will create a new table each time something passes the condition.  What might work better is to create an Array in the beginning, then add to that array inside the loop (leave the condition as you have it), and then outside of the loop, convert the array to your html table, and then send a single email with the new table.

Let me know if you need more details, I didn't want to presume where you were in your comfort level.

-Ed-

If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

[ppei]

@edgonzales 

Thanks again for your suggestion. I tried that as well. The problem is the same: the Flow will send out a blank email to a department even if there is no feedback for that department. I have set a Filter Query for department inside Get Items action. Do you have any other suggestions?

[edgonzales]

Yep - Working on it now.

[edgonzales · Accepted solution]

@ppei 

Ok, I think I have something that works, and is surprisingly elegant (for me) 🙂

The first thing I did was add a filter to the Get Items so that I'm filtering out anything that has a null in that field to begin with.  This negates the need for a condition and requires only the simplest of OData - FieldName ne null  - The FieldName is RelatedDepartment field and 'ne' is "Not Equal to" and "null" is essentially blank.  See below:Next, I added a condition to check if the array was empty.  Again, this is a simple expression that returns "True" if the array is empty, and "False" if there's stuff in it.  Pieter Veenstra has a cool article on Arrays in Flow.  The expression looks like this: 

empty(body('Get_items'))  which tells Flow to look in the output of Get items for an empty array...

If it is True, the array is blank and we probably don't want to do anything.  If it's False, then there's something there and we want to send the email.  Here's the whole Flow:

I still have one more trick if this one doesn't solve it for you...this one just seemed to be the simplest and most stable.

Let me know if that helps.

-Ed-

If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

[ppei]

@edgonzales 

Ed, thank you very much for your dedication and great help. I'll test it tomorrow. Have a good night!

Pei

[ppei · Accepted solution]

@edgonzales 

Ed, 

I just tested your solution. The step to check if the array is empty is crucial. If I use it to check if Get Items is empty, the Flow will still send out a blank email even if there is no feedback for a department. But, after I create a variable, store the array to the variable, and change the expression to refer to the variable. It works as expected.  The problem is solved.

I give you all the credit. I really appreciate your help.

Thanks again.

Pei