After my Advanced Flow session at the Microsoft Ignite Hong Kong, an attendee asked me about a specific update that could be done to the demo. What she wanted to do is add some additional information to an auto-generated email, namely not just showing data grouped together, but also showing the count of the items within a group. If you’re curious about the overall demo in general, you can review the video from the large Microsoft Ignite 2018 conference on youtube:
For my demo, what I was using is a list called ‘Service Requests’ with a few columns. To simplify my example here, I’m only going to use Title, Status (Choice column with ‘Open’, ‘Closed’, ‘Deferred’, ‘In Progress’ as possible values), and Created By.
What I want to achieve is to receive a daily email with a list of all service requests, grouped by their corresponding status, and including the count of the requests with a specific status. For better illustration, here’s the final result already:
Most of this was already done as part of the session demo, but adding the “In Progress: 2”, as an example, on top of the individual group blocks was not available yet. So, after the attendee asked if and how this is possible, I made some changes to my flow to include it. Here’s how it’s done:
First of all, here is the complete flow, starting with a “Recurrence” trigger to start it once a day. The “Get Items” step retrieves all items from my SharePoint list.
Next, we are retrieving a list of all status values from the list of SharePoint items which we retrieved, via a “Select” action. However, we want to get a set of distinct statuses, so we are also initializing a new variable in the “Initialize Unique Status” action. We call it UniqueStatus, set the type as “Array”, and set the following Expression as the value: union(body(‘Get_Status_Values’), body(‘Get_Status_Values’)). Yes, we are doing a union (so, a merge) of the status array with itself, which returns unique values.
Next, we are initializing a smaller helper variable called HTMLBody, which we’ll use to store the content of the email which we’ll send at the end of our flow.
Now that we have all the unique statuses in our UniqueStatus array, we loop through them with the “Apply to each” action and perform a few actions. Here’s the overview first:
In the “Filter array” action, we are simply filtering our overall item set which we retrieved from SharePoint to only include those that match the current status, as provided by the “Current Item”.
Now that we have a set of all items for a specific status, e.g. “In Progress”, we updated our HTMLBody to include the “In Progress: 2” mini-header as seen in the screenshot at the top of this post. We use the “Append to string variable” action, and use the following value:
<b>@{items(‘Apply_to_each’)}: @{length(body(‘Filter_array’))}</b><br/>
Next, we create a HTML table to show the values that interest us – ID, Title, and Created By (the last one is represented by the expression item()[‘Author’][‘DisplayName’] expression). You could also create your own table, or select other columns if wanted/desired, together with additional expressions. Check the video for more inspiration!
We append the HTML table to the HTMLBody variable, and afterwards add some more content (I kept it simple and simply added 2 new lines).
Once that’s done, the “Send an email” action is used to send a HTML mail with the HTMLBody variable in use for the Body. No screenshot here, this should be the easiest action of them all.
After the Flow was executed, you can also review the individual steps and check the variables, inputs, and outputs. Here’s for example a part of the “Apply to each” step, where we prepare the group header with the count, and the corresponding table:
