How PowerApps could have avoided the loss of 15k Covid-19 test results

It was recently reported that Public Health England (PHE) had lost over 15,000 Covid-19 test results.

This was caused by a poorly designed solution that was reliant on Microsoft Excel to be used as a database. The Covid-19 test results had been filed using CSV formatted documents.

The issue was then caused when an automated solution was developed to compile all these results into a Microsoft Excel file that was using an outdated file format known as XLS. Instead of the modern file format XLSX.

POWERAPPS EXCEL V2

Due to the outdated file format, each document could only hold 65,000 rows of test results. This oversight meant that multiple files were being automatically merged and files that contained too many rows of test results that exceeded this threshold were not included in the final total.

See the latest updates from PHE on this issue here.

How Microsoft 365 could have avoided the loss of Covid-19 data

There are several Microsoft 365 products that could have improved this overall process and inevitably would have prevented this loss of data.

Excel is not a database

Although it can be argued that an Excel workbook can be used to store data for a PowerApp development process inside of OneDrive. This should only ever be used for very small-time business applications. Such as a productivity application shared between a very small team.

Simpsons v2

Common Data Service

The common data service offered by Microsoft inside of the Power Platform would have been the perfect relationship-based database that could have safely and securely stored the data inputted from a very simplistic PowerApp.

common data service

Covid-19 PowerApp Interface

A very basic Canvas PowerApp could have been put together very quickly to allow data entry into the system from the source of where it was being input. Rather than workers at PHE having to manually type into a “Legacy IT System” that only operated with outdated file formats. They could have used a PowerApp interface like below from a mobile device:

Power Automate

Microsoft 365 has a product that can automate workloads such as merging data and creating output in the form of documents, among others. Power Automate could have been used to automate reports of Covid-19 test results and it has an excellent troubleshooting portal that would have flagged any potential issues with data that was being merged.

Power Automate

Covid-19 Data Loss Summary

This error was caused by PHE using outdated IT practices. PowerApps and the other technologies outlined in this article are all based around “Low Code” solutions. Meaning it does not take a computer programmer to build.

Using PowerApps it would be possible to build an end to end solution within weeks, compared to traditional forms of software development that could take months or even years to develop.

We hope to see not only PHE but all of the public sectors adopt modern cloud based approaches to IT solutions in the future, to mitigate similar issues from reoccurring.

Want more information on PowerApps?

Download your copy of our recent whitepaper “Power Apps Governance Strategy”.

If you would like more information in regards to getting started with PowerApps then please download the whitepaper below.

Power Apps Governance Whitepaper

DOWNLOAD WHITEPAPER

We collect your name and email only to send you a link to download the whitepaper. This information is not shared or sold. We may also contact you for feedback.

Valto also host free web seminars, which on occasion, you will be invited to. You can unsubscribe at any time.

To download the whitepaper please fill in the contact form below and you will be emailed a link to download the file.

    Keep me up to date with your latest news and online events

    Need help with PowerApps?

    I hope you found this information useful, if you have any questions regarding Power Apps, Power Automate, SharePoint or Office 365 please do not hesitate to contact me.

    Author


    Valto
    Dougie Wood
    Dougie@valto.co.uk
    10 Watergate Row
    Chester, CH1 2LD