Issues with older VB 6 apps

How old is your LIMS Software?

Do you know what programming language your LIMS was written in? If it was a homegrown LIMS app? Is your LIMS an affordable LIMS software package from a professional LIMS vendor? Is it "old", like turn of the century? Is it specifically a LIMS for a small lab situation?

If your answers to any of these questions is "yes", chances are it is either a VB 6 app or an MS Access app.

If either of this possibilities are true in your case, you may be sleeping on a currently inactive volcano.

Why is that?

Because when a LIMS is being built it is usually tested with some data, but not necessarily thousands of records.  This is simply reality. Ask any software developer (after a few drinks).  Note: Almost all software developers drink...

Because of the reality of not developing a system with thousands of records, there are two things that will inevitably not be checked:

• Performance
• Overflow Errors

The first one, performance, is obvious, things run aster with only 500 records, than they will with 500,000 records.

The next issue though, "Overflow Errors" is a programming term. It has to do with programming "data types". Back in the old days, when a hard drive was a microscopic 64K, and RAM was extremely limited, you had to be careful how you used programming variables. The trick was to use the tightest or smallest data type for each specific variable, so as not to chew up RAM.

Note: Nowadays, this is really a non-issue. We have more RAN and disk space then even Gene Roddenberry or George Lucas could have imagined.

An example of this data type situation are two choices for storing whole number values. One is referred to as an "integer" and the other data type is called a "long". An integer variable can hold 16 bits of data, or 2^16. Since half of the values are negative, the positive range is really 2^15, or 32,768. A long is 2^32 (or 2^31 for positive numbers) and can go up to 2,147,483,648. It is twice as big to the computer, but in reality the long data type has a limit that is sixty five thousand times larger than an integer datatype.

So what does this mean?

If you are using a numeric data type in either VB 6 or MS Access (VBA) to hold a numeric value, then you have to give some thought as to which datatype you will use. If you are just assigning a number to items in a short list, like the fifty states, an integer data type will be fine. However if you are assigning a number that is equal to the amount of people that live in Virginia, then an integer will run out of space, since it can only go up to 32,768. If you try to set an integer variable to a number greater than 32,768, then BAM! you get an overflow error.

Why would anyone use an integer datatype then? It's habit. Kids in college were taught to use integer datatypes all the time in VB 6 classes. All the books used them too.  

It usually takes a seasoned LIMS software developer to realize that there will always be portions of code that can get by with an integer, most of the time, until you have that one special custoemr (lab) that does things a little differently. A table that usually has a few hundred records now has over forty thousand records. When they go to do anything in the system where an integer datatype is set to a value of 40,000 or so, it blows up.

Newer developers never consider this, due to lack of experience. Also, they are testing with databases that are nearly empty.  You really have to assume that any whole number based variable will surpass (or could surpass) 32,768. It actually takes less time to declare a variable as a "long" than an "integer" because there are less key strokes.

if you have an older VB 6 app or an older MS Access app, then I GUARANTEE you, it will eventually blow up, because of the integer / long scenario.

I just ran into this with a lab in Utah, and had to due a major work around for them since the program they have is compiled and they are not able to get a newer compiled version. Ouch.

They had a table that grew by a few hundred records a month. It took several years, but finally, this table grew beyond 32,768 records and then different things in the app started to break.

What can you do?

Upgrade your LIMS now to a nice juicy .NET app or even a web app. Be proactive. Don't sit back and wait for your ancient app to break, because break it will.

Make sure you know what is is you are looking for in your next LIMS.

You want your coffers to overflow, not you LIMS software application!

How Quick (or S-L-O-W) is Your Lab's Turn Around Time?

Do you complete samples within a day? A week?

How can you track this? Does your LIMS make this easy for you?

What about other software? Does your accounting software (Quickbooks / Peach Tree) tell you the average Turn Around Time that it takes for your invoices to be paid?

What about your inventory application? Does that track the turn around time between order placement and receipt of goods? How about between receipt and consumption?

Turn around time is everything, and you need a good dashboard tool that allows you to report on not only Turn Around Time at a high level, but also the ability to filter (or filter out) different types of data.

All of these questions are valid, and all for the same reason: Your testing lab may enjoy the most amazingly quick and prompt turn around times, which will no doubt boost sales and profits, however you are doomed if you cannot prove this electronically the one or two times that you are called on to do so.

The best LIMS for a lab is the one that allows you to quickly obtain the average turn around time, whether it be for all samples of a particular type (like compliance for environmental labs) for the friendly and uninvited inspector, as well as for a customer that calls you on the phone. Reporting and ad hoc querying is everything.

If you were accused, perhaps, of having less than exceptional turn around time, how would you be able to quickly and definitively dispute this accusation, assuming that it was false? The answer is to have an electronic dashboard, or dashboards, in place to allow the immediate quantification and display of this information.

Can Your LIMS Actually "Talk" to Your Customer Service Software?

Can Your LIMS Actually "Talk" to Your Customer Service Software?

