Hey Flow Fans! Welcome to this Flow of The Week Written by Community Member Phillip Guglielmi!

Phillip is a Senior Enterprise Business Intelligence and Analytics Architect with my Microsoft Certified Solutions Associate in Data Management and Analytics and Certified Public Accountant designation. As a diverse hybrid, I have a deep understanding of the interface between business and IT with advanced technical skills in cloud based and on premise applications including SQL, Azure SQL Data Warehousing, enterprise data warehousing, OLAP cubes, SAP HANA, Dynamics 365, OBIEE, Azure Machine Learning, Azure SSAS, SSRS, Tableau, Power BI, SSIS, PowerPivot, Power Query, PowerApps, Flow, and Excel. Currently, He is developing with program language including C#, R, jQuery, JSON, REST API’s, OData, HTML5, JavaScript, MDX, and DAX. My goal is to support others in the community through shared knowledge as I seek to strengthen my skills in key areas of enterprise operations and the technologies transforming them, including data architecture, process automation, governance and strategy, future state enterprise data warehousing, and computer science.

Be sure and leave some comments below, letting him know what you thought of the post!

A robust, Client-specific approval workflow and write-back engine is not a standard feature of many ERP systems, which requires BI architects and data engineers to think outside the box to deliver scalable solutions. Microsoft Flow can meet these enterprise level requirements, and can do it at a fraction of the time and cost of any other solution on the market. This article will tackle the current business requirements pertaining to enterprise level solutions around on-premise SQL data warehouse write-backs, robust tiered approval cycles, and sales order approval requests.

The Approval Scenario

1. The Business requires an on-premise SQL Data Warehouse utilizing Dynamics 365 data loads and needs all the approvals to be written back to their on-premise data warehouse.
2. Requestor completes a sales order form and routes the form to the first level of approval (the “Owner”). Once the owner has approved, it is sent to the second level of approval (the “Approver”) who will authorize the release of the sales order the request.
3. The authorized owner and approver names and e-mails (owner and secondary) on each sales order number are included in user-defined fields in the DimEmployees entity in Dynamics 365 and data warehouse.
4. Only open sales orders will be brought into the approval cycle to avoid duplicated e-mails to the approvers.
5. Every sales order could include numerous products. One of the requirements it to provide a comprehensive list of all products within each sales order, and provide them to the owner. Therefore, sales orders will need to be grouped and provided in the body of the approval.
6. The owner and approver require their being able to review and approve a sales order without having to access the system (i.e. via their phone or e-mail).
7. The sales orders pending approval will remain in the approval cycle (loop) until approved by the final approver.
8. If the sales order is rejected, it starts the approval cycle over again and comments are written back to the on-premise SQL to highlight the change(s) that need to be made before the manager will approve.

Overview of the flow

1. Trigger the flow through Recurrence.
2. Execute the SQL stored procedure to mark sales orders as ‘Pending’.
3. Select the desired columns for deduplication and parsing later.
4. Join the array into a single text string with a strategic delimiter.
5. Start approval cycle and loop until the sales order has been approved.
6. Execute a SQL stored procedure to write-back the approval status, approval date, modified date, and any comments from the approver to the owner.
7. If sales order is rejected, notify the owner and write-back to the data warehouse while remaining in the approval cycle.

The scenario described in this tutorial will operate on a SQL stored procedure and Approvals, but you are more than welcome to substitute any data that produces an array within Flow so that you can follow along: a SharePoint list, a list of documents in OneDrive, tags from Computer Vision API, etc.

Please see attached link to the AdventureWorks data warehouse to follow along with the blog post.

The next article I write will use the new Excel Connector and Common Data Service.  

The trigger

I will walk through how to build this flow from scratch, but you can also use this template to follow along yourself.

1. “Recurrence”

You can use the Recurrence trigger as we will be using an OData filter to find open transactions only.

2.  “Get Rows” & “Execute Stored Procedure”

Insert a step after the Recurrence and search for "get row" and select the option for "SQL Server – Get rows."

Use an OData filter to return Sales Orders that are ‘Open’. We will immediately mark these rows as ‘Pending’ with the Execute Stored Procedure.

Insert a step after the Get rows and search for “Apply to each.”

Insert a step after the Apply to each and search for "SQL Server – Execute stored procedure."

Running a SQL Procedure to update the rows that are ‘Open’ to ‘Pending’ is important because we do not want those records re-entering the Approval cycle again, thereby effectively keeping them in the Approval cycle until approved (or rejected). Upon rejection, they will remain in the loop. Therefore, only new sales orders will be picked up by the approval cycle.

