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.

Keep reading to learn how your current business requirements, including SQL data warehouse write-backs, robust tiered approval cycles, and sales order approval requests can be addressed using Microsoft Flow and the new Power Platform.

The Approval Scenario

1. The Business requires an on-premises SQL Data Warehouse utilizing Dynamics 365 data loads and needs all the approvals to be written back to their on-premises 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 is 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-premises SQL to highlight the change(s) that need to be made before the manager will approve.

Overview of the Approval Workflow

1. Trigger the flow through Recurrence.
2. Execute the SQL stored procedure to mark sales orders as ‘Pending’.
3. Select the desired columns for de-duplication 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 as shown below, but you are more than welcome to substitute any data that produces an array within Microsoft Flow so that you can follow along: a SharePoint list, a list of documents in OneDrive, tags from Computer Vision API, etc.

Microsoft Flow Triggers

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

Microsoft Flow Recurrence Trigger

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

Get Rows & Execute Stored Procedure Flow Triggers

1. Insert a step after the Recurrence and search for “get row” and select the option for “SQL Server – Get rows.”
2. Use an OData filter to return Sales Orders that are ‘Open’. We will immediately mark these rows as ‘Pending’ with the Execute Stored Procedure.
3. Insert a step after the Get rows and search for “Apply to each.”
4. 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 Process

Select the desired columns for de-duplication and parsing later

1. Insert a step after the ‘Apply to Each’ and search for “Data Operations – Select.”
2. Click into the From field and use the dynamic content box to choose the results of the “Get Rows” step.
3. 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.
4. There are two columns in the section for Map: “Enter key” on the left and “Enter value” on the right.

5. Click into each row in the “Enter value” column and use the formula below:

The coalesce function is one of my favorite functions because if all of the arguments are blank, then the function returns blank, rather than an error.

Union the desired columns for de-duplication.

1. 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 de-duplicated list and finally an array to be grouped, we need to union each row using the formula below:

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.

Creating Set Variables

1. Insert a step and search for “Variables – Set Variable.”
2. 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.
3. To do this, we will initiate a variable to track whether the owner and the approver have approved a sales order.
4. Type will be Integer and the Value will be set to 0 as shown below.

Creating a Set Variable for the sales order number

1. Insert a step and search for “Variables – Set Variable.”
2. 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.

Setting-Up the Approval Workflows in Microsoft Flow

To send every sales order in a batch with all the details

1. Insert a new step and navigate “Apply to each.”
2. 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.

3. Insert a step and search for “Data Operations – Compose.”

4. Use the compose operation to retrieve the sales order number from the apply to each, using the formula below:

@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”.

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.

The “Do Until” 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 using the following formula: @equals(variables(‘isSecondApproved’), 1))

Filter for the first sales order to be sent for approval

1. Insert a step and search for “Data Operations – Filter array.”
2. The From will be the original get rows step and we will click on the Edit in advanced mode using the following output formula: @equals(item()?[‘SalesOrderNumber’], outputs(‘SalesOrderNumber’))

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 Sales Order Approval Process- Owner Approval

Check if there are any owner level approvals pending.

1. Insert a step and search for “Data Operations – Compose.”
2. 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 using the formula below, otherwise, the approval process will start with the secondary approver.

Formula: length(body(‘MasterSelect’))

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. We will then select the columns we need to de-duplicate and join.

1. Insert a step and search for “Data Operations – Select.”

2. Insert a step and search for “Data Operations – Compose.”

3. 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, using the formula: join(outputs(‘a1EmailUnion’),’;’)

Full shot:

4. 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 article 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-premises 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.

Second Level of Approval Process – Approver

Setting up for the second approver.

1. 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:

2. Select the Approvers e-mail address from the body of the ‘Filter array’ action.

• Insert a step and search for “Data Operations – Select.”

concat(item()?[‘ApproverEmail’],”)

3. Compose a union the Approvers e-mail address from the output of the Select operation.

• Insert a step and search for “Data Operations – Compose.”

union(body(‘Selecta2Emails’),body(‘Selecta2Emails’))

4. 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.

• Insert a step and search for “Approval.”

join(outputs(‘a2EmailUnion’),’;’)

5. 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.

Handling the Approving and Rejecting of the second approver.

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.

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.

If the sales order is approvedby Approver:

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

• 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.

• Insert a step and search for “Variable – Set Variable.”

If the 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.

• 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.

• Insert a step and search for “SQL – Execute Stored Procedure.”

3. E-mail back to the Owner.

• 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.

• 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 is to continue enhancing the capabilities of Microsoft Flow and the Power Platform. To learn about all the features of Power Platform, click here.