LIMS and QuickBooks

This article offers a good overview as guidance for those who want to integrate LIMS data with QuickBooks. Note that similar procedures may be used when integrating LIMS with other accounting software.

First, you will need to decide whether you will create your invoices in LIMS or in QuickBooks. Each approach has its advantages. We have users doing it both ways, but the majority create their invoices with LIMS and an Excel template then import the receivables into QuickBooks. In either case, LIMS data is queried into an Excel workbook then imported into QuickBooks using a third-party utility (see below). If you create your invoices with LIMS and a modified copy of one of our example Excel invoice templates, you can update each invoiced LIMS sample with its invoice number and invoice date. This allows you to easily find invoiced and un-invoiced samples in the LIMS using options on the Additional tab of the query controls as seen below. You can then use invoice numbers or dates to query data to import into QuickBooks.

If you use an Excel invoice template, make sure the ‘Save Invoice# in LIMS’ option is enabled on the Settings worksheet in the template then generate an invoice by exporting an Invoice report to your template. Next, use the [Print and Update Last Invoice#] button and the invoice number and date will be saved to the LIMS for each sample on the invoice.

The easiest way to get LIMS data for QuickBooks into Excel is to query the data directly from Excel using the LIMS Data Query workbook. See “Updated LIMS Data Query Workbook Now Available” in LIMS Insights Issue 23 for more information. We can provide assistance to help you create an appropriate Structured Query Language (SQL) statement for the LIMS Data Query workbook to query invoice data. We can also provide a modified version of the workbook with a simpler user interface for querying the LIMS data as shown below.

Once the LIMS invoice data exists in Excel a simple third-party tool can be used to import the data into QuickBooks. We have learned that the $250 Transaction Pro Importer is widely used to import data into QuickBooks for many different types of transactions including invoices. The screen below shows the one-time mapping of Excel columns to QuickBooks fields in Transaction Pro Importer. After the mapping is configured and saved you simply use the map file each time you import LIMS invoice data into QuickBooks.

See Transaction Pro Importer for QuickBooks Mapping Screen Tutorial for more information on how to map Excel data to QuickBooks fields.

You may find two LIMS fields helpful when integrating with QuickBooks. For example, the QuickBooks customer number or name can be maintained in the Account# field on the Customers setup screen and the QuickBooks item can be entered in the Account# field on the Analyses setup screen. Simply include the Account# fields in the data queried in Excel then map them to the QuickBooks field.

With an appropriately configured LIMS Data Query workbook and Transaction Pro Importer it is easy to import LIMS invoice data into QuickBooks and integrate these vital applications.