An Inventory, the Hard Way: Launch Reason

Motivation can be a powerful force.  Tasks that need to be done in order to perpetuate a system are often begrudgingly done.  Tasks that should be done to improve the system rarely make it beyond the statement, “Wouldn’t it be great if…”

There existed an abundance of weekly work with the inventory that was repetitive, laborious, and obligate for the continued operation of the lab. Much of the tasks involved sifting through the inventory for samples and parsing out the garbled entries that would occasionally clutter the final report. These sorting, searching, and reporting tasks were of the brand that makes work an undesirable experience.  These are also the sort of tasks that lend themselves to WHERE, GROUP BY, HAVING, and ORDER BY clauses.

Performing inventory manipulations in a spreadsheet can be especially chaffing when juxtaposed with the knowledge or imagination of any more effective system. No great technical skill or understanding is required to envision any number of better methods of storing and working with the inventory data. The major problem was the gulf of time and effort separating imagination and implementation.

An Inventory, the Hard Way: Potential Problem

The cumbersome flat file inventory management system that existed during and prior to the mid 2000′s weathered a number of attempts at whole sale upgrade. This isn’t to say that improvements were not made; herculean effort was exerted in improving the fidelity and coverage of the system. However, the inventory was recalcitrant to being migrated away from a flat file single read-write access model.

The magnitude of the precarious nature of the spreadsheet-based inventory seemed to be unrealized by most of the staff. This issue was known, but the potential size of the problem was poorly understood. Since the system was apparently operational, the specter of massive data corruption was largely unacknowledged. The possibility that a widespread yet subtle error went undetected for an extended period of time was not in the top worries of the personnel.  All of the people working with the system were attentive, smart, and responsive to problems. Despite the best people and intentions, a few relatively minor data corruptions, that were identified and corrected, foreshadowed the possibility of a catastrophic failure.

Reluctance to move away from using an Excel spreadsheet is understandable.  It is entirely managerial for small projects and seems scalable because it appears very simple.  However, as a project grows in size the logic and complexity required to maintain it falls to the people running the project.  Excel is a good spreadsheet, but it is not a database and has little ability to scale up.