MSC-LIMS.com Logo

Your source for an affordable, flexible, and proven
laboratory information management system (LIMS).

Home / Articles / Articles and Case Studies /
Off-the-Shelf System Tackles Monitoring, Reporting Needs

This article, edited for length, originally appeared in the March 2005 issue of Water World magazine.

Off-the-Shelf System Tackles Monitoring, Reporting Needs

A facility operations management company selected inexpensive off-the-shelf hardware and software, and tied it all together with a simple Excel interface.

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. How could they streamline the facility's data collection strategies, compile the documentation required by multiple regulatory agencies, preserve historic data and assemble current data - all without breaking their backs OR their budget?

Answer: They selected inexpensive off-the-shelf hardware and software, and tied it all together with a simple Excel interface.

Background

The Land Between The Lakes National Recreation Area (LBL) is the largest inland peninsula in the United States. The region has more shoreline than Lake Superior and is the second largest contiguous block of forested public land east of the Mississippi. In 1963, President Kennedy hoped to stimulate economic growth in the region by creating a National Recreation Area. The effort was a great success: LBL and the surrounding lakes are now the focal point of a $600 million regional tourism industry.

The area now hosts over two million visitors per year - many of whom camp at one of four developed campgrounds, enjoy backcountry campsites, hiking, biking, all-terrain and off-road vehicle and equestrian trails, boat launches, wildlife viewing opportunities, historic sites, interpretive centers, a planetarium and other visitor services.

In 1999, responsibility for LBL was transferred from the Tennessee Valley Authority to the United States Department of Agriculture, to be managed as a unit of the USDA Forest Service. LBL's Advisory Board requested competitive bids for all of the area's maintenance and engineering services, and finally awarded the contract to Ecotone Services, Inc., of Dover, Tennessee, based on their fixed-price bid.

Ecotone's contract consists, in part, of monitoring water and wastewater facilities throughout the area. Field monitoring is conducted daily at each of the 26 drinking water wells and four permitted wastewater treatment sites. Drinking water well and tap inspections include field tests for total and free chlorine, pH, gallons pumped, temperature, caustic and chlorine added, etc.

Wastewater site inspections are performed daily at each facility at influent and effluent, aeration basins, wiers, clarifiers, contact tanks and so forth. Tests performed or data captured at each point 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 additional tests, including BOD, suspended solids, ammonia, fecal coliform, etc.

Because LBL spans the state boundary between Kentucky and Tennessee, Ecotone is obligated to provide regulatory reports to both states, as well as periodic monitoring reports to the Forest Service.

The old data collection system was outdated and unreliable

As the new contractor, Ecotone had wisely hired the best of the previous contractor's staff. And with over 170,000 acres and 420 miles of winding roads to cover, their field crew needs to be efficient. They must accomplish their daily monitoring and testing duties, keep up with maintenance procedures and be responsive to emergencies.

LBL's previous maintenance contractor had established a system which once sufficed but was now outdated. The old hand-held units used to collect field data were DOS-based machines, rigidly programmed. The user selected a test location by keying in its code, and then keyed in a code number representing the appropriate analyte. Mark Mitchell, Safety Manager at Ecotone, explained that the old units were very basic. "They worked and were industrial and did what they needed to do, but there was no way to upgrade them in any way." The system came with instructions, but required an outside programmer to update or revise.

The next step, downloading data to the old LIMS, was "tricky" according to Mark. It required strict protocols for completing samples because each download overwrote its predecessor. Even so, it was possible to create duplicate entries.

Meanwhile, back at headquarters, Ecotone assessed the old LIMS itself. Although the program had been used successfully in the past, "much of the infrastructure to the program had been lost over time, " said Mark. "Many of the templates and reports were no longer available. We could put data in and print daily reports, but we could not compile reports other than manually. The system had lost its ability to do any compiling of data." The old LIMS system's technical support agreement had long since lapsed, and the original software media was nowhere to be found. To complicate matters, a lightening strike had disabled the sole computer that housed the LIMS.

Ecotone foresaw an operation with a hamstrung field crew and burdensome data management system. But the budget simply did not accommodate an exorbitant software and hardware upgrade. They needed an affordable plan, and fast.

The search for a solution

Ecotone wanted to get and retain control over an uncomplicated system, while keeping within their strict budget.

They began at the heart of the problem by researching their LIMS options, which included: investing $15,000-$20,000 to upgrade their existing LIMS; purchasing an off-the-shelf high end LIMS and altering their operations to fit its requirements; or ordering a custom LIMS. Ecotone was leery of investing limited resources in an expensive or inflexible LIMS, or in a system that would demand unending technical support. These choices could result in the same dilemma they were currently trying to resolve.

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), in large part because of its unique integrated Excel interface. They were also drawn to the level of technical support that would be available. MSC-LIMS' annual subscription license was selected because of its affordability for the full off-the-shelf system.

MSC's founder, Rick Collard, demonstrated to Ecotone that MSC-LIMS' Excel interface is a straightforward method for tying lab and field instruments to the LIMS. It also guaranteed Ecotone a no-programmer-required method for making changes, by simply modifying the Excel templates.

