In 2020 while COVID was causing lockdowns and remote learning I was asked to take on a little project, it wasn’t even a project then. It was more of a “Can we do this?” A question that became a massive project. So what was that project? Could I create an approval process workflow that could handle our schools Professional Development requests?
Our current system was old and needed to be upgraded, and we had reviewed paid workflow applications and none fit the bill of what we wanted. So I took on a challenge, could we use Microsoft Power Automate to make our own! This would mean we needed to use Microsoft Forms as well, though it should work… and to quote Jeremy Clarkson, how hard can it be?
I first started with creating a simple form, it had a few questions and then had it trigger a Power Automate Flow that would ask people for approval. Simple right?
We’ll no, I ran into issues quickly where the approval process worked but it didn’t know what to do with the answers. I realised I needed to tie in SharePoint Lists to ensure that the data was collected to check afterwards, one of the issues my first test had was there was no way to check what had been approved and what had not. The only form of approval the school had was an email, and if that was deleted we had no record of the approvals or denials that were easy for people to see.
So I added SharePoint Lists to the design. And I built a great system that worked well (you can see below the workflows). It had become staged workflows with different out points but I ran into an issue. You can only have so many nested groups in a Flow, when you hit that level you can’t go any further down and have to come up with another way. So for me, I had to create multiple Flow’s… it’s always fun to make things more complicated.
I now had the following;
Workflow 1 – Staff submits a PD request;
A Microsoft Form would be filled out, Workflow 1 would then add a new line on a SharePoint list.
Workflow 2 – A new line has been added to the SharePoint list;
Once workflow 1 created a new line on the SharePoint List it would trigger Workflow 2. This asked a series of questions stepping from Head of Department, Head of School.
This initial testing then showed that it seemed to be working well, though a few issues came up.
- If a PD request was above a certain amount it required the Headmasters Approval.
- The time and date on the flows were all UTC.
- I had done too much scripting for the schools liking.
Number One was pretty easy to achieve, with an if statement that asks if the cost is over $1,000 send to the Principal for Approval, though if under $1,000 it would then be approved. I did make one slip up here, in my head approvals had to be a Yes and denies had to be a No, though since this final step to get Headmaster approval was over $1,000 or no, a No was also a yes. But when I got my head around that it all came together.
Number Two was a quick code change, re-formatting each query to go into GMT+10. The fact that Microsoft have a support document on how to do this frustrates me, it can’t be that hard for them to just include a Set Timezone on your flow?
You can follow the steps from this link: https://support.microsoft.com/en-us/topic/converting-time-zone-in-microsoft-power-automate-8bce2441-1859-4d76-2f51-985d9c96534d
Number Three was the most amusing to me, I had scripted the system to do some maths so the user didn’t have to. Once added to SharePoint Lists, Lists would then do some calculations to add up the Travel, PD Cost and Accommodation into one total cost. And then it also divided that cost across the number of staff going, so the approvals would get a sub-total of say $5,000 for a PD, though with only three staff attending it would only cost $1,666 for each staff member. Though some staff were not paying enough attention to the costs, so we removed that feature so that more addition had to be focused on the total cost.
With those changes made the system was working well, though it did end up slightly more complicated… as I said, how hard can it be?
There are effectively three areas of the school, Admin, Junior and Senior. You see since each area has a different structure for leave approval I needed to segregate the setup. So the final solution ran like this. My workflow does loop both Junior and Senior under Teaching Staff, since the only change is WHO gets alerts I didn’t see the point in having repeated files.
If you want to see a more in-depth run of the workflows they are below.
Now you can see two of the remaining flows, again like my above graph, I have only showed the Senior and Admin Flow, I didn’t see the point in posting two versions of the same thing for Senior and Junior.
Sorry for the small quality, WordPress won’t let me put it in a higher quality.
Senior School Flow 
Admin & Ops Flow
We do still have some issues, one of which is the nature of how workflows operate. You see we have a few situations where a staff member submitting a PD request have to approve it themselves, this becomes frustrating and currently, we have no way around fixing it with the current workflow. For example, if a Head of School submits a PD request, it goes to themselves twice as the HOD and then HOS to approve before it goes to the Principal. One way we are going to get around this is to create an Executive branch of the workflow that doesn’t have the Head of Department Check level.
Another issue we have found is that due to the potentially chaotic nature of schools staff aren’t always in front of their email ready to hit submit a Professional Development request. As a request sometimes the requested email is missed and requests seat there pending, I have built a PowerBI dashboard to track the requests, however, it is not currently in use since our licensing for PowerBI does not allow for the automatic update of that dashboard. An ongoing problem I am hoping to fix with an OData sync to our PowerBI Server, though I haven’t done that yet.
To combat the issue, one thing we have been doing is teaching staff to use Microsoft Teams to check for approvals, in there you can see those that you have submitted and if they are approved or denied. But also any you need to approve or deny. It would be good if Microsoft could do two things here.
One, create an Approvals tab in Outlook like in Teams so that staff and easily check things there.
Second, allow for the adding of Approvals on office.com. Currently when you log into there you have options for quick links own the side, some recommended actions and folder quick access. It would be great if a pending approvals section as there.
Overall the system works, it has some flaws and bugs that we need to work on, though since the only real cost the school has had to wear is my time I label it a success. Yes, my time is not free, however with Remote Learning and COVID my workflow was reduced thanks to our work with Identity1 and automating Teams groups.
Once I get my PowerBI dashboard working I will let you know how it goes.
Leave a Reply