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.