How to Integrate your LIMS with Quickbooks

Many labs use a professional stand alone accounting package to run their labs, like Intuit's QuickBooks or Sage 50 (formerly Peachtree). Although their respective LIMS packages may offer some accounting and billing capabilities, their labs all share the following reality: Your accounting needs are not limited to your LIMS. There are other concerns such as:

• Payroll
• Rent
• Operating Expenses
• Chemicals/Solvents/Standards
• Instruments

This is why many labs prefer to capture the initial billing information via their LIMS, like the cost of a sample or a series of related samples, and send out an initial invoice, perhaps as an email attachment. But then they will usually opt to have that initial cost data sent over to QuickBooks, where they can then manage the billing through to final payment.

Let's look at this whole process one step at a time. There are really four separate logical parts to this entire process. They are:

• Setup
• Custom Programming
• The Export à Import Process
• Important Notes to User

We will now look at each section one at a time.

Setup

• Must have the same Customer name, or it will create a new customer (not account # based)
• You can do a dump of the initial customer list in your QB, then have your LIMS provider add a field in the LIMS so that you can match the names by hand (a combination of selecting LIMS Client names from drop downs and then matching them up with a text field that represents the QuickBooks Client's corresponding name.

The cutting edge LIMS Vendors that can handle QuickBooks integration can usually get your client list from QuickBooks and then try to match the accounts for you with the Clients in your LIMS. If they can do this for a reasonable fee, to get you started, it will save you not only HOURS of time in setup, but also it will save you from many client account conflicts when importing invoice data into QuickBooks. This is because if the client name is not a match, then QuickBooks will think that it is a new Client and will create a brand new account. Now, while this is completely fixable within QuickBooks, let me ask you: How long will it take for that process to get old?

Custom Programming

• Send over just the total dollar amount.
• IIF file

Just to forewarn you - The LIMS Software Vendor will need a copy of your QuickBooks company file. This is because not every lab uses QuickBooks in exactly the same way (imagine that, right?) and so the developers will need to see how you name and use "split accounts" (like Account Receivable) for record keeping purposes.

The Export - Import Process

Your LIMS doesn't actually "talk" to QuickBooks directly, this is a two step process. Notice the two parts:
 
• The Export from the LIMS to an "IIF" (text) file.
• The Import into QuickBooks via the Utility menu

This process should be set up so that you can invoke it at any time during the day or week. Experience has shown though that typically this is something that labs do once a day, usually near the end of the day.

When you are importing the IIF file into QuickBooks you need to be aware of a few rules:

• You must be logged into QuickBooks as an administrator
• QuickBooks needs to be opened in "Single User Mode"

The system should only send over records that meet certain criteria. Here are some examples of this:

• The record should represent either a sample or samples that are both "Approved" and "Reported"
• The record cannot already be flagged as "Exported to QuickBooks" - This requirement is always true, regardless of the lab

When you create the IIF file, and before you import it, you may be tempted to open it and look at it. We humans are curious creatures. If you are this type of person (and you know you are) please make sure to read the section below that will go over the pitfalls of opening the IIF file in Excel.

Once you are ready to import the file, you go through the steps inside QuickBooks to make it all happen.

If you are successful (no errors) then QuickBooks will tell you right away.

Your LIMS should then give you the ability to flag the records as exported, so that you don't keep exporting the same records.

Note: The flagging of results in your LIMS software as "Exported to QuickBooks" should ONLY happen AFTER you do the IIF import into QuickBooks.

This way, you only flag the records as exported once you know that QuickBooks had no issues with the data.
 

Important Notes to the User

DO NOT open the IIF file in Excel and then save it, ever! Excel will add a ton of formatting information in the invisible header portion of the file. Once that happens, the file is no longer importable into QuickBooks.

Notepad is much "safer" to use than Excel, however you won't be able to read anything, because the columns will not line up as you go down record by record.

Downlaod a free editor like CSVed




You may have to look hard on Google to find the right LIMS vendor to do a full up LIMS QuickBooks Integration, but it will be worth it.