Interface BAX Instrument with LIMS Software

Summary

This article describes the procedures necessary to interface Dupont/Qualicon’s BAX® DNA-based detection instrument (hereinafter “BAX”) with the LIMS software.

The BAX system screens food and environmental samples for pathogens or other organisms, producing either “positive” or “negative” results for up to 96 samples per batch. The interface discussed in this article automates creating the pre-run file and importing BAX results into LIMS, eliminating the need for manual data entry.

More Information

The interface process follows a logical sequence, using Excel worksheets as the middleware between LIMS and the BAX. In essence, your LIMS analyses, sample IDs, and descriptions are exported to an Excel worksheet, which graphically represents the sample wells used by the BAX; the data from the worksheet is then exported to and used by the BAX system’s software. When the run is completed, the BAX results are imported to another Excel worksheet, and from there the results are imported into the Results Entry by Analyte screen in LIMS.

Note that the resulting Excel workbooks need not be saved since they exist only to provide a temporary workspace from which either a BAX file is created or its results are made available to LIMS. At the end of the sequence, your results will exist in both the BAX data file and in LIMS.

Part One of this article describes appropriately configuring LIMS and the two necessary Excel templates.

Part Two describes the procedures necessary to create a BAX pre-run file.

Part Three describes importing analytical results from a BAX data file into LIMS.

Part One: Configuring the LIMS and BAX Interface

First, you must obtain the Excel templates:

LIMS BAX Rack Setup.xlt	An LIMS export template used to create a BAX pre-run file populated with LIMS sample IDs, descriptions and BAX targets in user-specified well locations.
LIMS BAX Data Import.xlt	An LIMS import template used to import results from a BAX data file.

Click on the links to open the templates, or navigate to the File Library in the Customers Only section of LIMS.com. Copy the templates to the appropriate LIMS folder, as described below.

Now log on to LIMS using an account in the Admins security role. Open the Workstation Configuration screen on the Admin menu, and select the Folders tab (see Figure 1). Verify that folders for both your Excel export and import templates have been selected. If necessary, use the [Browse] buttons to create new folders on your file server or select the folders in the Examples folder where LIMS was installed.

Figure 1 Workstation Configuration

Close the Workstation Configuration screen. Now copy the file ‘LIMS BAX Rack Setup.xlt’ to the Export templates folder. Then copy the file ‘LIMS BAX Data Import.xlt’ to the Import templates folder.

Each of the Excel templates has a Settings worksheet with a few options that you must configure for your LIMS implementation. Open the file LIMS BAX Rack Setup.xlt from within Excel (NOT by double-clicking within Windows Explorer), then select the Settings worksheet (see Figure 2) by clicking the Settings tab.

If necessary, edit the formula in cell B2 to provide a default file name without the (.bax) extension for the BAX pre-run files created by the template. Note that the default file name can be changed when the BAX file is created. Now enter your LIMS analyte names in column A, adjacent to the BAX target names in column B. The template will use this cross-reference table to enter the proper BAX Target name in the BAX file for your LIMS analytes. Be sure that the list is sorted in ascending order by LIMS Analyte Name, as shown. Use Data | Sort if necessary. Save your changes and close the template.

Figure 2 Settings for LIMS BAX Rack Setup template

Now open the file LIMS BAX Data Import.xlt from within Excel (again, NOT by double-clicking within Windows Explorer), and again select the Settings worksheet. Enter the values you use for Positive, Negative, and Indeterminate result types in the LIMS. Note that to import results, these values must exactly match the values specified in the Report Format column on the Result Types Setup screen in the LIMS Setup menu.

Still in LIMS BAX Data Import.xlt, select the Results worksheet (see Figure 3) by clicking its tab. Because LIMS will import BAX results from the Results worksheet, you should refer to this sheet while configuring the Excel interface for each LIMS analyte. Excluding “Sample ID”, the values on row 4 of the Results sheet are the exact names of the target values found in a BAX data file. The lengthy formula in each of row 5’s cells (cell E5 is shown as an example) uses the name in the column’s row 4 cell to look for a result for the target for the LIMS sampleID that will be listed in column A. You can extend the template for additional or future BAX targets by simply adding the target name in a new column and copying an existing row 5 formula to the new column’s cell. Save your changes and close the template.

