Filter array of owners to send one email with a list of items with status open

[spupazza10]

Hi, as per the subject, I'd like to create a flow to send one email to each owner and in the email the list of items in open status. I managed to send the email with the list of items in status open, but once I added the filter array for email owners, I got stuck. The flow succeeds but no email is sent. If I look at the flow output steps, the filter array has an empty output, but I don't understand why. 

Here is my flow:

Any help is much appreciated.

[creativeopinion]

@spupazza10 Is your owner list a single person column or a multi-person column? If it's a single person column, you might find this YT Tutorial helpful: How to Send a SINGLE EMAIL ✉️ with multiple SharePoint list items | Build THIS Power Automate Flow

In this tutorial I cover:

✅ How to send multiple list items in a single email with a Power Automate Flow

✅ How to create a dynamic date range

✅ How to use the Convert Time Zone action

✅ How to use a Filter Query in the Get Items action

✅ How to count number of items in an array

✅ How to use the Select action to extract a users display name and email address

✅ How to create a unique list of email addresses

✅ How to use the Create HTML Table action

✅ How to customize the HTML Table with CSS styles

✅ How to use the Send an email (V2) action

✅ How to use the Append to String Variable action

✅ How to create a custom list of items for an email

✅ How to use the Send an email (V2) action

✅ How to display singular or plural text based on the number of items returned

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response! If you're feeling generous— ☕️  Buy me a coffee: https://www.buymeacoffee.com/acreativeopinion

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok

[ekarim2020]

HI @spupazza10 ,

 In your Select2 action switch to text-mode using the following steps. Make sure that the Map properties are empty before switching to text-mode:

This change to the Select action will return a list (array) of just email addresses, for example:

The compose action

The compose action containing the union() function will then remove the duplicate email addresses.

See also : 📧How to Remove Duplicate Email Addresses from a Set of Data Using Power Automate – Ellis Karim's Bl...

Ellis
____________________________________
If I have answered your question, please mark the post as ☑️ Solved.
If you like my response, please give it a Thumbs Up.
My Blog Site

[spupazza10]

Thank you for your reply, it solved the issue of sending the email, but how can filter the html table to send only open items for each owner, meaning that if owner 1 has 2 items, one open and one closed, the html table for owner1 should display only 1 item open, not all the items open in the list where there are other owners.

[ekarim2020]

Hi @spupazza10 ,

In the Get Items action you may be able to add a filter query to filter for action status (Open/Close) is equal to Open. Or you may need to use the Filter Array action.  I don't know the data type for the action status column (integer, choice?).

Please share more details about the SharePoint List data types.

Ellis
____________________________________
If I have answered your question, please mark the post as ☑️ Solved.
If you like my response, please give it a Thumbs Up.
My Blog Site

[spupazza10]

I've already done that, it filters open items, but the created table lists all open items for all owners, not for that specific owner. Is there a way to filter the owner's email address to get distinct values in the Get Items step? Would that solve my issue?

I've researching online for possible solutions, and I've moved the created table after the filter array. Now teh outcome from the filter array is empty, therefore the email is empty and I receive more than one email like the count of open items in the list for all the owners.

[ekarim2020 · Accepted solution]

Hi @spupazza10 ,

Here is an approximation of the flow:

 

Please check the following post which explains some of the details : How to Remove Duplicate Email Addresses from a Set of Data Using Power Automate – Ellis Karim's Blog

 

Ellis
____________________________________
If I have answered your question, please mark the post as ☑️ Solved.
If you like my response, please give it a Thumbs Up.
My Blog Site

[spupazza10]

Hi, Thanks for the help. I have tried as you suggested. The outcome is, if in the list there are 5 open items with two different owners, I receive 5 emails (instead of 1 with the list of my open items) and they are empty. I've also substituted the current item with the following expression:

item()?['SharepointColumnName']?['Value']

The column name is the column (Lookup column) and the Value is where the email resides (I saw the Select Output). I use the expression in the filter array and in Email as the recipient. But still have the same scenario, 5 empty emails.

[spupazza10]

Thank you @ekarim2020 . I've solved it. For the email, in the first 'select' I used the formula:

item()?['SharepointColumnName']?['Value'] but as you suggested above I switched to text-mode.

In the second 'Select', when adding the dynamic content, it would create a second Apply to Each loop, which causes to send emails with the same item listed three times. Therefore, I've found this video:

Power Automate Get Unique Values from Array | Distinct Items from Excel, SharePoint & Dataverse (you...

It uses the formula: item()?['ColumnName'], which would not create a second Apply to Each loop, and solved all the issues. I'm receiving 1 email with a list of my open items.

Thank you for your help.