Creating variables and arrays for the approval

3. "Select the desired columns for deduplication and parsing later."

Insert a step after the ‘Apply to Each’ and search for "Data Operations – Select."

Click into the From field and use the dynamic content box to choose the results of the "Get Rows" step.

Based on the business requirements, we need to group all Sales Orders by the column SalesOrderNumber as the approvers would like to see the comprehensive list of products that make up the sales order.

There are two columns in the section for Map: "Enter key" on the left and "Enter value" on the right.

Click into each row in the "Enter value" column and use the formula below:

coalesce(item()?['SalesOrderNumber'],null)

Navigate to https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-isblank-isempty for more information on the coalesce function. This is one of my favorite functions because if all of the arguments are blank, then the function returns blank, rather than an error.

4. "Union the desired columns for deduplication."

Insert a step after the ‘Select’ and search for "Data Operations – Compose."

The select step has reduced the get rows step to the sales order number, but the data is still a table. In order to turn the table into deduplicated list and finally an array to be grouped, we need to union each row.

union(body('SelectLIDCODE'),body('SelectLIDCODE'))

This union function returns a single array or object with all the elements that are in the array or object passed in. For parameters, this function only requires either the array or object from where we want all the items. The parameters for this function can either be a set of objects or a set of arrays, not a mixture of both. If there are two objects with the same name, the last item with that name appears in the result.

5. "Variables – Set Variable."

Insert a step and search for "Variables – Set Variable."

The Variables connector is one of the more important connectors. To use a variable you will first have to initialize a variable. Here, we will start our flow with a list of initialize variable actions as we not yet able to initialize variable actions in a single scope box.

To do this, we will initiate a variable to track whether the owner and the approver have approved a sales order.

Type will be Integer and the Value will be set to 0.

6. "Variables – Set Variable for the sales order number."

Insert a step and search for "Variables – Set Variable."

Add variable->”Initialize variable” action, Name set to Initialize LIDCODE, Type set to Array and set Value to the Output from the Compose.

Below is an extra screenshot to show where which Output to use.

Now that we have set up the ex-ante variables and arrays, we will now start the approval process, which will proceed until all the sales orders are approved. Watch this awesome tutorial by Jonathon Levesque about kicking off the approval process to include timeouts and escalations, read this article by Merwan Hade on setting an approval expiry, and this article by David Blyth on approval reminders.

Setting-Up the Approvals

7. “Send every sales order in a batch with all the details”

Insert a new step and navigate “Apply to each.”

Now that we have a unique list of sales order numbers, we can add an “Apply to each” on the list of sales orders and create an approval and write-back the response to SQL. An apply to each loop makes it possible to control the list that you repeat over.

Insert a step and search for "Data Operations – Compose."

Use the compose operation to retrieve the sales order number from the apply to each.

@items('Send_each_sales_order_indivdually')['LIDCODE']

It is very important not to forget to make your “Apple to each” execute in parallel by going to options and enabling “Concurrency control”.

8. "New step and Do Until loop."

Insert a step and navigate “Add a do until”

We set up the “Do Until” where the exit condition is when the isSecondApproved variable becomes 1 and the sales orders array is empty (meaning they have all been sent out). The variable will be checked at the start of every loop iteration, and once the approval is complete and variable set to 1, it will exit the loop iteration. 

Do until loop. This loop will continue until both the approver(s) have approved the sales order. That will start out looking like this, once you add the approval step in:

@equals(variables('isSecondApproved'), 1))

9. "Filter for the first sales order to be sent for approval.”

Insert a step and search for "Data Operations – Filter array."

The From will be the original get rows step and we will click on the Edit in advanced mode.

@equals(item()?['SalesOrderNumber'], outputs('SalesOrderNumber'))

10. "Select the sales orders that will be grouped.”

Insert a step and search for "Data Operations – Select."

Again, we use coalesce to avoid any errors in the output.

First level of approval – Owner approval

11. "Check if there are any owner level approvals pending.”

Insert a step and search for "Data Operations – Compose."

We are checking if anything is currently open for the owner. If there is, we will set up the approval to begin with the owner, otherwise, the approval process will start with the secondary approver.

length(body('MasterSelect'))

12. "Data Operations – Join to get the owners.”

We need to create an array that captures the approvers who will be responsible for approving the sales order. By creating an array and then joining them, we will be able to add the output to the approval.

