How to Create a Simple Approval Flow with Microsoft Flow

Have you ever wanted to watch a SQL Server table, a SharePoint list or any kind of data source and as rows come in, run a series of rules? Microsoft Flow is the tool to do this and, in this demo, I’ll show you how to create a simple approval flow.

In this example, I have a table of timecards and if someone was looking to add overtime to their timesheet, I want to be able to approve that overtime automatically through a series of Microsoft Flow events.

I’m setting up this Flow so if a row is inserted that is more than 8 hours (someone looking for an overtime request), it will go through the approval process that I set up and will come through Flow as an email, an app and on the Flow website portal and if it’s less than 8 hours it will automatically approve it. Let’s run through this: 

• I start with a table that has a project ID which will relate to another table, so I’ll have to do a join to that other table. I could update inside of Flow, but I chose to make it simple by creating a stored procedure that does an update for me. This will update the table and set the approved status to be approved or rejected based on the timecard ID.
• Next, I go to Flow and when I’m there, you’ll see a tab for approvals which shows approvals I’ve already done (or later that it will show ones in process). There are many other ways to get access to those like email or phone app – a great feature of Microsoft Flow.
• I click on My Flows and create a new flow entry to watch that table. Although there are templates available, I’m going to create a flow from blank as I’m watching a SQL Server table.
• I click on Create from Blank and then every flow must have a trigger and actions upon that trigger. I put SQL Server in the search bar, and I get a series of actions we can watch for. In this case, I want to watch for a record that’s being created.
• It’s important to note that I must have an identity column on this table so it can track ‘what is the latest ID I’m looking at’?
• Next, I’ve already created a connection, then in the drop down on Table Name, I point to my table, in this case, my timecard table. That’s all there is to watching that table – super simple.
• Now to add the triggers and actions I add a new conditional step to say, ‘if it’s more than 8 hours, then do my approval process’.
• To do this I click on Control and then Condition; this will be an if/then statement.
• I choose a value – I want to look for the number of hours and if they are greater than or equal to 8.
• Then I add an action to the Yes and No boxes; if Yes, do the approval process, if No, I’ll add an action to automatically approve it.
• So, under No, I choose a SQL Server action and I’ll execute a stored procedure. I’m going to run the stored procedure 3 times, once for approving automatically and once for the manual approvals.
• I enter the stored procedure name (TimeCardStatusChange) and put Status as Auto Approve and add the timecard ID. Flow is looking at the data types and telling you that this data type needs an integer; because of that, it’s choosing the right one in my case.
• On the Yes side, I do a search for my actions and I do an approval action. I add my if/then as greater than 9 hours (to cover that overtime) and I’ll choose ‘Start and wait for an approval (V2)’ as my action.
• I’m planning to send this to 3 people, and I select the approval type. I can choose ‘First to respond’ (maybe the business owner or project manager) and we’ll move it on or I can choose so that everyone who I send it to has to respond and approve before this person gets the overtime, as well a number of other choices.
• For this one, I choose ‘First to respond’ as the approval type from the dynamic content drop down, then give it a title (Timecard User), as well as an subject/title for the email or alert that will go out: Time card user has entered more time than 8 hours, do you approve?
• I also must choose who this is assigned to (the person(s) email doing the approval).
• Next, I need to set up another condition to ask ‘did they approve or not’ after the first action happens. Here I choose what response or outcome came out of that (approved or not?). I select outcome and then Approved. I could also add many other dynamic content such as add comments where the approver can tell why/why not the OT was approved.
• The I add an action on the Yes side that if it was approved, it will execute a stored procedure by putting in the procedure name. And on the No side, if it wasn’t approved, I’ll set up a stored procedure action. Both will look similar with the same Timecard ID, but status will be approved for Yes and rejected for No.
• Finally, I give my flow a title and save it.
• To test this, I go back to my Management Studio, open my SQL Timecard Table and add a row for 6 hours and one for 10 hours and see what happens.
• I can then go back to My Flow and see it is watching this table and I quickly get an alert on my phone that two approval requests have come in. I can open that up for the 10 hour one and I can reject that row or auto-assign it somewhere else. I can also see these approvals in my approvals window in Flow or in my Outlook email.
• If there is an issue, I can go back into my Flows and easily dig in to see what’s happening behind the scenes each step of the way and debug these workflows.

So, this video/blog gave you the basics of watching a table in Microsoft Flow and giving approvals based on that. Keep an eye out for future videos that dig deeper into this technology. If you want to learn Microsoft Flow, our On-Demand Learning platform has Intro and Advanced Microsoft Flow courses. Sign up for a free trial and check out those classes today!