Improving Lab Systems: From Paper to Spreadsheets to LIMS

Download PDF

Upon completing my undergraduate degree, I worked in an analytical research and development R&D laboratory. Its role was to conduct the routine testing of polymers, devise methods of analysis, provide support to production-based quality control labs, support pilot plant R&D, and carry out special projects. Those special projects could range from analyzing competitive materials to analyzing unusual samples brought in by sales reps or found in the production process. The work was divided between traditional service lab operations and research.

This article will focus on the work’s service lab portion and how different support systems can be used. Those systems include paper-based procedures, spreadsheets, and laboratory information management systems (LIMS). Some labs may begin with paper and progress through spreadsheet implementation and then to LIMS, while others may start off directly with LIMS systems. We will first describe a service lab’s operational needs and then look at the effectiveness of each of the three implementation methods.

Service lab operations

The operational characteristics of service labs revolve around samples and testing. Of primary focus is workflow management within the service lab. What work is coming in? What has to be done? What are the priorities? What results have to be approved and released? The primary functions that have to be addressed in sample processing described below:

• Sample login – Samples are brought into the lab’s receiving area along with a sample request sheet. The request sheet contains the submitter’s contact information, sample description, level of priority, any special issues or concerns, a list of tests requested, and room for analyst comments about the work. (Note, however if there are multiple tests, they may not all be performed by the same analyst.) Depending on how the lab is organized, there may be space to note the sample storage location, as well as any special handling, such as with freezers.
• Sample scheduling – A laboratory analyst will need to find out what samples must be processed by looking at the log book entries made when the samples were logged in or by reading the sample request sheets. An analyst will rarely perform one test on one sample. Usually, they are looking for groups of samples that need the same work.
• Sample testing – The analyst may contact the sample submitter if any problems occur, if anything unusual happens, or if additional testing is recommended. When completed, identifying any reference charts produced are noted on the sample request sheet so that questions about the analysis can be answered.
• Results sign-off – The analyst’s work would be recorded and witnessed with dated sign-offs, a common requirement in most lab operations for regulatory and legal purposes.
• Results review and management – When the analysis is done, the results are reviewed, and those results are sent to the submitter if approved. If they are not approved, the issue must be addressed and resolved, which may be as simple as repeating the analysis with a written record of the problem and its resolution.
• Sample disposition – The remaining sample (if any) is disposed of according to designated procedures or returned to the submitter.

In addition, there are management functions that have to be met. Those may consist of weekly or monthly reports, reporting and resolving any issues that may arise, inventory management, equipment maintenance records management, and materials and equipment ordering.

While most lab operations are more complex than described above, that list of primary functions gives us enough substance to compare paper-based systems, spreadsheets, and LIMS. That comparison will be through three elements: workflow, data collection, and regulatory requirements.

Paper-based systems

This is the starting point for most laboratories until they realize that paper-based systems need to be improved and they have enough experience to look for more robust solutions like a LIMS. Given the workflow described above, a paper-based system would consist of the sample logbook, binders containing the sample request sheets (in large facilities, there could be multiple binders depending on the nature of the work), and a paper notebook for each analyst.

The workflow in a paper-based lab can be described as controlled chaos. The pending work resources consist of the logbook, sample sheets, and the samples themselves, all of which may be in the books and storage location or in possession of another analyst working on some aspects of the test request. If you plan to work on that material, you have to chase down the resources. The same paper chase occurs when results are documented and reviewed for potential release.

Data collection is another issue. Entries must be made in notebooks by writing them (an item verified by another analyst as covered in regulatory guidance) or by pasting/taping a report from an instrument into the notebook. References to charts or printouts that are too large to put in a notebook have to be made so that they can be located for review if questioned.

Meeting regulatory requirements is another concern. The guidelines do not require computerized systems, but they make it much more manageable. The biggest issue is that a lot of paper must be kept organized in many places. Regulators look for timely responses to requests for information, and that may be a problem. The information they want may be in an older notebook stored in a central archive or in someone’s files requiring a physical search. Data loss is a significant issue, particularly if stored material (e.g., notebooks, instrument printouts, etc.) isn’t protected.