Similar to step 3 above, we will select the columns we need to deduplicate and join.

Insert a step and search for "Data Operations – Select."

Insert a step and search for "Data Operations – Compose."

Insert a step and search for "Approval."

Utilizing the output from the compose data operation above, we join all potential approvers that are on the sales order.

join(outputs('a1EmailUnion'),';')

Full shot:

Insert a step and search for "Condition."

We are going to set the condition as Response is equal to Approve.

If you are working in sharepoint and would like to know how to get a dynamic list of approvers, check out this flow of the week about sending parallel approval requests for a dynamic set of approvers.

Once the owner approves the sales order, there is a stored procedure that writes back the on-premise SQL Data Warehouse the results, and the second approver is set to pending.

We will not worry at this point what happens when the Owner rejects in this scenario. However, we will cover what happens when the second owner rejects the sales order. We will cover that now.

Second level of approval – Approver

13. "Setting up for the second approver.”

Insert a step after the SQL stored procedure and search for "Data Operations – Select."

Once the owner has approved the sales order, it is now the second owners turn to approve it. We are going to follow the steps we performed in setting up the owner:

1. Select the Approvers e-mail address from the body of the ‘Filter array’ action
   1. Insert a step and search for "Data Operations – Select."

concat(item()?['ApproverEmail'],'')

2. Compose a union the Approvers e-mail address from the output of the Select operation
   1. Insert a step and search for "Data Operations – Compose."

union(body('Selecta2Emails'),body('Selecta2Emails'))

3. Join the Approvers e-mail address from the output of the compose operation and add them to the Assigned to field in the Approvals action
   1. Insert a step and search for "Approval."

join(outputs('a2EmailUnion'),';')

The important part here is to make sure to use the selected e-mail columns and compose action to create an array for the secondary approvers e-mails. Finally, we will use the join function to combine all potential approvers on the sales order.

The final difference will be in how we handle is the second approver rejects the sales order. We will send the owner an e-mail notifying them that the sales order has been rejected, and then we will set the variable so that the loop will resend the owner the approval.

14. "Handling the Approving and Rejecting of the second approver.”

Finally, just as we did with the owner we will make sure to set our variables depending on the outcome of the secondary approver’s decision.

Sales Order is Approved by Approver:

1. “SQL – Execute Stored Procedure” and enter the RowID as the Output from the ‘Filter_array’ action, and the ApprovalDate as utcNow() function.   
   1. Insert a step and search for "SQL – Execute Stored Procedure."

2. “Variables – Set Variable," and set the Name ‘isSecondaryApproved’ to 1 which will end the ‘Do Until’ function we created, and that sales order will exit the loop function.
   1. Insert a step and search for "Variable – Set Variable."

Sales Order is Rejected by Approver:

The Owner will receive a notification the sales order has been rejected along with comments from the Approver on the modifications that are required for approval. 

1. Set up an Apply to each approver response, information and add the Approver’s comments to be written back to SQL and added to the rejection notification back to the owner.
   1. Insert a step and search for "Apply to each."
2. “SQL – Execute Stored Procedure” and enter the RowID as the Output from the ‘Filter_array’ action, and the ApprovalDate as Response date.  
   1. Insert a step and search for "SQL – Execute Stored Procedure."

3. E-mail back to the Owner.   
   1. Insert a step and search for "Email."

4. “Variables – Set Variable," and set the Name ‘isSecondaryApproved’ to 0 which will trigger the ‘Do Until’ function to run again and send a notification to the Owner to make changes. This sales order will remain in the loop function for now.
   1. Insert a step and search for "Variable – Set Variable."

Next Steps

Future developments will look at allowing the manager to delete a rejected record or cancelling the sales order. Maybe even a purge process that has been out there for an extended amount of time. There are many avenues to take the process, and the goal to inspire and encourage curiosity with the Power Platform and Flow.

My next posts will be around Approvals in the Common Data Service and Dynamics 365.

Let us know in the comments if you like this post as I will be developing enhancements to this Flow.  

https://flow.microsoft.com/en-us/blog/approvals-in-cds-and-seven-updates/

For formatting approvals to, use markdown language at: https://docs.microsoft.com/en-us/flow/approvals-markdown-support

For an amazing article by Stephen Siciliano on Rich text formatting with Approvals, please see the link below:

https://flow.microsoft.com/en-us/blog/rich-approvals-text-and-multiselect/