Ecotone purchased a new Dell desktop PC, on which MSC-LIMS was installed. The LIMS would maintain all field and laboratory data, and automate the production of monthly regulatory reports. MSC-LIMS would also be used to log samples, and its Excel interface would be used to import the collected field data.

Ecotone then considered options for field data collection. Personal digital assistants (PDAs) would have sufficed; instead, they opted for three new hand held PCs (HPCs), preferring the HPC's full-sized keyboards. They chose NEC Solution's MobilePro 900, each complete 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. Even so, the closed unit measures a compact 10" x 5" and weighs less than two pounds.

See Figure 1, above, NEC Solution's MobilePro 900 (photo courtesy of NEC Corporation; unauthorized use not permitted.)

Data entry screens on the HPCs are custom Excel workbooks that MSC designed to Ecotone's specifications. The workbooks are necessarily simple, since 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).

From data collection in the field . . .

The solution is simply elegant: Excel is the middleware between the HPCs and MSC-LIMS. Excel's long track record implies reliability. Its familiar worksheet paradigm is widely used and its tab-style layout is intuitive.

The HPCs are used to collect daily field data for LBL's water and wastewater sites. The HPCs' Windows desktop presents two shortcut icons to workbook templates: "Wastewater Locations" and "Water Locations". Technicians use the shortcuts to create a new workbook from the 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 (see Figure 2, "Sample Water Location Worksheet", below).

Sample Water Location Worksheet

Figure 2, Sample Water Location Worksheet, on the HPC

The sampler's initials and current date automatically appear on each sheet; 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.

. . . to secure LIMS in the office

At the end of the day, technicians download their data at the LIMS workstation by simply connecting their HPC with the available USB cable. Microsoft's ActiveSynch software automatically activates and 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. These shortcuts open new workbooks from Excel templates that 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 (see Figure 3, "MSC-LIMS Batch Login Screen", below). MSC-LIMS' Spreadsheet Import feature retrieves and validates the field data from the open Excel workbook.

Figure 3, MSC-LIMS Batch Login Screen

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 any of several query combinations.

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

Reporting to State and Federal agencies

Once the samples are logged and results entered into MSC-LIMS, the technician can retrieve and report results using any of a number of selective query criteria. For example, a query of all analyses performed by a specific sampler on a specific day produces a log of tasks accomplished.

Tennessee and Kentucky each have different reporting requirements for Monthly Operating Reports (MORs) and Discharge Monitoring Reports (DMRs); therefore different Excel report templates are used for each. Being able to adapt with the LIMS using different templates for different states has been extremely helpful, said Mark. "Reporting is absolutely easier with MSC-LIMS."

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 - see Figure 4, "Excel report: KY Groundwater MOR", below.

Figure 4, Excel Report: KY Groundwater MOR

Such Excel reports can be configured or revised by Ecotone as necessary. Mark, a self-styled student of Microsoft, is comfortable with both Access and Excel. "It's easy to understand," he declared. "[MSC's] Rick 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."

Ecotone can also respond quickly to the occasional request for information posed by the Forest Service. Recently, their Forest Service field representative requested a report on the amount of caustic added at certain well locations over a six-month period. Another recent request stemmed from a water well situation, where a year's worth of turbidity data was compiled and reported in tabular and line graph form, as requested.

Results

Ecotone was able to implement a solution that is simple, flexible, maintainable - and cost effective - by using relatively inexpensive off-the shelf software and hardware.

Mark does not miss the old system. "We were glad to get away from it, because of the hoops we had to jump through every time we wanted to do anything." Mark believes Ecotone made the right choice. "We realize that technical support has been very well done, and we appreciate the fact that we can call or email at any time and get a response very quickly. I've heard that some corporate giant LIMS' support is not as quick as it needs to be. We've been extremely pleased with support and turnaround time on different situations we've incurred."

MSC-LIMS is the centerpiece of the solution. It resides on a single workstation at Ecotone's office, and interfaces gracefully with the field crew's new handheld PCs. The software is fully supported and documented, as is LBL's new Interface Solution itself. MSC-LIMS produces the necessary reports required by Kentucky, Tennessee and the Forest Service. Luckily, it also was able to ingest the last gasp of historic data coughed out by the old LIMS, so LBL's trending analyses are thorough and complete.

Mark relates that "the Forest Service is extremely pleased with how Ecotone is doing business", and their performance in general. Ecotone is meeting its obligations for monitoring and maintaining public water and wastewater facilities at LBL. Their field crews are flexible and responsive; regulatory agencies receive the required reports on time; and the visiting taxpayer is assured of safe drinking water and sound environmental practices. And Ecotone accomplished all of this using off-the-shelf software and hardware, and all for under $10,000.


Pat Ehrman of Mountain States Consulting LLC., Jackson, Wyoming, can be reached at 307-733-1442 or info@msc-lims.com.

Join Our Mailing List

Stay informed by signing up for our email Mailing List and receive our MSC-LIMS Insights newsletter and occasional notices with product and company news.