Microsoft Lists – Issue Tracker template: Summary email of active items with Power Automate

One feature that is not included in the Microsoft Lists/SharePoint lists templates but can be quite useful is the ability to get a summary of active items at a frequency of your convenience.

Luckily a simple Power Automate Flow can achieve that. Check how to build this flow below.

Issue Tracker List sample

For this example, consider a scenario where you have used the Issue Tracker list template, and you want to consider as active only items with status New or In Progress.

In the example below, we have two items marked as Duplicate and one as Completed, which should be ignored by the summary email:

Recurrence Trigger

When creating your Flow, select the Scheduled cloud flow option, or create a blank flow and add Recurrence as the trigger. Set it to the desired frequency. In the example below, it’s configured to run every week, on Mondays at 8:00 AM.

Retrieve Active Issues

Next, use the SharePoint ‘Get items’ action pointing to a list created using the Issues list, and set up the following OData query (or if you are using the preview version, include the conditions to get both statuses ‘New’ and ‘In Progress’):

Status eq 'New' or Status eq 'In Progress'

Any item with a different status won’t be retrieved.

Pre-Select and format fields

This step is to pre-select only the desired fields and change their display names in a friendly way if needed. Add the ‘Select’ data operation action, passing the return from the Get Items action.

In the example below, 4 fields are used:

• Title – Renamed as Issue
• Issue Description – Renamed as Detailed Description)
• Logged by DisplayName – Renamed as Logged by)
• Assigned to DisplayName – Renamed as Assigned to)
• Date Reported – formatted to the proper format ‘dd/MM/yyyy’ using the formula:

if(not(empty(item()?['DateReported'])),formatDateTime(item()?['DateReported'],'dd/MM/yyyy'),'')

Generate HTML Table

After transforming the fields, use the ‘Create HTML Table’ action to auto-generate an HTML table from the items so it can be emailed later:

Email the contents

To wrap up, select the action ‘Send an email (V2)’ (the same can be achieved with other email actions that support HTML content) and add the output of the ‘Create HTML table’ to the body of your message:

Final Layout of the flow:

The final layout of the actions on the flow will look like the following:

Results/Conclusion

When your schedule triggers the flow, or when you trigger it manually, you will get your summary in an e-mail message:

In a few steps, we can extend the out-of-the-box Microsoft Lists templates using Power Automate.