Using Sharepoint, Microsoft Flow (Power Automate) and Power BI to track capital expense requests

Problem/Challenge:

Employees were making expense requests via a SharePoint form that included a Microsoft Approval Flow sent to the finance department to action.  In turn the finance controller often needed to request for further information from the direct managers as to the necessity of the request.

These requests were causing too much handling and there was no method of tracking approval process. Too many staff were involved in the process even for small requests.

The finance controller requested a multi-step customized approval process for these expenses.   The suggestion was for in-line managers to approve the initial request,  amounts below $200 to be approved by the ordering department and amounts over $200 to be approved by the Finance Team.

The finance controller also requested a report that allowed her team to see the progress of these requests so they were not getting missed. This report would detail the exact progress of each approval process.  Currently managers and approvers had to manually check the approval status to ensure requests were being acted on in an appropriate and timely manner.

The Solution:

Office 365 SharePoint Online List

The existing SharePoint list was edited to include an Approval Status and Notes field, these fields are used to store current approval status and notes.

Microsoft Power Automate (Microsoft Flow)

We used Power Automate to detect a newly created request, this would send off an automatic Microsoft Power Automate request for approval to the appropriate manager.  The flow was setup so each expenditure requests was sent for approval to the department head. There are several ways this could be solved using flow but for ease of management and readability we decided to create two flows, an initial one that one that detects new items in the SharePoint list, this updates a status field and kicks off an approval request to the users direct manager. This was followed by a second flow that detects when the status has been updated by the Manager and sends through the approval request to the appropriate person depending on the requested amount.

 

As you can see my First Flow was quite straight forward:

The approval request begins and adjusts the status of the item to “Awaiting Approval” it then grabs the user’s manager based on settings in Active Directory. The Power Automate flow then emails an approval request to the appropriate manager which looks something like this:

The manager can review the details of the request by clicking on the link, then Select Approve or Reject. Once Approved (or Rejected) the manager is also presented the option to provide a reason for their decision before finally submitting it back to the list.  The requested is sent any rejections at this point.

The list is then updated with new status’s (either Manager Approved or Rejected)

The second flow then triggers when that status field is updated by the initial Flow. There’s a little logic built into the flow to determine where to send this second flow for approval. This one’s a little more challenging to provide a screen shot of the entire thing as the way Microsoft’s Flow lays out the logic it spans quite wide on each of the decision trees, so it wouldn’t fit clearly into one image for this page but each of the decision trees look something like this:

You can see each of the departments is checked through this logic to decide who is the best department head to request approval from, there are then other decision trees with similar logic for when it’s approved by various departments and managers to determine the appropriate approval course. From there the request is finally Approved or Rejected and reasons left for the user to see clearly in the initial SharePoint list and it’s kept the whole process automated while proactively seeking out the appropriate department heads and managers so they need only respond to one simple email upon request.

Microsoft Power BI to report on progress.

A power BI report was developed to report on currently open expense requests , highlighted the status of each request including any additional notes.

This report connects to the Sharepoint List and published to the Power BI service.

You can also schedule the report to send weekly to the appropriate staff.

 

 

Please contact us if you need any assistance [email protected]  or contact Kevin Carne on 03 93127444.

 

Approvals With Flow