Note: The steps in the Create HTML table action have been updated from my original post, on Dec 21, 2020, to address an issue where the Filter array columns were no longer available in Power Automate.
This post is inspired by a question I received from a previous blog post where the user wants to send a single email reminder for multiple Microsoft List items. The typical Power Automate pattern is one reminder per item. Consolidating items, by owner for example, is harder that you would think. I am using the Issue tracker template for my demo and we will send a single email message for all open items assigned to the same user on a weekly schedule.
Create a new Flow from Power Automate > Create new > Scheduled cloud flow. Provide a Flow name, i.e. “Issue tracker – Weekly report” and click “Create”.
Insert a SharePoint – Get items action and set:
- Site Address and List Name to your target site and list
- Filter Query to:
Status ne 'Completed' or Status ne 'Duplicate'
Insert a Data Operation – Select action and set:
- From to “value” from the previous SharePoint – Get items action
- Map to “Person or group the issue is assigned to” from the previous SharePoint – Get items action
Insert a Initialize variable and set:
- Name to “varAssignedto”
- Type to “Array”
- Value to the following expression:
Save and test your Flow. Copy the output from the Initialize variable step.
Insert a Data Operation – Parse JSON step and set:
- Content to “varAssignedto” from the previous Initialize variable step
- Click “Generate from sample” and paste in the copied output from the Initialize variable step
Insert a Control – Apply to each action and set:
- Select an output from previous steps to “Body” from the previous Parse JSON step
Add a Data Operation – Filter array action and set:
- From to “value” from the SharePoint – Get items action
- Choose value to “Claims” from the Parse JSON step; “is equal to”; and Choose value to “Person or group the issue is assigned to Claims” from the SharePoint – Get items action
Add a Data Operation – Create HTML table action and set:
- From to “Body” from the previous Filter array action
- Columns to “Custom”:
- Header: Title – Value to expression “item()?[‘Title’]”
- Header: DueDate– Value to expression “item()?[‘DueDate’]”
- Header: LinkToItem– Value to expression “item()?[‘Link’]”
The output from the Create HTML table can be messy but there are options to format using CSS if so desired. In my example, I specify Title for the issue name, due date and a link back to the item (See image below). A nice upgrade to this listing would be to combine Title and the link into a single clickable HTML tag.
Finally, add a Send an email (V2) action and set:
- To “Email” from the Parse JSON step
- Subject to “Issue tracker – My items”
- Body to “Output” from Parse JSON step
My completed Flow looks like the image below.
Save and the test the Flow. If all goes well the persons assigned to the issue will receive a single email with all items assigned to them.
I like the idea of this pattern but I would invest time into formatting the HTML table for the best possible user experience prior to production use.
Thanks for reading!