This section describes and details the integration between LabLite CS and LabLite SQL (LIMS).  It explains not only the user interface but also how it can easily be configured and reconfigured to fit your needs.

The following areas will be discussed:

1. Automatic login of samples into LIMS.
2. Availability of Action numbers in LIMS.
3. Ability to do searches on both Complaint and Action numbers.
4. Automated closure of Actions from LIMS.
5. Configuration.

1.0   Automatic Login of Samples into LIMS

LabLite CS can be configured to automatically login the appropriate samples into the LIMS system when certain actions are added to complaints.  This feature is completely configurable in the LIMS by any one who has administrative rights

In the following example we will create a complaint that will have 4 actions assigned to it.  The 4 actions will be:

Flush Lines,

Get Sample Rusty,

Get Sample Smell,

Get Sample Taste

Currently the system is configured to only do automatic login of samples into the LIMS for 2 of these actions:  ‘Get Sample Rusty’ and ‘Get Sample Taste’.  The ‘Get Sample Smell’ action has not been configured for auto-login yet, but will be in a later example.  In a real world application, the ‘Flush Lines’ action would obviously never be configured for auto-login just by the nature of that type of action.  Notice below that the 4 actions discussed have been selected and attached to the complaint:

Here is what the actions look like in the grid on the actions tab:

Note: The grid settings have been temporarily altered to illustrate the fields that are pertinent to this discussion.  You will see that all 4 actions are open, there are as of yet no closing comments, and of course the end dates and end times are null. 

Now we will take a look at the LIMS system.  Below is a search for the current day in the Client Status module:

Notice that in the illustration directly above that only actions CSR-0028-2 (Get Sample Rusty) and CSR-0028-4 (Get Sample Taste) had associated samples that were automatically logged into the LIMS.  Again, this is because only those two action types have been configured in the LIMS to automatically login samples.  The other two, CSR-0028-1 (Flush Lines) and CSR-0028-3 (Get Sample Smell) have no association with the LIMS.

1.1 Availability of Action Numbers in LIMS

In this section we will illustrate the availability of the LabLite CS Action Numbers in the LIMS system.  The following illustration is a repeat of the one in the previous section:

Notice that in the left most column that the ‘Action Number’ is displayed.  This is the ‘Action Number’ generated by LabLite CS. 

1.2 Ability to do searches on both Action and Complaint numbers

The Client Status module can be enhanced to allow searches on both LabLite CS Complaint numbers and LabLite CS Action numbers.

To search on either of these criteria, simply click the property dropdown to the right of the screen:

First we will select the ‘Action Number’ option and then we will enter the value ‘CSR-0028-2’, after which we will click the Enter key:

As you can see, only the one action number is returned.  Conversely we could enter the value ‘CSR-0028-4’ and then we would retrieve the sibling action record:

While this feature is great for a single Action Number, it may become impractical, in a real world implementation, for those complaints that have several auto-login sample actions attached to them.  For these scenarios we can also do searches for the entire Complaint number as illustrated below:

As you can see, both Action numbers are returned from the search.

1.3 Automated closure of Actions from LIMS

When all results for a specific ‘Action’ have been completed in the LIMS then the corresponding Action will automatically be closed in the Customer Service application.  First we must select an action (sample) from the Client Status screen:

Then we need to ‘drag’ it into the Lab Notebook module:

Then we need to add results to all tests (in this case, 2) and then save:

Now that all tests are completed the associated action (CSR-0028-2) will automatically be closed in LabLite CS.  If we go back to the LabLite CS app we will not automatically see the changes:

This is because the current actions view needs to be refreshed.  If we click the ‘Search’ button then we will see the updates to action CSR-0028-2:

As you can see, the Action Status has been updated from ‘Open Action’ to ‘Closed Action’.  Also, the End Date and End Time fields have been updated.  Also, the ‘Closing Comments’ field has been updated to ‘[Closed by LIMS]’ as there was not an opportunity to add closing notes in person. 

1.4 Configuration

In the previous examples we saw how we could auto-generate samples for certain actions.  But what if we need to alter the rules for which actions have auto-generation of samples?  This can be accomplished both quickly and easily through LabLite SQL’s admin interface.  In LabLite, click File>>Setup>>Setup Tables.  You will see the setup tables interface:

After selecting the ‘Groups’ option you will see the following screen:

Note: Your setup table data for the ‘Groups’ table is certain to vary from what you see displayed above. In this strictly textbook example there are only 2 entries.  We will now add a third to the list by first clicking the ‘Insert’ icon:

Notice below that we now have an empty row:

Your LabLite documentation will have comprehensive steps detailed for adding groups in this table.  As you can see, however, the Action Type is selectable via a dropdown, with values furnished from the LabLite CS application.  Notice, too, that there is a ‘(None)’ option.  This would only be for when you are creating a group that has no connection to LabLite CS. 

You must first create a record and then save it before you can attach analytes to it:

Now that the new group is created you must click any cell in the grid for that record, and then right click anywhere off of that cell to see the following popup menu:

