Have you ever thought about structuring data in MS Flow like grouping? In other tools this operation is very straight forward, just as far as a simple click of a button. In one of my current projects I faced this common scenario to be handled within MS Flow and I’d like to share this with you.
Let’s imagine the following
- you have a table with multiple hour registrations for several projects
- each project has a project manager
- you’d like to send a list of consumed hours to the individual project managers using MS Flow
As mentioned above, this task is very common and easy to do in tools like PowerBI or Excel. To illustrate what we’d like to achieve, I did the same in MS Excel using a Pivot Table and copied a filtered view to MS Outlook.
Let’s get started by just creating a blank flow.
Adding the Input Table
The first task now would be to add your input table to flow – in my example this is MS Dynamics Finance and Operations with entity “Journal Trans” which holds all desired project transactions.
Building the Project List – Part 1: Empty Array
Next thing to be added is an empty array where we’d like to write the unique project IDs to.
- Add an “Initialize Variable” action
- Give it a name – I named it “UniqueProjects”
- Set the type to array
- Set the start value to “”
Building the Project List – Part 2: Process All Line Items
Now as we have all prerequisites ready we can start to process the line items – one by one – to figure out which project IDs we have in the list. What we now need is a “Apply to each loop” to call each line item. The input for this action now needs to be your input table – in my case the list of items I called from D365 Finance and Operations.
Building the Project List – Part 3: Find Unique Items
Next thing to achieve is to find unique values in the list and write them to our array variable. MS Flow doesn’t come with a proper grouping feature but comes with a neat function which
- allows us to union values
- Skipps all values which are already in the list
To use this function we now add a compose action (1) and set the value using an expression.
The expression one by one…
|union||combines multiple uniqe values|
|variables(‘UniqueProjects’)||First thing to combine is our array variable|
|createArray(item()?[ProjectId’]||Second thing to combine is the project ID of the actual processed item which we put in the format of an array in order to fit to our array variable|
As a second action we use a “set variable” action (2). We’ll now set the variable “UniqueProjects” to the output of our union action. With each loop we’ll now
- Union the content of our array with the next project ID which is not yet part of the array
- Write the complete union back to our array
Check the results
We can now fire MS Flow to produce the first results.
Sending Emails: Part 1 – Process All Unique Projects
So far it’s been very easy. Let’s go ahead and build the list of line items for each project. As we already now, we’ll need an “Apply to each loop” to process all unique projects coming off our array variable.
One by one
|Apply to each||Input||Our array variable “Unique Projects”|
|Filter Array||Input||Our Input List, List of Transactions|
|Filter Array||Filter Column||Field ProjectId coming from the input list|
|Filter Array||Comare Value||The current cylce item|
Now as we have the desired items in place, we may put them in an HTML table which will also be the format we’ll use to dispatch the lines by email.
Let’s see what we have here
|Create HTML Table||From||The body/outcome of the previous filter action|
|Create HTML Table||Include Headers||Yes, as we’d like to have a header row in the table|
|Create HTML Table||Columns||Custom, as I’d like to define the columns which are included in the table|
|Create HTML Table||Header||The individual header name for each column|
|Create HTML Table||Value||The value to be included in the individual row. |
It’s just “item()?[‘FIELDNAME’]”
Seems we have everything ready to dispatch the emails containing the individual project hours for each project in MS Flow. In real life we’d also have to find the recipient somewhere in a table where project and project manager are married, but for now I assume we already have this information in place.
Sending Emails: Part 2 – Build Email
The last step will not bring too much suprises. In general we’ll create an “Outlook – Send an email” Action.
I’ve set the values as follows – as always feel free to adapt according to you requirements.
|Send an email||To||The recipient. I set this to a fixed value for now – in real life you’d place your picked project manager email address here.|
|Send an email||Subject||Some fix text + the actual processed item (project ID), Expression: items(‘Apply_to_each’)|
|Send an email||Body||The email text. I’ve put some fixed text here. The added output will place the HTML Table in here which we produced in the last step. Expression: body(‘Create_HTML_table’)|
|Send an email||Importance||When it comes to send emails using the outlook send an email action, it’s always good to set this value. If you don’t – Flow defaults to low priority.|
|Send an email||Is HTML||As I used some linebreaks in the body and we are going to send an HTML table, we have to set this value to “yes”.|
We are now finally ready to fire the flow 🙂 Let’s see the output!
Here we are. Of course there are some things we could do better, like rounding hours or formatting the date but most important – we managed to split the whole table by project ID.