Excel for the UK’s COVID-19 Track & Trace database?!!

For the last 27 years I have been working with computers (since the middle of my degree) on databases, and systems. Over the last 15 years on the subject of databases and systems here at IceStar Media we have had one clear message:

Once your business and data gets to a minimum size you need to migrate from spreadsheets to a full database.

The benefits a database system will offer you are numerous. So when I see things like this: https://www.bbc.co.uk/news/technology-54423988 with the headline: “Excel: Why using Microsoft’s tool caused Covid-19 results to be lost”, not only does the rage start to set in, but also a large dose of despair .

It leads me to question the qualifications and common sense of all the people involved.

A single use database like this is remarkably simple to set up, given that its coming in from CSV files, you are going to have a fixed set of (known) fields. Thus an importer into a rapid development tool would be simple. The only real unknown would be the numbers of cases coming in and the numbers of operators required to deal with those cases.

Given they have chosen XLS (not even XLSX) we can only assume that someone has reversioned a CSV importer (most likely written in VB) that was probably written a decade or two ago. There are multiple modern ways of dealing with this (even using Excel) – Microsoft Flow/Google Sheets will all help you process CSV files and dump them into Office 365 spreadsheets (if you must go that way).

However even off the shelf DB tools will deal with this, SuiteCRM for example can quickly and easily setup custom modules (people/contacts are already built in). I do not know the exact details of the data they are using, but given the nature of this we can make some assumptions, track and trace is all about contacting people who have the Covid-19 virus and finding people they have been in contact with and then contacting those people.

This quite nicely fits into a Leads/Contacts/More Leads workflow (that a CRM system deals with already). A good CRM system will also remind you or highlight those contacts that have been missed, it will be able to auto assign operators (on a round robin basis or other) and even have automated tasks based on certain replies (meaning people will not be missed).

Given the simple nature of this it would take about a week to set-up SuiteCRM for this (including an importer), then plenty of time for testing, you use proper cloud based hosting with Load Balancing facilities to ensure its expandable.

Of course to go even further you have to ask why they are using CSV files in the first place. This implies that the testing labs are bundling up large numbers of results and then sending them over (probably at set intervals ie daily), this can’t be good for the Track and Trace process which has to rely on speed, these lines of data should be being sent and processed as each one is completed preferably via an API, however if this isn’t feasible again there are a multitude of options.

One of the most simple would be a virtual email (no inboxes), email is sent to an address with either data in the body or attached as a csv file, its converted to an api call to the DB system that interprets the cal and extracts the CSV. This is no more complex than the CSV importer and the data row is in the System immediately the lab has finished with it (which also spreads the import load on the server).

The bundles of data could then be used on a daily basis to cross check and ensure that all data has been caught giving you redundancy.

So, if all this sounds familiar to your business and you need a bespoke system (or you are the UK Government!) please do give us a call and we will happily provide you with a no-obligation Zoom or phone consultation on 01438 894 776 or email us at [email protected].