Off-the-Shelf System Tackles Monitoring, Reporting Needs

By Pat Ehrman

A facility operations management company stood to inherit extensive water and wastewater facilities and an archaic laboratory information management system (LIMS). Like many treatment facilities throughout the country, they needed a cost- and manpower-efficient data collection and management system. They chose inexpensive off-the-shelf hardware and software, and tied it all together with a simple Excel interface.

The Land Between The Lakes National Recreation Area (LBL) is the nation’s largest inland peninsula, boasting more shoreline than Lake Superior. The area hosts over two million visitors annually.

In 1999, responsibility for LBL was transferred from the Tennessee Valley Authority to the Department of Agriculture, to be managed as a unit of the USDA’s Forest Service. LBL’s Advisory Board solicited competitive bids for maintenance and engineering services, and ultimately accepted Ecotone Services Inc.’s fixed-price bid.

Ecotone’s contract includes monitoring the area’s 26 drinking water wells and four permitted wastewater treatment sites. Daily drinking water well and tap inspections include field tests for total and free chlorine, pH, gallons pumped, temperature, caustic and chlorine added, etc.

Daily wastewater facility inspections are performed at each influent and effluent, aeration basin, weir, clarifier, contact tank, etc. Tests performed or data captured include caustic and chlorine added, total chlorine, dissolved oxygen, flow, pH, temperature, sludge levels, etc. Samples are also collected and sent to outside labs for BOD, suspended solids, ammonia, fecal coliform, etc.

Because LBL spans a state boundary, Ecotone must report to both Kentucky and Tennessee, and to the Forest Service.

Information Management

Mark Mitchell, Safety Manager at Ecotone, said the former contractor’s DOS-based field units worked ”but there was no way to upgrade them.” Downloading data to the old LIMS was tricky, and “much of its infrastructure had been lost over time. We could put data in and print daily reports, but the system could no longer compile data.”

Technical support had lapsed, and the original software media was lost. And a lightening strike had disabled the LIMS’ computer.

In searching for a new system, Ecotone wanted control over an uncomplicated system within their budget.

They began by researching LIMS options, including $15,000-$20,000 to upgrade the existing LIMS; purchasing a high end LIMS and altering their operations to fit it; or ordering a custom LIMS. Ecotone was leery of investing their limited resources yet ending up in the same dilemma they now faced.

To keep the system simple, flexible, and maintainable, only off-the-shelf hardware and software components were used.

Ecotone chose MSC-LIMS, an Access-based LIMS developed by Mountain States Consulting, LLC (MSC), because of its integrated Excel interface and the level of technical support available. MSC-LIMS’ annual subscription license was selected for its affordability.

The system uses Excel as the middleware between lab or field instruments and MSC-LIMS. Excel’s track record implies reliability and its tab-style layout is intuitive. Ecotone is guaranteed a “no-programmer-required” method for making changes, by simply modifying Excel templates, according to MSC’s Rick Collard.

Ecotone purchased a new Dell desktop PC for the office. There the LIMS would be used to log samples, import and maintain all field and laboratory data, and automate production of monthly regulatory reports.

Data Collection

For field data collection, personal digital assistants (PDAs) would have sufficed, but Ecotone preferred the full-sized keyboards of hand held PCs (HPCs). They purchased three NEC Solution’s MobilePro 900s - each with an Intel PXA255 XScale processor, Microsoft Handheld PC 2000 operating system and application suite (including “pocket” versions of Word, Excel, Access, PowerPoint and Internet Explorer). The HPCs have 8" screens and 78-key QWERTY keyboards with stylus, yet the closed unit measures a compact 10" x 5" and weighs less than two pounds.

Data entry screens on the HPCs are Excel workbooks created by MSC. The workbooks are necessarily simple, because Pocket Excel does not support advanced features such as Visual Basic for Applications (VBA) macros or data validation (validation occurs later, upon import into the LIMS).

The HPCs’ Windows desktop presents two shortcut icons: “Wastewater Locations” and “Water Locations”. Technicians use the shortcuts to create a new workbook from the Excel template, then log on with their initials and the current date. The new workbook is saved with a new file name that includes their initials and the current date.

Each workbook consists of a series of tab-style worksheets, one worksheet for each sampling location. The current Time is added with a keystroke and comments are entered manually. The technician collects the sample, or conducts the field test and records the analysis result by entering the appropriate value in the corresponding cell. Results are saved periodically or when prompted upon exiting Excel.

Downloading Data

At the end of the day, technicians download their data at the LIMS workstation by connecting their HPC with the available USB cable. Microsoft’s ActiveSynch software automatically copies all new field data from the HPC to the workstation.

The workstation’s Windows desktop contains “Import Water Locations” and “Import Wastewater Locations” shortcut icons, which open new workbooks from Excel templates. These templates contain the user interface and all of the VBA code written by MSC to import the data from the field data workbooks. The technician uses the [Import Field Data…] button to select the day’s water or wastewater workbook.

The technician then logs onto MSC-LIMS, and there creates a new sample batch from the day’s samples. A Spreadsheet Import feature retrieves and validates the field data from the open Excel workbook.

When samples have been collected and sent to outside labs for testing, the required analyses are automatically added to the LIMS samples for later manual results entry. A technician can locate any such pending analyses by querying the analyte, collected date, sampler, or other query combination.

Regular backup procedures insure that the LIMS data is safe and secure - even from lightening.

Reporting

Once the samples are logged and results imported into MSC-LIMS, the technician can retrieve and report results using any of a number of selective query criteria.

Tennessee and Kentucky have different reporting requirements for Monthly Operating Reports (MORs) and Discharge Monitoring Reports (DMRs), but the new system is extremely flexible, Mitchell said. LBL’s existing report formats were converted to Excel templates. Ecotone queries the subject data in MSC-LIMS, then exports the data to the Excel report.

Mitchell, a self-styled student of Microsoft, is comfortable with Access and Excel.

“It’s easy to understand,” he said. “Rick (Collard) is the master of Excel and the inner workings. He’s shown me how to do some things, so I do what I’m comfortable with.”

Mitchell revises the Excel report templates as necessary.

Using MSC-LIMS, Ecotone can also respond quickly to the Forest Service’s occasional requests for information. Recently, they submitted a unique report on the amount of caustic added at certain well locations over a six-month period. Another query arose from a water well situation, where a year’s turbidity data was compiled and reported in tabular and line graph form, as requested.

About the Author:
Pat Ehrman of Mountain States Consulting, LLC, Jackson, Wyoming, USA, can be reached at 307-733-1442 or [email protected]. For more information on MSC-LIMS, visit www.msc-lims.com. For more information on Ecotone Services, Dover, TN, visit www.ecotoneservices.com, or email Mark Mitchell at [email protected].