Log Samples from Excel

LIMS’ Excel interface is widely used to import final results and to export LIMS data. A main benefit of the Excel interface is reducing or eliminating duplicate data entry. If your customers or users already submit sample information electronically or you are considering this option you can improve your sample login procedures by logging samples from Excel.

LIMS’ Batch Login screen is a perfect solution when logging samples from Excel. The Batch Login screen stores sample information in its own temporary records table where you can build your sample batch dynamically, add and remove samples, and edit sample information before committing the samples to the LIMS database. Fortunately, this temporary records table is accessible to Excel so we can instruct Excel to add samples using Excel data.

Adding samples to the temporary records table is a very safe solution since samples are not committed to the LIMS database until the LIMS user completes the login in the Batch Login screen, and even here the samples can be edited or the login cancelled up until the last keystroke.

The best way to explore sample login from Excel is with an example. Remember, because we are using Batch Login’s temporary records table, you can safely try the example with your production database since you can abandon the batch without logging the samples. To follow the example you will need:

1. An empty dynamic batch. If you don’t already use dynamic batch login, simply create an empty batch named “Dynamic Batch” (for example) using the Login Batches screen on the Setup menu. See “Use a Dynamic Batch to Speed Login” KB article for more information.

2. The example workbooks from Excel Sample Login Examples. Download these from the File Library.

Begin by opening example workbook LIMS Sample Login.xls and review the additional information on the Settings sheet. Select the “LIMSInterface” name from the Name Box pick list (see the screen below) and observe that this named range identifies a table defining the cells on the first worksheet where LIMS sample field values will be found.

You can alter the Samples sheet to suit your needs then update the LIMSInterface table accordingly. For example, if you would like to import sample notes, add a Notes column to the Samples sheet then enter the notes’ cell range in the LIMSInterface table. Additional sample fields including custom fields can be added.

Recall that the minimum information required to log a LIMS sample is project, location, sample type, sampler, and collected date. In the LIMSInterface table in our example below, note that we are using the internal identifier for sample fields that are related to other LIMS data.

For example, since a sample must have a project and projects are configured elsewhere in the LIMS, a sample must include a value for the ProjectID field. We could use the project’s name (more on that below) but the name is subject to change and our workbook would still need to look up the internal ProjectID for a given project name. Similarly, we must include valid values for LocationID, SampleTypeID, and SamplerID.

The workbook is greatly simplified by using the internal ID values. To find the internal ID values we can create a query in the LIMS and view the internal IDs in the SQL constructed for the query.

Start LIMS, open the Sample Summary report option on the Samples menu then build a query by selecting a project, location, sample type, and sampler you want to use to log a test sample from Excel. Click the [Query] button then select the SQL tab of the query controls and the internal ID values will be listed under “SQL for last query” (see example below).

Now return to the LIMS Sample Login.xls workbook, select the Samples sheet and enter your internal values for ProjectID, LocationID, SampleTypeID, and SamplerID in one or more rows.

The example below shows the values added to the first five rows. In practice, you might create separate workbooks for different combinations of project, location, sample type, sampler, and customer or use distinct regions of labeled rows for different combinations.

With values now defined for ProjectID, LocationID, SampleTypeID, and SamplerID we only need to enter collected dates to log our samples. Optionally, we can enter collected time, customer sample ID, and description because these LIMS sample fields have been configured in the LIMSInterface table on the Settings sheet.

The screen below shows our completed test samples. To log the samples, open LIMS, use the Batch Login option on the Samples menu, select your dynamic batch then click [OK] to open the Batch Login screen with an empty batch.

Return to the LIMS Sample Login.xls workbook, select the Settings sheet and click the [Log LIMS Samples] button. You will receive a confirmation message showing the number of LIMS samples added. Click the [OK] button to dismiss the message.

Return to LIMS and view the samples added to the Batch Login screen (see screen below).

The samples have simply been added to the screen’s temporary records table so you can modify the samples and their analyses and complete or cancel the login as you would with any other batch.

Now that you have completed the example above you can begin to envision the benefits of logging samples from Excel. Let’s consider another example.

Open example workbook LIMS Sample Login – Pick Lists.xls and again review the additional information on the Settings sheet. You will see that this example defines lists of LIMS projects and locations with their internal IDs.

Switch to the Samples sheet and select a Project or Location cell. Notice the cell’s dropdown pick list (see screen below) which was defined using Excel’s data validation option and the corresponding table on the Setting sheet.

This workbook’s LIMSInterface table is the same as our first example. However, the Sample sheet’s cells where ProjectID and LocationID are found now contain formulas to look up the internal ID using the selected project or location name. Consider such pick lists when you want a more flexible method to select sample characteristics while restricting selections to a subset of options.

Explore these example workbooks and you may find ways to streamline your sample login process and begin logging samples from Excel with your own workbooks.