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.
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.
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:
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.
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?
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.
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.
Access vs SQL Server - What is Better for Your Lab's LIMS?
For many people the thought of having to understand database architecture and/or mechanics may seem dizzying. It certainly can be, especially if database architecture is not your wheelhouse.
That's okay, you don't have to understand the databases in detail. What you really need to know is how different types of databases vary.
SQL Server is a full blown enterprise RDBMS (ODBC compliant) database package whereas MS Access is really a desktop (ISAM) database tool. To be fair, MS Access is an amazing product for a desktop app, but it cannot scale nearly as much as SQL Server.
Whether you are looking for the best LIMS program that money can buy, or simply the most affordable LIMS program for a small lab, scalability will eventually become an issue.
SQL Server is a 'real' database system, on the same level as Oracle and Sybase. Some individuals would also like to add MySQL to this list, however MySQL is a free database package and ultimately you do get what you pay for.
SQL Server is for multiple users. MS Access is technically for multiple users, but have you used it in this fashion? Have you had twenty or more concurrent (simultaneous) users? How did that work out for you? - Please feel free to leave a heated comment below about any interesting experiences on this. Feel free to vent on our dime.
SQL Server is designed to handle many more users. As a result of this, SQL Server is more expensive, not just to acquire the program, but additionally their are user licenses (CAL's) to consider. That being said, it is not entirely unexpected. A typical new automobile is much more expensive than a typical new bicycle, but no one would really question that, would they?
In the old days (late 90's) many smaller labs would come with a home grown LIMS in MS Access that would eventually need to be converted to a SQL Server backend with most likely a VB front end. These days you are almost better off starting right with SQL Server, especially since you can start off with SQL Server Express, a free solution. The thought is that you can upgrade to a more robust version of SQL Server as your business grows, which means that you will be able to afford the more robust version.
Another difference is that MS Access needs to constantly be monitored. There is a setting called "Repair and Compact database". Seriously! What other apps comes with a built in feature that basically says: "We know this will break" ?
You are probably past asking "do I need a LIMS?" or "Does my lab need a LIMS?" since the answer is painfully obvious. Today it is not a question of whether or not you need a LIMS. Maybe that was valid eighteen years ago. Today the real question is how extravagant of a LIMS solution do you need?
Many Access based apps may work fine for you, for a while, but you will eventually need to scale upward, if you are planning on growing.
Some LIMS vendors, like Lablite, makers of Lablite SQL LIMS, originally only offered an Access based app, but quickly scaled up to a full blow SQL Server backend. Note: to give you an idea of how long ago this was, you could install the app with a stack of twenty or so 3.5 inch floppies. We are talking about the stone ages.
One last thing to say about Access. If you think that as a stand alone app your Access database isn't really a Client Server app, or more generically that it doesn't have a front end and a back end, well, guess again.
Most developers are smart enough to create two separate Access files. The back end file contains the data tables. The front end has the forms and reports, or the "GUI".
This is so that the developer can make changes to a copy of the front end (reports and forms) while the back end is still being used by the lab. Later, when the changes are made, the developer just needs to replace the front end file, for the lab to see the report changes and /or the form changes, but the back end database has remained undisturbed.
At run time, the two files need to be synced up in order for the whole app to work. Does that sound like a stand alone app to you?
For more information on the topic of databases, see the article:
SQL Server or MS Access, which is right for me?
