From 5 Days to 5 Minutes

[iAm_ManCat]

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:

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:

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:

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

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:

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):

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:

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):

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

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:

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:

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:

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:

View once filtered by business manager:

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

[Anonymous]

Great solution and a very detailed post, thanks!