Your Lab’s “LIMS to Quickbooks” Export – Defining the Challenges

Does your lab use a professional, off the shelf accounting package?

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”.

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.

The three levels of Instrument Interface software

Testing labs love to have things automated. Who doesn't? This is especially true when it comes to result data from testing instruments.
Why would you ever want to spend the time to re-enter results by hand, with the risk of human error on top of it, when the process could happen automatically and electronically?
When it comes to having result data imported into your system, nothing beats a custom "instrument interface". An instrument interface is a software process or software program utility that will handle the importing (INSERTING) of your result data (RECORDS) into your back end database (RDBMS).
Note: Please don't tell me that you are still using an MS Access based LIMS! Please tell me that the back end is an enterprise level RDBMS like SQL Server / Oracle / Sybase / MySQL.
Today you can get very good LIMS Software at a fair price from a bunch of affordable LIMS companies. These affordable LIMS vendors can easily provide you with informative online demos of their LIMS products.
Having this kind of a process set up for your suite of instruments is a no-brainer. Even if the initial cost per instrument to set this up is somewhere around $1500 in programming fees, think of the time you will save, EVERY DAY, for several years. Again, it is a no-brainer. - Add to this the fact that the the data will be recorded correctly, without the chance of human error creeping in.
Question: How much is that worth to you? How costly would it be to lose a large customer because someone hand entered a result of 222 ppb, instead of 22 ppb, and then the results were flagged to the state? Yeah, you can't undo that too easily, can you? - Goodbye large customer.
Does the idea of automating this process sound good to you? Would you like to step up and have your data automatically imported? If so, there are some decisions that you will have to make. This is because there several different ways that this process can be approached. They each involve varying degrees of end-user involvement. We will next discuss and explain the three different approaches/strategies to utilizing an instrument interface. Determining which way is ultimately best for you can actually vary from instrument to instrument, so you really need to think about this.

1. Simple (to end users) - Fully Automated - SQL Server Only - Maps to Existing Samples

This is the typical, most common form of an instrument interface and/or importation of results from a file that comes from another lab (contract lab).
This configuration features all of the following components:

• The samples HAVE been logged into the system, by a human.
• The results will be automatically imported via SQL Server processes.
• There is no UI in which end users can "eyeball" the records prior to being imported.

2. Simple (to end users) - Fully Automated - SQL Server Only - Creates the Samples

This approach is less common than the first approach, as it takes more time to set up the processes. The samples are not created by and end user before hand, the interface takes care of this for them. This approach usually only works when the samples being imported are not heavily laden with metadata (sample properties).
This approach features all of the following components:

• The samples have NOT been logged into the system. They will be created automatically by the interface.
• The results will be automatically imported via SQL Server processes.
• There is no UI in which end users can "eyeball" the records prior to being imported.

3. Super - Provides UI for Users to Inspect, Modify, Reject Records prior to Import

This is the least common form of an instrument interface, as it is the most complicated to set up. It is primarily reserved for situations where the data is "high maintenance" and needs to be visually "inspected" and "okay'd" by a human being..
This configuration has all of the following features:

• The samples may have been logged in ahead of time by an end user, or they may not have been.
• There is an UI in which end users can "eyeball" the records prior to being imported.
• The UI may also allow users to reject records.
• The UI may also allow users to add lookup data to the system: customer records, site records, etc.

As you can probably guess, this type of interface is by far the most expensive from a software development perspective.

In Summary

There you have it folks! I hope this helps. I have personally written over fifty different interfaces over the years, so I know what's going on, and what it takes to get this working. I've done every type of interface described above. I've done every format: CSV, XLS (non-CSV), TXT, XML, jagged TXT (ugly ASCII), etc. I've even had to write interfaces for data coming out of SAP. (That was fun...) That being said, if you are needing advice, or if you have any questions, please feel free to comment below, or email me directly: dmerton@lablite.com

Auto Emailing - Safeguards Part 1 - Special Characters

It is true that the best LIMS programs out there will offer special features like the auto emailing of results (and possibly invoices) to your customers. Even an affordable LIMS program should be able to provide this capability. To this email subject (no pun) we need to understand certain issues that can creep up.

Some special characters are allowable in an email address, like the apostrophe. The email address:

      liam.o'brien@notarealemailaddress.com

...is valid as far as SMTP is concerned. However, you are probably setting yourself up for a whole slew of unintentional heartache by having that special character in the email address.