Figure 3 LIMS BAX Data Import template’s Results sheet

If you have exited LIMS, log on again using an account in the Admins security role. Now open the Analysis Setup screen on the Setup menu, switch to Edit Mode, and then select your first analysis whose results will be imported from the BAX (see Figure 4). Select the Excel Interface tab, then use the [Browse] button and select the template LIMS BAX Data Import.xlt. Referring to the Results worksheet in the LIMS BAX Data Import.xlt template (see Figure 3), complete the analysis’ Excel interface configuration as follows:

Workbook Template:	LIMS BAX Data Import.XLT
Results Worksheet:	Results
Result Start Cell:	B5 (C5, D5, etc.)
Direction:	Column
Offset:	1
Sample ID Start Cell:	A5

Be certain the appropriate Result Start Cell for the current analyte is set to match the exact location this BAX target will be found on the Results worksheet in Workbook Template LIMS BAX Data Import.xlt. For example, the screen in Figure 4 shows the Result Start Cell correctly set to B5 for analyte E. coli O157:H7; however, the Result Start Cell should be set to C5 for analyte Genus Listeria.

Configure all the LIMS analyses which will receive results from the BAX. After configuring your LIMS analyses, close Excel saving your changes to the templates. You are now ready to use the LIMS and BAX interface.

Figure 4 Analysis Excel Interface Configuration

Part Two: Creating the BAX Pre-Run File

To create a BAX pre-run file, you will simply query and preview an LIMS Worksheet Report, then export its data to the LIMS BAX Rack Setup template. This template is specifically designed for LIMS’ Worksheet report.

Use the Work Sheet report option on LIMS’ Notebook menu to open the Work Sheet Setup dialog (see Figure 5). Now select one or more analytes that will be part of the BAX run. Use the standard query controls to enter your query criteria to locate the selected analyte(s) in specific samples. Use the [Query] button to query the LIMS database for matching samples. If necessary, use the [Select] button to refine your selection.

After querying Work Sheet samples, use the [Preview] button to preview the LIMS Notebook Worksheet report. In the print preview window, use either File | Export to MS Excel Template, or the equivalent toolbar button, to open the Excel Data Transfer popup. Select the LIMS BAX Rack Setup template from the list of templates and click the [OK] button to export the report’s data.

Figure 5 Work Sheet Setup Dialog

After you export the Worksheet report’s data to the LIMS BAX Rack Setup template, a new workbook created from the template will automatically appear in Excel (see Figure 6). The workbook’s Rack Setup worksheet will show a list of all analytes queried and exported from the LIMS. The list of analytes is automatically sorted by BAX target, then by LIMS sample ID.

Following is a summary of the tasks performed by the template’s hidden AfterTransferFromLIMS macro:

1. Turn off screen updating so the user does not have to see the macro’s work.
2. Create named ranges on the LIMSData sheet for each column transferred from the LIMS.
3. Sort the data on the LIMSData sheet by sampleID.
4. Query the LIMS for the work sheet report’s analysis data and write the data to the AnalysisData sheet beginning at cell D2.
5. Copy the SampleID, Description, and Target formulas on the AnalysisData sheet in cells A2:C2 to each row below with data. Sort the data by Target then by SampleID.
6. Copy the SampleID, Description, and Target data on the AnalysisData sheet to the Rack Setup sheet beginning at named range LimsID.
7. Switch to the Rack Setup sheet and turn screen updating back on.

To populate the BAX rack well positions in rows 2 through 9, simply cut and paste or drag the SampleID, Desc. and Target data from the sorted list, into the appropriate well locations in the upper right of the worksheet. The worksheet uses split and frozen panes so you can simultaneously scroll vertically in the LIMS data and horizontally in the well positions. Alternatively, select one of the predefined well populating patterns from the pick list and use the [Populate Wells] button to automatically move the SampleID, Desc. and Target data to well locations.

