Perhaps you’ve used a home grown accounting system for years. Maybe it is written in Excel or MS Access, and it has worked for you thus far. Nevertheless, all labs that experience true growth will graduate to a professional accounting software package like Intuit's QuickBooks or Sage 50 (a.k.a Peachtree).
This is true partly because your accountant or bookkeeper will appreciate you using a program that they are familiar with, as opposed to them having to “learn” your system. This saves you money in two ways:
- Your accountant will save time* when going over your records.
- A package like Quickbooks is designed by a company that does strictly that, making the industry’s premier accounting software.
* Note – Your accountant’s saving in time alone will more
than pay for the cost of QuickBooks.
Cut over now.
In addition to your LIMS needs, switching over to a “real” accounting program also helps you track other, non-LIMS transactions:
- Rent/Mortgage/Taxes/Utilities
- Salaries
- Operational Expenses
- Insurance Premiums
- Supplies
- Instrument Purchase/Maintenance, etc
Therefore, if you have a software package like Quickbooks,
then it is a logical conclusion that you need to find a way to easily export
data from your LIMS and then import it into Quickbooks.
Notice that we said “easily”.
Notice that we said “easily”.
Many labs opt to capture the initial billing records
directly from the LIMS: sample cost / group test cost / COC batch test cost,
etc… Then, this initial bunch of data is used to construct an initial invoice
straight from the LIMS, perhaps even using an “Auto Email – PDF Invoice” strategy
as well.
However, after this initial billing has been sent out, what
do most labs want to do? They wish to have any further billing interactions continue
directly from QuickBooks, since this is how they will be handling everything
else, beyond generating the initial invoice. This is because QuickBooks is a much more robust tool for tracking future billing, payments, accounts receivable, etc.
Note: The process that is being described can be coded by any software developer at a plethora of affordable LIMS program companies.
If you think that maybe you aren't able to afford the best LIMS software on the market, then why not target the best LIMS for a small lab or starter lab?
Here is a step by step outline of how this LIMS Quickbooks integration process works:
- Customer/Client Record Setup
- Custom Development/Programming
- Export /Import Functionality
- Notes to End Users
Customer/Client Record Setup
- Both systems, the LIMS and Quickbooks, must have the identical Customer Names, or else Quickbooks will create a brand new customer record upon import. Once this happens it can be a nightmare trying to reset the data from within Quickbooks. Note: Some people seem to think that all you need to do is match on the Account Numbers, but that is simply not true.
- It is highly recommended that you do an export of the customer list from your Quickbooks company file. Next, you need to have your LIMS provider provide a dedicated field in the LIMS system for mapping to the customer records in Quickbooks. This is so that someone (like an admin) can go in and match the names by hand (usually with the help of a dropdown of LIMS customer names) and then a text field for entering the Quickbooks customer name that matches.
The leading LIMS suppliers that can usually handle QuickBooks integration setup and are able to obtain your client list from QuickBooks. They can then match the accounts in your behalf, saving you hours of time.
When this can be done for a nominal fee, it’s so worth the expense. On top of that, it will spare you the agony of unanticipated client account conflicts when importing billing records to QuickBooks.
Custom Development/Programming
This next part covers some of the "structural" parts of the import process. You will need to make some critical decisions. My advice is to not make any "snap" decisions until you have had at least a day and a night to mull these items over. - Yes, they are that important!
The two items that we will cover below in more details are:
- Whether or not to send over the just total dollar amount for the invoice, or every line item
- The IIF file (Intuit Import File)
Your LIMS Provider will need a copy of your
QuickBooks company file. This is so they can see how you are specifically using your "split accounts" (Accounts
Receivable, etc...)
/////////////////////////////////////////////////////////////////
Export/Import Functionality
Your LIMS doesn't actually "talk" to QuickBooks directly, this is a two step process. Notice the two parts:
• Part 1 - The Export from the LIMS to an "IIF" (text) file.
• Part 2 - The Import of the "IIF" file into QuickBooks via the Utility menu.
Note: The .IIF extension stands for "Intuit Import File".
This process should be set up so that you can invoke it at any time during the day or week.
Experience has shown however 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.
Note to End Users
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.
You are much better served by having a separate editor (program) installed on your system specifically for the purpose of viewing CSV files. I personally use a program called CSVEd. It is robust and free.
Download CSVed
Note: If you want to open a CSV file with a new app like CSVEd, you will need to tell your PC that you want to switch the "default program" for CSV files from Excel to CSVEd. To do that, right clikc the CSV file and then select "Open with...", then select 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.