There are a few reasons:

• The need to 'escape' the character in program code
• The possibility of conflicting ASCII values for similar text characters
• The simple fact that an apostrophe in an email address is incredibly rare (and thus, software developers rarely consider this)

Let's take a look at each of these items in more detail below:  

Escaping Characters

Escaping and URL Encoding are two techniques for dealing with certain types of "special characters". An example of escaping is when you are using code that cannot handle a single apostrophe as normal text, since a single apostrophe is a text delimiter.

What would have to happen is you'd have to double up the apostrophe in any text value if it is already inside a block of text that is being started and ended with single quotes (apostrophes).

An example of this would be in TSQL, the command line language for SQL Server:

Bad

     SET @Email =  'liam.o'brien@notarealemailaddress.com'

Good

      SET @Email = 'liam.o''brien@notarealemailaddress.com'

ASCII Issues

There are also some really interesting things that can happen when you are copying text from one editor to another, most notably from MS Word to almost anything else. In most contexts, you use the same keyboard key to type either a single quote or an apostrophe. However some text editors will supersede your wishes and alter the nature of some characters, like a right single quote (U+2019) vs an apostrophe (U+0027).

You need to be careful because in some systems, the characters are very different, where as in others they are the same. 

A true single quote or apostrophe has an ASCII value of 39. This true apostrophe is allowable in an email address, however what if the email address was first dumped into MS Word?

In MS Word, you can also have a left single quote (ASCII = 145) and a right single quote (ASCII = 146). Now, in SQL Server, with a typical collation of SQL_Latin1_General_CP1_CI_AS, the same two ASCII values will map to the same actual characters as in MS Word. However, this is not necessarily true of other apps on your PC. 

The left and right single quotes are not valid for email addresses, so depending on how the email address was initially stored into a database is very important. If there was a cut and paste, it may have a character that looks like an apostrophe but that will not be recognized.

It might be better to simply stick with letters and numbers, but you certainly can't control what your customers may do. Just be careful when entering the email addresses into your LIMS software. If it has an apostrophe, make sure that it is not curved, but straight up and down. This will save you lots of troubleshooting later.

So, whether your lab has the easiest LIMS to set up on the planet, or it has a monster LIMS solution from a huge LIMS vendor, just make sure to scrutinize email addresses for this potential issue.

This will ensure that you have the best LIMS auto emailing experience possible.

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.

Granularizing Units of Measure

Do you think it is necessary to have different lists (drop downs) for your units of measure in your LIMS software?

For example, should you have just one monster list that will be used in all places, or should it be filterable? If so, then you need to start asking "Filterable by whom?" Logically you then need to ask "Filterable how?"

Should there be more than one list? Multiple lists can be wearisome since you would no doubt need to have some of the same units in both lists, yet they would have different underlying key values in their respective tables. BTW - This specific scenario is just a data corruption petri dish waiting to happen.

The debate rages on for a few simple reasons: Some units of measure may only be appropriate for actual testing and then there can be sub sets of this list for prep analytes, etc.

There may be other units that are strictly for the purposes of QC.

There could even be other labs, perhaps contract labs, that will send you CSV files of data, with units of measure that will not match your own. You may have to have a specific lookup table, to map unit to unit, just for this.

Note: If you are dealing with multiple contract labs that are feeding you with instrument driven CSV export files, then you might have to have separate custom designed setup tables, just for mapping the units to the subsequent contract labs' different pantheon of unit names.

BTW - for a great article on exporting data from a LIMS into CSV format see the article:
LIMS REPORTING - A Slick Way to Export Result Records into a CSV File. 

Aside from all of these possibilities is the nastiest of all, when the state that your lab is in requires your reports to list units of measure in terms that they have determined and mandated, not necessarily the terminology that you maybe used to using. - This is one situation that you really cannot ignore.

But how can you handle this?

At least with analytes you don't have to worry about analyte names, when sending results to the state, because the storet codes are universal and indisputable. Either that or the state will have their own codes for each contaminant. State contaminant codes are usually available on the state's web site, although they are not necessarily up to date. Until such a time when they create the "Unit Storet" or maybe just the "Unet" you will have to have a real way of mapping the units.

