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.

Getting around the pitfalls of QC with your new LIMS

If you've been working with a LIMS for any length of time then you either love or hate the way that your LIMS software handles QC. This topic can be very polarizing.

Think about it: The best LIMS is not necessarily the same thing as the most sophisticated LIMS, unless that is what you want.

Also, the most sophisticated LIMS may not necessarily be the same as the easiest LIMS to set up, so again, it depends what you need.

-----------

So how do you go about auditing your own needs prior to LIMS shopping?

Do you just line up a few online demos and then listen and see what sounds good, and then make a decision based on price?  That may work for a gym membership, but what about a new car? Wouldn't you tend to research the different car choices with a little more effort than you would for a one year gym membership?

The same goes for purchasing a new instrument for the lab. You better believe that you are going to do the research and shop for the best deal, and ten haggle on top of it, just on general principle.

So, um, why wouldn't you use the same intelligent approach to shopping for your next LIMS? It might be because this is only your first or second time shopping for one.

Here's an idea: Why not make a list of your "must have" features that you are not willing to compromise on. Then, you will be able to quickly eliminate any products that don't meet all of your "must have" items. After that, other bells and whistles are nice, but can in no way make up for a deficit in the "must have" department.

For example, when it comes to using QC, do you need the ability to create charts and graphs of results over a period of time? Would you rather this be exhaustively complicated or as easy as a few mouse clicks?

No one stops to think about these things until after they by an inferior LIMS product that will be the bane of their existence for several years. Once bitten twice shy.

How easy should it be to set up a QC run? How easy should it be to set up a QC run template for a specific method, with a specific standard?

If you must have these things be easy (and who wouldn't?) then if you see a demo where these common activities are difficult, no amount of other features will make up for this. Would you be happy with a five dollar cappuccino if they gave you an extra shot of espresso for free, but the cup was leaking? No way!

---


The reason why QC is so important for testing labs is because QC is how you defend your data.

When in inspector steps in, what do they care about the most? Isn't it QC, I mean, after the selection of donuts.

One lab owner had this to say: QC Graphing is a must!

Does your current LIMS offer quick and robust QC graphing, with enough bells and whistles to appease even the grumpiest of inspectors?

What really is the best LIMS for small lab personnel when it comes to how the QC component is handled? Feel free to leave a reply below.