While vendors may not be directly involved in your planning, the complexity of today’s technologies make them a supporting stakeholder for both advising on new products/technologies and technical support. As a result, a serious effort should be made to establish good working relationships with key vendors, particularly those with LIMS, ELN, and other informatics products used in the lab. The lab should provide the vendors with input on their future needs while expecting the vendor to keep them abreast of their development efforts; this may require NDAs for key technologies.

One critical aspect to the above is ensuring that those working in the lab have the appropriate education in the science and technologies used and anticipated for use in lab work. Part of management’s goals should be programs for formal and informal education of all lab personnel.

Another issue is pointed out in a recent (2022) American Pharmaceutical Review article [1] which asks: “Why are the top 10 FDA Form 483 [2] issues mostly the same for the past twenty-three years? What is/are the cause(s)?” Among the answers, one finds that: “many small firms are still paper-based and do not have the necessary money and technology to analyze process and analytical data in such a way that allows for proactive responses to trends.” While the article is specific to FDA requirements, the same issues can likely be found in labs across the wide spectrum of industries. (However, note that the process analysis referenced in the article is a normal part of implementing informatics systems.)

One important aspect of regulatory requirements is audit trails for results and the data that goes into their development. An audit trail represents the history of each data/result element. If a result needs to be corrected, the original entry has a line drawn through it so that it is still readable; whoever corrected it initials the change with a date and the reason for the change. Keeping track of audit trails in paper-based systems can become cumbersome if those changes require additional instrument printouts.

The biggest issue with paper-based systems is that everything is physical and subject to damage and loss. Backup copies of materials can be created, but that is more paper that has to be managed, and labs have limits to how much paper is kept within the lab. The excess – which can’t be discarded – has to be stored in a way that it can be accessed if needed. This is a security issue for your lab and its data/information.

Management report generation – in fact, all management activities – are manual. For example, sorting through papers and doing performance summaries can be a time-consuming set of tasks.

Spreadsheet-based systems

There are two key elements to service lab operations: samples and testing. It’s easy to imagine those as a set of axes on a table with test results filling in the blanks. That concept has enticed some to look at spreadsheet software, commonly available as part of office software suites used in lab work, to organize a lab’s operations. In larger operations, different spreadsheets might be used for various customers/submitters or product lines in quality control or some R&D facilities. The software cost is low, with specialized products and add-ons that may make reporting and other tasks more manageable. This might be a starting point for a small lab, particularly if funding is tight and lab personnel doesn’t feel they are up to the perceived challenge of choosing and implementing a LIMS. It can work, but eventually, you’ll outgrow it.

From the workflow standpoint, we’re not in much better shape than paper-based systems since most spreadsheet programs offer single-user access to a sheet, unless you divide the lab’s work by some criteria across multiple files. Each spreadsheet may have to be layered with multiple sheets per file if you are going to keep track of sample locations. All data entry will be manual and must be verified by a second analyst. Audit trails may be easier to implement if multiple layers of spreadsheets are used. One significant benefit of spreadsheets is that they are electronic systems that are easy to back up and avoid data loss if proper procedures are put in place. However, there is still the issue of managing and referencing any data and information that comes from instruments, as well as providing backup for printed reports. Some systems may offer the ability to output reports to .pdf or .csv files which can be electronically backed up.

Instrument data collection requires manual data entry and verification by a second analyst. There should be a link to any charts generated by instruments so that results can be traced back to the source data.

Regulated labs must be careful of how spreadsheets are developed and used. There are guidelines for developing spreadsheets used in food and drug administration (FDA) applications [3] that may be applied to other regulatory agencies. The first is titled “Spreadsheet Design and Validation for the Multi-

User Application for the Chemistry Laboratory” and contains a list of common errors in developing multi-user spreadsheets (these were found in one lab, but it’s easy to see how they might be repeated elsewhere). The following quotes are taken from the paper:

