cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User
Super User

From 5 Days to 5 Minutes

Hello everyone!

 

This post will show you how I used Power Apps, Power Automate and Power BI in an integrated solution that relies on each of the products playing their part to take what was a massive and cumbersome process and turn it into an intuitive and simple one.

 

 

Health & Safety Auditing Solution

 

Technology:

 

Power Apps

 

  • Handles the 'actionable' side, creating audits, creating action-items for audits, completing audits, actioning tasks, extending tasks, completing tasks.

 

Power Automate

 

  • Handles the automation of creating Actionable tasks from Audit tasks, task creation emails, task reminders, task escalation for overdue items, upcoming audit reminders, setting of overall audit risk, checking for overdue tasks

 

Power BI

 

  • Handles the data reporting side, based on the information created by Power Apps and Power Automate, and allows users to filter and then export the item to send to managers or directors.

 

 

Introduction:

 

Every company has some kind of health and safety audit process, and a large number of ours are for physical sites. The audits cover a large number of topics and were between 15 and 40 printed pages each. The goal was not only to recreate these forms digitally, but to find a way to encompass the end-to-end process that the Health & Safety team used and improve on it.

 

Previously, the process for the initial Audit went something like this:

image.png

These audit tasks are then actioned by staff and updates emailed back to the administrator who then updated the global spreadsheet

 

There are some obvious challenges with this process:

 

  • Paper-driven - excessive use of paper, with some audits being printed multiple times erroneously
  • Error within inputs - transcribing hand-written notes in multiple other staff's handwriting resulted in errors or missing data.
  • Manual Sorting and sending - time-heavy and admin-heavy process of finding each task that was assigned to that user
  • High overhead - every two weeks the administrator would go through all of the tasks and email out individually the list of tasks to each of the task assignees.
  • Reporting manual exercise - once a month for 5 entire days the whole Health & Safety team would be working on reporting to show to seniors at what stage each profit centre, manager or assignee was at regarding the tasks progress and overall progress.

 

The Solution

 

Architecture:

image.png

 

 

 

Data

 

Ok, so to start everything off, we look at the data - what data do we need and what data do we foresee the solution using?

 

I created the following SharePoint Lists, as they define what I see as the Bare Minimum data required for the solution to function.

 

Audits - contains the parent Audit object with the name of the site and other details

AuditRefs - An auto-incrementing list to assign ReferenceID fields to the Audits

AuditAppMembers - List of members who have Auditor-level access to the App

AuditTasks - List of tasks generated during the Audit

ActionableTasks - List of Actionable Tasks from the AuditTasks - this keeps the AuditTasks as a static auditable copy.

LastCompletedSection - Used to determine what section of the Audit the person was last on and where to continue from

AuditCompleted - Flag that triggers a Power Automate Flow to create the Actionable Tasks and email the Task Owners

 

 

Creating an Audit (Power Apps)

 

The App starts and loads in the list of Approved Auditors, who are allowed to create new Audits. This defines what buttons and functionality they can see within the App - the AuditAppMembers list is a simple list with a People field and a then Text label for display name - this is also managed from within the App by the other AuditAppMembers.

 

 

When creating a new audit, they can set various pieces of information:

 

image.png

 

 

Then the Auditor starts creating items in AuditTasks, in which each individual task can be assigned to a set of owners:

 

image.png

 

Clicking on the Risk Rating hazard sign, we can see details of how the risk rating affects the Due Date and some suggested guidelines for that particular risk rating:

 

image.png

 

To see what details are involved with Task 1, we can click the Spyglass for it to appear as an overlay (useful for staff to overlay this while within a certain field rather than to keep scrolling up/down every time they need to check the task assessment details):

 

image.png

 

While completing all of the tasks, one can navigate between different tasks, rather than complete the audit end-to-end, this is where writing the taskID to the LastCompletedSection is used, then choosing to continue the Audit will start with the next task after the last completed.

 

Once the audit is completed, the last screen contains a 'complete audit' action, with the tasks being put into a hidden gallery on that screen and the average risk is determined by using the sum of the gallery item's risk levels divided by the total number of items in the gallery, using CountRows() - this is then used to patch the original Audit item in Audits.

 

Upon completing the Audit, a row is written into the AuditCompleted data source, which contains the AuditRef (ID) of that Audit.

 

Post-Audit Task Generation (Power Automate)

 

A Power Automate triggered flow is then monitoring that AuditCompleted list. Upon a new item being created, this reads in all AuditTasks that match the AuditID stored in the AuditCompleted list item, and creates a duplicate of each of these within the ActionableTasks list - this is to ensure there is an auditable set of original tasks and a secondary set of actionable Tasks (this will come back later as a visible comparison within the Power App). This also then generates a table using the Compose action (rather than Create HTML table), of all the tasks and their initial comment as well as if any action is required and who was assigned to them, which it then sends as an email to all Auditees of that Audit and the Auditor:

 