Click ‘Analytes’.  Then you will see this interface:

In this example we will select Silver, Aluminum and Arsenic:

Finally we will select ‘Done’.  Then we will save our changes for our group, and then we are done.  To see the impact of our changes we will need to create a brand new complaint:

Notice that this complaint has all of the same actions as our other complaint detailed above in the section:

1.0 Automatic Login of Samples into LIMS.  What is different however is when we look at this complaint in LabLite SQL  (LIMS):

  

Notice that the previous Complaint, CSR-0028, is unchanged, and only has two actions that have been logged in.  That is because there were only 2 actions configured in LabLite when these actions were created. Now that we added a third group with an associated action, and then created the same list of four actions, the LIMS program was able to login a sample for the newly created group-action record, ‘New Test Group’.  Now let’s pull it into the Lab Notebook:

As you can see, our three analytes that were selected for our new grouping are present: Silver, Aluminum and Arsenic.  We can now follow all the steps outlined above in previous sections to add results and automatically close these actions directly through the LIMS system.

Communicating between LIMS and Process Control

If you have a LIMS software application and also a Process Control software application, can they communicate with each other for the sake of reporting and data collation?

Granted  a LIMS is very different type of software app from a Process control app, however many institutions rely on both. We are going to discuss this topic in quite some detail.

LIMS solutions often need to handle the import of data from external sources, like a custom written instrument interface file. This file might be in one of several formats: .XLS, .XLSX, .CSV or even plain .TXT (ascii). The LIMS may even offer to export data for the sake of a separate bookkeeping app, like producing a fully formatted .IIF file that needs to send invoice data over to Quick Books.

On the other hand, even the simplest Process Control app may need to import hordes of data from a SCADA interface.

In either case, both apps are usually robust enough to handle the producing of export files and/or the consuming of import files.

That being said, if your organization has both a professional LIMS package and a professional Process Control program, do they understand how to talk to each other?  They would if they came from the same software vendor.

Does your current LIMS software vendor also offer an OTF affordable Process Control software app? For that matter do they offer affordable LIMS software? If you are in the market right now for either a Process Control app or a LIMS, or both, shouldn't you investigate a complete solution for a single source? That is the only way to absolutely guarantee a successful integration between the two intiatives, especially when it comes to producing single reports that must read information from both sources.

It all boils down to how your lab will need to track the results of different items. Certain analytes may need to be tracked all across town, via samplers, like pH, temp etc, for water distribution points. many more (non field) tests will need to be done in the lab for the same samples. The water treatment results are a different story all together and usually need to be tracked (partly via SCADA interfacing, perhaps) in a nice juicy Process Control database. This is a totally different app, with possibly totally different folks doing the data entry, etc.

At some point however, like at the end of the month, you may need to take huge chunks of data from both programs and combine them into those really big reports (DMR, eDMR) that need to go to the state.

If you have to pull the data from two programs that cannot speak to each other then this must be done by hand, painfully, for hours, every month. Sound familiar? This can take some lab folks about 6 or more hours per month! Now, if you are looking for job security, and like to have that 6 hours to work on reports, I get that, but if you are looking for accuracy, productivity and automation, then you need to read on.

LIMS Imperatives - Data Backup and Recovery

This post is just to let you know about a short article on data recovery for your laboratory data. (Hopefully this is a service that you will never need!)

I personally know of a few absolute horror stories that happened to real testing labs with real data. They thought that they were doing real backups, only to find out that they were in real trouble later on.

Horror Stories - Full System Backups

One lab, in Florida, religiously did their backups every night, the way that they should have been doing. However, they never thought to test out any of the backups. Three years later they found out that the backups were not completing during the night, because an error message would pop up on the server at 2:00 AM when no one was around to notice it. Whoops!

Happily, someone finally figured this out, before a serious situation had developed. In that case they would have been dead in the water and would not have been able to recover. They would have gone under, out of business for ever.

Horror Stories - Database Backups In other cases, lab personnel who do their IT internally, as opposed to hiring third party IT professionals (this is true of most smaller labs) may do all of their backups the traditional way, by backing up the entire file system (image) which would seem to make sense most of the time. However, with and RDBMS like SQL Server it's not that simple. You really need to back up the data via a SQL Backup plan while SQL Server it is running. This is because the data files (2 per database) are all exclusively locked by SQL Server and cannot be 'copied' or 'backed up' by a normal windows type backup. Read here for best practices in setting up the best SQL backup plan strategy for your organization.

What You Can Do When All Else Fails

If that awful day comes when your computer drive fails and the data seems to be unrecoverable what can you do? What should you DO and more importantly what should you NOT do?

• NEVER put the drive in the freezer! 
• NEVER attempt to open it unless you have it sitting in a legitimate 'clean room' tank for repairing hard drives.

What should you do? There is still a glimmer of hope, but only if you work with a professional hard drive restoration outfit like Data Medics. The article can be viewed here.

In addition, if you happen to find yourself in a situation where you may need to have data recovered from a hard drive, first decide if you really need data recovery or not.