After you have populated the BAX wells, use the [Create BAX File] button to create the BAX pre-run file. The BAX file name will be set to the value configured on the template’s Settings sheet (refer back to Figure 2); however, you can enter any appropriate name for the file. Now select the appropriate local or network folder for the file and click the [Save] button to create the file. Now use the BAX software to load the pre-run file, and run the BAX as usual.

There is no need to save the workbook you used to create the BAX pre-run file, because you have created, named and saved a new file. Simply close Excel without saving the workbook.

Note that the hidden code behind the [Create BAX File] button simply saves all of the data on the BAXData worksheet to the specified file in tab-delimited text format. Any future changes to the BAX file format (e.g. for new BAX software updates) can be made by updating the data and formulas on the BAXData worksheet in the template.

Figure 6 BAX Rack Setup in Excel

Part Three: Importing BAX Results

After your BAX instrument run you can import the results into LIMS from the saved BAX file using LIMS’ Results by Analyte screen. Use the Results by Analyte option on the Samples menu to open the Results Entry by Analyte Setup screen. Enter query criteria to retrieve all samples whose results are now available in the BAX file. You can query a specific analyte or omit the analyte to retrieve all analytes in the queried samples. Click [OK] to view the Results Entry by Analyte screen.

With multiple analyses for one of your BAX analytes listed in the Results Entry by Analyte screen, use Spreadsheet | New to open a new workbook created from the LIMS BAX Data Import.xlt template. The Results worksheet will automatically appear in the new workbook. Click the [Import BAX Data File] button, then locate and select the BAX file to import. All LIMS samples and their results will automatically appear in the Results sheet (see Figure 7), and you can view the raw data from the BAX data file on the BAXData sheet. The data is ready for import into LIMS.

On the Results sheet, use the [Return to LIMS] button, Alt+Tab, or the Windows taskbar to return to LIMS.

Following is a summary of the tasks performed by the hidden macro code after clicking the [Import BAX Data File] button and selecting a BAX data file:

1. Copy all of the data in the BAX data file to the workbook’s BAXData worksheet then delete any blank rows. Note: older versions of the BAX software use Cr Cr Lf to end a line resulting in blank rows after import.
2. Find the first row with a cell containing the value “Well Info” in the BAXData sheet then copy that and the next 96 rows of data to the LookupTable sheet beginning at cell B1.
3. Copy the SampleID:Analyte formula in cell A1 on the LookupTable sheet to the next 96 rows then sort the LookupTable sheet by SampleID then by Analyte (i.e. BAX sample type).
4. Change BAX numeric result values to LIMS result types using the result type configuration on the Settings sheet.
5. Add a list of distinct LIMS sampleIDs found in the BAX data to the SampleIDs worksheet and create named range SampleID for the list.
6. Copy the OneSampleResults named range on the Results sheet to consecutive rows once for each LIMS sample ID on the SampleIDs sheet. Note that the formulas in the OneSampleResults named range perform lookups into the data on the LookupTable sheet for each sampleID and each BAX target.

Figure 7 BAX Results Ready for Import into LIMS

In the Results Entry by Analyte screen, use Spreadsheet | Import to import the results for the current analyte and display the Excel Data Import verification screen (see Figure 8). Verify the data imported from Excel. Note that the Status column will list the reason why a specific result cannot be imported. Use the [Cancel] button to abandon the import or use the [Import] button to proceed and import the data into the Results Entry by Analyte screen.

If your BAX data file has results for additional analytes, use the LIMS’ [Previous Analyte] or [Next Analyte] buttons to navigate to the next analyte, then use Spreadsheet | Import to import that analyte’s results.

After you have imported all results from Excel, simply close Excel without saving the workbook. Your results now exist in both the BAX data file and in LIMS.

Figure 8 Excel Data Import Verification