Case Study

A Small Lab Develops A Laboratory Information Management System

By Rick Collard

It seems that regardless of profession, most workers face an endless struggle to keep pace with developing technology. Attempting to harness that technology to improve productivity is an even greater challenge and no small task in today's technology climate. Whether manipulating data with word processors and spreadsheets or employing computer-controlled analytical equipment, the small water/wastewater lab is not immune to such challenges. When properly applied, however, information technology can yield significant benefits to even the smallest laboratories. One small Wyoming lab's experiences with automation serve to illustrate what can be accomplished.

During the summer of 1994, the lab administrator for the City of Cheyenne's Board of Public Utilities contacted Mountain States Consulting of Jackson to discuss his laboratory's data management problems. The administrator and one full-time technician are responsible for all sampling and analysis in the Board's two wastewater treatment plants and two water treatment plants, as well as for the industrial pretreatment program. The lab was struggling with unwieldy paper-based records, increased sample testing load, lost and expired samples, and more frequent and extensive requests for lab data. Without the luxury of additional personnel, a laboratory information management system (LIMS) appeared to offer a viable solution.

The Custom Approach
Three possible approaches existed for implementing a LIMS. The first option was to locate a suitable off-the-shelf commercial software product that would meet the lab's needs. The second solution was to use a commercial product that could be customized for the lab's specific requirements not included in the base product. However, with a LIMS software budget of only $10,000 the Board was unable to locate any commercial LIMS products for either of these options that would fit their budget, so the third path was taken-development of a custom system.

We realized that many of the commercial LIMS products included such high-end features as customer invoicing, automated instrument interfacing, and client/server relational database engines. These features are important in large commercial labs, but are not crucial in a two-person municipal lab. Track record, product support, version updates, and an extensive array of features are a few reasons to prefer a commercial product over a custom-written solution. However, when the cost of a one-size-fits-all vertical market application, such as a LIMS, exceeds the cost of developing a custom solution, the custom approach must be considered.

After reviewing the Board's list of requirements, Mountain States proposed a custom system to be developed with Microsoft AccessR, a relational database management system for Windows. We chose Access because it is widely used, has extensive third-party support, and is accessible to non-programmers. This software also is fully relational, which means the system's underlying database model can be constructed with data integrity rules. From a developer's perspective this means the database engine can enforce integrity rules without writing additional software. From the user's perspective, integrity rules ensure that the organization and structure of the data can not be compromised by operator error. For example, a sample can not be logged if it does not have a project, location, and sample type, or an analysis definition can not be deleted if it is already part of a sample.

Taking Shape
System development began in the fall of 1994. During the initial design phase, Board personnel reviewed our database model and detailed design specifications. Communication is always extremely important during this phase of the project so, to avoid misconceptions, we went through several iterations of design specifications until we reached agreement on all aspects of the system. With a detailed design, development proceeded rapidly and in April 1995 we installed the LIMS on the lab's existing 486 PC and added a bar code laser scanner and label printer.

Cheyenne's LIMS consists of five modules accessed from a notebook tab-style main menu: setup, administration, samples, laboratory notebook, and quality control. The setup and administration modules are used to configure the system's operating parameters and users.

While city staff members currently run the system on a single PC, they did anticipate a future local area network, so the system will accommodate multiple users. Also, the LIMS administrator creates individual user login accounts assigned to one of four security roles: read only, samplers, technicians, and administrators. Members of the read only security role can only log on and view data or run reports. Samplers have the privileges of the read only group and can log in samples. Technicians have sampler privileges and are allowed to enter sample results but can not update results once entered. Members of the administrators role have full privileges throughout the system and are responsible for configuring system lists such as analyses, projects, requirements, sample types, locations, units, container types, preservatives, and contract laboratories. An analysis definition includes the analyte, analysis method and data entry, and warning minimums and maximums. A requirement is a named list of analyses. For example, a "metals" requirement may include analyses for cadmium, copper, zinc, etc. A project includes any number of analyses and/or requirements.

The samples module includes functions for logging a sample, entering results, tracking samples, and viewing sample data with various reports. To log a new sample, the user selects a project, location, sample type, etc. The sample's project automatically determines the required analyses, however, the user is free to add or delete analyses and requirements as necessary. When login is complete the system automatically calculates the number, size, preservative and type of container(s) required for the sample and prints a bar-coded sample label for each container. To enter sample results, technicians simply select the results entry option, pass a bar-coded sample container or worksheet under the laser scanner to retrieve the sample, and enter the appropriate results. Any changes to existing samples, including altering results or deleting analyses, automatically produce an audit trail event.

The LIMS administrator uses the laboratory notebook module to document lab methods and schedule samples. Adding a project to the sample schedule ensures that a sample for the project is logged by a certain date. When the LIMS is started, the system automatically detects and displays both schedule and sample warnings. The laboratory notebook module also is used to print bench sheets, develop chain-of-custody reports, and generate custom bar-coded worksheets to record analysis results. The quality control module is used to log quality control samples and their results, maintain information on samplers, employees, employee training and certification histories, instruments and their calibration and maintenance schedules, and laboratory procedures. Control charts and statistical reports are also generated from this module.

Just Rewards
A few minor problems were detected and fixed, and the system has run flawlessly since its installation. Samples no longer expire, scheduled sampling is no longer missed, samples are now quickly tracked using any combination of criteria, and data is queried and reported with ease. Having contributed to the design and witnessed the evolution of a more efficient and productive lab, the Board considers its LIMS system a complete success. From the consultant's perspective, the design objectives were met, the client is satisfied, and the LIMS is a vital tool in the daily operations of the Cheyenne laboratory.

About the Author: Rick Collard is a software engineer and the founder and owner of Mountain States Consulting, an information systems consulting and software development firm in Jackson, Wyoming. You can contact him at 307-733-1442.

Edited by Ian Lisk