• “Nearly all of the spreadsheets in the laboratory showed rounding-off errors, and none used the Excel rounding function when needed to avoid rounding-off discrepancies between the original and check calculations by the second analyst.”
• “Formulas in the analytical procedures did not follow the Excel equations.”
• “Conversion factors were not expressed in the analytical procedures.”
• “The formulas used by the check analyst for manual calculations were not the same as those used in the Excel spreadsheets.”
• “The pre-determined specifications or limits were not shown in the spreadsheets.
• “Spreadsheets were not clearly documented. For example, the product declaration was not indicated, replaced by only a number in a cell.”
• “Units were expressed as numbers without descriptive labels such as mg/mL or mg/g.”
• “Sample weights were described as sample volume and areas as ratios.”
• “Spreadsheets did not contain provisions for security and integrity of data.”
• “Spreadsheet applications were not protected from changes, meaning analysts could freely change labels and formulas.”
• “Regression analysis was calculated with the y and x axis inverted in the Excel formula, which generated erroneous slope and intercept results.”

Validation of spreadsheets is also difficult, particularly in a multi-user environment where people can make changes to spreadsheets without documentation.

The use of add-on software can make management reports production easier for weekly/monthly summaries as well as performance metrics. Security is another issue that warrants attention. The systems you use need userID/password controls for the active data/information as well as the backup systems. Policies need to be put in place for access controls both local to the laboratory and remotely if off-site access is permitted.

At best, spreadsheets are a stop-gap measure for a small lab that will eventually be replaced by a LIMS. Considering the costs of implementation, the risks of issues in regulated environments, the efficiencies gained by electronic data capture in LIMS, and the need for data migration to the LIMS when implemented, the benefits are marginal.

Laboratory information management systems

The issues noted in the preceding paragraphs are the reason that the LIMS was developed. Both paper-based systems and spreadsheets are labor-intensive to use, and they often require people to wait for access to the information they are looking for. LIMS provides simultaneous access to multiple users, makes it easy to search, and allows data entry to be either manual or electronic, allowing instrument data systems to enter results without human effort.

Workflow is simplified. For example, it is easy to generate a worklist of outstanding samples that require a particular test procedure including priorities and sample locations. Audit trails are built in. Standardized reports come with the system and specialized versions are easily created. Samples that are out-of-range for a particular type of sample or test can be flagged as data is entered instead of manually comparing results against specifications. Tests that have to be reviewed can be called up in a single query, approved, or flagged for further work, and if a sample’s work is closed out, a report can be sent to the submitter electronically. There are facilities built-in that permit submitters to log in test requests further reducing the amount of work the analyst or administrator has to do.

Instrument data collection can be electronic, directly connected with an instrument data system (IDS) or under program control for less sophisticated devices. For example, the LIMS can send a worklist of samples to the IDS and when completed, the IDS can send the results back to the LIMS for automatic entry into the sample record.

Finally, with a LIMS, regulatory requirements are more easily met. Validation is required and LIMS vendors often have test suites to help qualify installation and other facets of the installation/validation process. However, the users are ultimately responsible for preparing user requirements documents and other documents used in the validation process. Vendors as well as other organizations can provide guidance in their creation.

In closing…

Paper-based systems and spreadsheets are standard technologies that are used to support a service lab’s operational behavior. To use them effectively, management policies must be set up to ensure that people know how the lab works and how these tools are being used; the assumption is that everyone knows how to use paper notebooks and spreadsheets. However, there are serious pitfalls in their use and those have been detailed. LIMS is a software system specifically designed and purpose-built to support service lab operations and help it meet corporate and external regulatory requirements.

The following table (Table 1) compares some of the service lab workflow elements for paper-based, spreadsheet, and LIMS support systems.

Table 1 – Comparison of service lab support systems

Another set of factors that needs to be looked at is the cost of implementation and operational use. Those are compared in Table 2.

Table 2 – Comparison of service lab support systems, costs and risks

References

^[1]Lynn, S.J., “A Discourse on Pharmaceutical cGMP FDA Form 483 Trends: Why are We Re-Living the Same Issues Over the Last 23 Years?”, American Pharmaceutical Review, Volume 24 Issue 4, May/June 2022, pgs 72-78

^[2] Form FDA 483, “Inspectional Observations,” is a form used by the FDA to document and communicate concerns discovered during these inspections.

^[3] See http://www.spreadsheetvalidation.com/wp-content/uploads/2020/04/LIB-Design-Multi-User2A-PDF.pdf, and, https://www.spreadsheetvalidation.com/wp-content/uploads/2020/04/LIB-Part-II-Single-User-6-17-05.pdf