image.png

 

Previously during the App we also set up a Due Date for each task based on its risk rating - a Power Automate recurring flow that runs daily looks at every ActionableTask item that is still listed as 'open' and checks if its due date is upcoming or whether it is overdue, updates the task item and sends out an automated reminder depending on the following logic:

 

Risk Level

Due Date

Initial Email

Reminder 1
(5 Days from Due Date)

Reminder 2
(Due Date)

Reminder 3+
(5 days Overdue)
Manager CC'd

Weekly emails with Manager CC'd after:

Low

Today
+ 30 days

0 days

25 days

30 Days

35 days

35 days

Medium

Today
+ 30 days

0 days

25 days

30 Days

35 days

35 days

Medium to High

Today
+ 20 days

0 days

15 days

20 days

25 days

25 days

High

Today
+ 7 days

0 days

2 days

7 days

12 days

12 days

Very High

Today
+ 3 days

0 days

n/a

3 days

3 days

8 days

 

 

Viewing and Editing Assigned Tasks (Power Apps)

 

When the link is followed, Auditees are then able to see a list of items they have assigned to them that are still outstanding (completed items are hidden), but they cannot see the overall list of items assigned to others (controlled using the AuditAppMembers List):

 

image.png

 

They can then view the details and update the task (only a select few fields are able to be edited):

 

image.png

 

 

 

Auditees can also add an attachment as proof or confirmation that the task has been completed (this could be a photo, or a signed document or etc.)

 

 

Auditors have the ability to view all Open and Closed tasks from within the App - this can be used to re-open, re-assign, make additional comments or extend a Task:

 

image.png

 

image.png

 

 

Task Edit notifications (Power Automate)

 

A Power Automate triggered flow is monitoring the ActionableTasks list, and sends out an email to the auditor and the task owner (auditee) whenever a task has been edited, updated or completed:

 

image.png

 

 

Quick-look Actionable Tasks (Power Apps)

 

An Audit cannot be viewed by anyone other than those who are Auditors or those who are assigned tasks within it. I wanted a way for Auditors to be able to look at an Audit and immediately see which tasks are outstanding and whether the task owner had uploaded any supporting pictures or documentation, so in the detail view for the Audit we have two rows, one that shows all of the Audit Tasks and another that shows all of the Actionable Tasks.

 

Task items are coloured by their risk rating. Any completed items are shown in Blue, and any actions without a required action are Grey. Tasks with attachments will have an icon to indicate. Clicking any of the items allows you to view details and edit the item:

 

image.png

 

Reporting (Power BI)

 

Previously reporting of this was done during the first 5 days of every month and involved the whole Health & Safety Team. By using the data created by Auditors within Power Apps and the subsequent edits and automation, we have been able to reduce that process to roughly 5 minutes of clicking filters, exporting to a pdf and then emailing that export to the relevant business manager that was filtered down to.

 

These reports were designed to be exported, rather than navigated purely through Power BI, this was to enable staff without licenses to also benefit from the data as well as for any external exports that may be required.

 

One example of this kind of report is divided by business manager, so we only need to click the business manager's name on the slicer and the entire report filters itself by that (to do this easily, have copies of synchronised slicers on each of your subsequent pages, then hide them):

 

Overall View between two dates:

 

image.png

 

View once filtered by business manager:

 

image.png

 

Subsequent report pages not included here but are also filtered by that same business manager.

 

 

Better Together! (Value gained)

 

We were able to save 6000+ sheets of paper per year on no longer having to print audits, and over 3300 hours of administrative time that was previously for manually reconciling these sheets with written notes to digital format (Excel).

 

The biggest change was with the reporting, as this had previously been a 5-day team-wide effort (think of the costs in salary!), now those 5 days can be spent on more effectively adding value to the business rather than laborious administrative work!

 

If I had just used Power Apps, I would only have reduced the paper usage, but there'd still be thousands of hours of administrative overhead for parts that I automated with Power Automate, and if I had left out the Power BI or done that on its own then I would have lost out on having a whole solution that covered all elements of the data.

 

There's a massive power available to us all when we use the products as individual instruments to create a symphony of solutions!

 

 

 

I hope this has shown you amazing Power available to you even using just the 3 core elements of the Power Platform

Imagine the value you could gain if you additionally utilised all of the newer members of the Power Platform Family!

 

And don't forget:

 

 

There is always a better way!

 

 

Thanks,

Sancho


@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


1 REPLY 1
Highlighted
Helper I
Helper I

Re: From 5 Days to 5 Minutes

Great solution and a very detailed post, thanks!

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

Top Solution Authors
Users online (6,976)