Don't be lulled into thinking that your programmer can set this up for you one time and then walk away. You need to have a tool where you can add new units (from the contract lab) to your setup table to map to your database's unit counterparts. This is because, no matter how much everyone on both ends swears up and down that none of the units will change, just give it two weeks. The minute the programmer's check from you clears the CSV file will have a new unit. Trust me on this. I am a LIMS software developer, and I know!

One smart way to handle all of the situations referenced above would be to have extra checkbox fields in your Units setup table that would allow you to determine the nature of the particular Units of measure.

For example, you could have the following checkbox fields:

• Testing
• QC

In addition, you could have other fields, text fields, where you could enter a special name for the units of measure.

• State Reporting Name
• Contractor Lab "A" Name

Having additional checkbox fields as well as text fields will help you to refine the units that you will be using in the LIMS. You might choose to populate one dropdown with all units, where as you may load a second dropdown with only QC specific units.

The easiest LIMS to set up may not be the easiest LIMS to live with.

If you think that you would benefit from this approach, speak to your LIMS vendor and see if they can provide you with a way to granularize your units. Why take the risk of having inappropriate units of measure be allowed to be selectable in certain situations? Nothing is worse than corrupt records in a LIMS, especially when it is the inspector that seems to locate them.

Can your LIMS handle Excel-like calculations?

When it comes to doing the same things over and over again, you need to make sure that even the simplest of keystrokes are necessary. If any steps prove to be unnecessary, and therefore avoidable, then it makes sense to do just that.

Steps that are not vital need to be nipped in the bud.

Question: What can you do if your LIMS program doesn't allow you to save these minor steps?

Answer: You can't do a thing about it!

When it comes to the nitty gritty things, like analyte calculations, doesn't it make sense to keep things simple?

Usually lab analysts find it easy to use Excel based formulas, since it is what they are used to. However, most LIMS programs that use a SQL Server backend may use calculations that are based on the underlying query language of SQL Server, known as T-SQL.

Now, since SQL Server and MS Excel are both Microsoft products, you would think that they would agree on the language of calculations (formulas in Excel) but there are a few times when this is not the case. it is important to know when they disagree.

One example is a very common formula in Excel which is the formula for calculating averages. Excel's formula language varies from the T-SQL function language in SQL Server.


Excel:

                                 AVERAGE()


SQL Server:

                                AVG()


So you can see that there are a few differences. The list of differences is not that difficult to learn.Not every large one-size-fits-all LIMS package necessarily means that it is the easiest LIMS to set up. You also have to consider how easy it will be for both you and your staff to use on a daily basis.

Probably the two most difficult things in a LIMS program are the QC modules and the calculations. When you put those two items together, you really need to have the app be as intuitive as possible, otherwise you will be shopping for Prozac.

Another related issue goes way back to VB 6. VB 6 and SQL Server, again, both Microsoft products, did not evaluate "is numeric" the same way. If someone entered just a period for a result (Why would they? I've seen it happen a bunch of times.) one of the apps (VB/SQL Server would evaluate the value as numeric, whereas the other one would not. This created a nightmarish situation for us. 

The good news is that our Lablite LIMS program has a lot of the code written in the database in Stored Procedures, so the issue was easy to fix.

Make sure that you are getting a LIMS that is configurable.

Also, make sure that the entire program is not compiled. This make it impossible to customize. The more code that is not compiled (for the sake of this article) such as SQL Server stored procedures, the more flexibility you will enjoy. 

A LIMS can be a huge investment. Don't you want to reap huge dividends?

Is there a such thing as an Affordable LIMS?

You've probably looked around online and have come to realize that you can't just spend $100 to $300 to buy a LIMS. A real LIMS will cost real dollars.

That being said, not all LIMS programs are equal. Does big cost equal big value? That can be really subjective, unless you know what it is you want. The real answer is: "Not necessarily."

Is it realistic to think that affordable LIMS software can still be of the highest quality?

It's not impossible, but you have to shop around. 

Have you ever had back to back online LIMS demos from different vendors? This can be an all day event. Each demo has it's own learning curve too. By the end of the third demo, your eyes start to glaze over and you can lose the ability to think critically. - Sound familiar?

Consider this as an option: Ask each LIMS vendor to demo only a certain aspect of their program, like logging in samples. You can easily set up four or five demos like that in a single morning. If you like only three out of the five, great. Invite the three back another day to show result entry. This way, you didn't waste an entire two hours on the vendors whose programs didn't seem to fit your needs.