Substance Provincial drug list management tool
Management
of a list of
more than
15,000 drugs
The Association québécoise des pharmaciens propriétaires (AQPP) had been using an application to manage the official list of medications used by all of the pharmacists in the province. However, their team had a monumental daily task—manually updating the list! The application was outdated and needed to be redesigned. As such, our assignment was to completely redesign the system to prioritize automation and minimize the amount of human input and resulting errors. We also needed to simplify the maintenance of the future application to make sure it would last.
The cloud application we designed and named Substance is a web platform that stores several sources of information on medications in a single central database. The data consolidation that had previously been done manually was now automated, leading to a decrease in human error and significant time savings. In addition, we redesigned the structure of the database and the application interface in order to simplify the procedures followed by AQPP employees.
Rethinking the processes
In order to completely re-engineer the existing processes, we first engaged in a lengthy discussion with the individual most suited to describe the problems with the previous application—the AQPP technician whose role included updating the database. While discussing her daily reality, we identified several irritants and potential areas for improvement for the new version of the application. The discussion made it possible for us to target specific areas that could result in errors being made or a significant amount of time being wasted. That’s when the idea for our solution began to take shape.
Processing a large quantity of data
Multiple Data Sources
The Substance database brings together information from multiple sources, including more than 160 pharmaceutical companies and distributors, as well as the Régie de l’assurance maladie du Québec (RAMQ). Every week, these various sources send the AQPP changes to be made to the consolidated list of more than 15,000 medications. The process is complex due to the large quantity of data to be processed and the fact that each source submits data in a different format. Our solution therefore had to be able to analyze and understand the specific characteristics of each data source.
Assisted Import
With a single click, the person managing the database could now import new lists of medications into Substance. It would no longer be necessary to manually enter the information for each medication one by one. Dragging and dropping each list of medications received triggers the application to analyze the data and automatically generate a list of changes to be made to the medications. With its data analysis system, Substance can handle lists in Excel, text or PDF format, no matter how the data is presented in each file.
Error Detection
To make mistakes is human. Since the previous database was manually managed by a single person, it contained several errors. Thanks to Substance, potential errors are now spotted right away and data is automatically validated through a set of business rules and comparisons with third-party data sources such as Health Canada. Substance also includes a decision support system that uses tooltips and colour coding to help the user identify potential errors that are ranked according to severity. The user can then focus on correcting the identified inconsistencies.
Modernizing the user experience
One of our objectives was to update and improve the interfaces of the previous software from 2008. However, we wanted to keep the functional aspects of the interface to avoid throwing off the users too much. The design of the new platform revolves around the needs of its main users—AQPP technicians. After analysing their work methods, we simplified and restructured the application so that it displays all useful information on a single screen. This prevents the user from constantly having to scroll down the page to view certain information and saves time by reducing the number of clicks and actions required to perform various tasks.
Introducing cloud computing architecture
Once Substance had been introduced, the Windows system installed on each physical computer at AQPP was no longer needed. Substance is installed on an Amazon Web Services (AWS) architecture, meaning that users can now connect to the application from any computer or operating system. Decentralizing in this way has also resulted in lower material and maintenance costs.