Title: Case missing shortly after being created.
Affected End Users: Property custodians, Lab analysts, Administrators. Some labs may not have property custodians. In those instances lab analysts will create initial cases within LIMS
Fielded statements: The following questions and/or statements are commonly fielded from end users.
What happened to the case I was just working on?
I tried to submit a piece of evidence to a new case and application froze. The case doesnt exist when I search by the ACN. What happened?
The LIMS-plus application lost my case, what do I do?
The LIMS-plus application is not creating and/or saving cases correctly. What is going on?
I cannot locate a specific case with an ACN. What happened to my case?
If you are a LIMS-plus System Administrator within your lab, you may have been presented with these questions by your lab analysts or property custodians. Before presenting a case scenario to illustrate this behavior, I will explain how the application creates a new case within the LIMS-plus 3.x application and how cases are treated within the database as they are being created.
Temporary Case IDs - LIMS-plus 3.x
When starting a new case, users are prompted to specify the outside agency along the corresponding Agency Case Number. The ACN is the number that a used by the outside agency referencing the case. Listed below is a screenshot of a new case being created within the LIMS-plus Application:
As you can see, even though a new case window has been opened, a temporary case id has only been assigned. In this state, the record is volatile. This means that if a user needed to back out of the case window due to an incorrect ACN number entered, he or she could select cancel. When a case is cancelled while in a temp status, the user will be prompted with the following dialog:
If an end user clicks yes, the record stored in the database is deleted. If the user clicks no, they can continue adding information to a case. We will discuss this dialog in more detail below. Clicking OK or Apply assigns a lab case number (ex. LAB-00023-2009).
The town of Mayberry, USA has a forensic lab. The forensic lab has two property evidence rooms on two separate floors. One property room (PR-DE) handles drug evidence and is located on the first floor. The second property room (PR-CSI) intakes evidence collected at a crime scene and is located on the 2nd floor. Each property room has a designated Property Custodian who intakes evidence and creates the case that will be used by a lab analyst.
Two police officers from Mayberry PD arrive at the lab. Each officer has evidence that will be submitted to each property room for the same ACN. Both officers go to the property room on the first floor (PR-DE). Detective A gives the drug evidence to the property custodian and begins a conversation with property custodian. The property custodian takes the information supplied and creates a case with evidence with a single request. The ACN for the case is CH-0000007777. The custodian is then interrupted by a telephone call and opens up another application on top of the LIMS window to assist with the caller. The LIMS application is left running in the background with the case in TMP status. The detectives leave PR-DE and head to the property room for crime scene (PR-CSI). Listed below is screenshot of the LIMS case that has been left running in the background in TEMP status
Detective A stays on the first floor to follow up on a dispatch call. Detective B goes to the Crime Scene property room located on the 2nd floor. Detective B submits the remaining evidence to the property custodian in PR-CSI. The PR-CSI custodian searches the ACN(CH-0000007777) in LIMS-plus. The property custodian sees the following message:
The second property custodian selects Yes and the TMP case is opened.
After clicking yes, the case opens up with a blank synopsis. Keep in mind that details entered by the first custodian have yet to be saved.
The PR-CSI custodian is then told by Detective B that ACN (CH-000007777) was given to previous property custodian. After hearing this information, PR-CSI custodian decides to stop entering case information and clicks cancel. The following message is displayed:
Since no information was entered, the PR-CSI custodian clicks yes to the message and then receives the following:
The PR-CSI custodian clicks NO. Detective B leaves to go downstairs.
Meanwhile, the property custodian in the drug evidence property room finishes up with the emergency phone call. The PR-DE custodian restores the LIMS window that was left open in the background. The custodian attempts to add a piece of evidence and then clicks apply. After clicking apply, the PR-DE custodian sees the “Applying New Evidence Submission” dialog box.
The PR-DE custodian notices that the application appears to have locked up and proceeds to kill the LIMS-plus process running in the Windows Task Manager. The user chalks up the application freeze to amount of windows open and decides to re-launch the LIMS-plus application. After logging back into LIMS, the PR-DE custodian searches for the case via ACN (CH-0000007777). Remember, the case was in TMP status in the background when the emergency phone call came in.
When attempting to locate the case by the ACN, the following message is displayed:
The PR-DE custodian is worried that the information that was entered earlier was lost. The LIMS Administrator at the Mayberry Lab is engaged to investigate the missing case.
What happened behind the scenes:
When a case is initially created, the ACN is the only piece of information related to the TEMP ID stored in the database. Any additional information entered to the TMP case by the PR-DE custodian would not be saved to the database until OK or APPLY are clicked. Since the case was running in the background during the emergency telephone call, the TMP case id was the only related item to the ACN when it was accessed by the PR-CSI custodian. When the PR-CSI custodian searched for the ACN, he/she clicked Yes instead of NO when the dialog box below appeared.
Keep in mind that what was being entered initially by the PR-DE custodian was being held in Random Access Memory (RAM) on the PR-DE custodians’ workstation. Since the case was not saved when information was initially being entered, the PR-CSI custodian saw a blank entry in the synopsis. The PR-CSI custodian would not have seen the synopsis details unless the case was saved by the PR-DE when starting the case. Once a TMP case is saved, a real lab case number is assigned (system generated or manually generated). Once assigned a real case number, the case can only be deleted by System Administrators via System Administration. In our scenario, the PR-DE custodian tried to save the case after it was cancelled by PR-CSI. This resulted in the application lock up and/freeze. The application was attempting to update a database record that no longer existed.
There are other factors that contributed to this situation. For example, the PR-CSI custodian was unaware that both detectives had initially submitted evidence to the PR-DE in order to start the case. Once the TMP case was accessed, he/she was informed by Detective B that a case was started for the ACN by the PR-DE custodian. This is the reason why the PR-CSI custodian backed out of the case. This can also apply to scenarios where a property custodian may mistype the ACN and access an unrelated ACN when entering a new case. The unrelated ACN may be tied to a TMP case currently in the process of being entered by another property custodian/lab analyst.
This type of scenario forces a LIMS Administrator to try to reconstruct the events leading up to the case deletion. This is not an easy task when a LIMS Administrator does not have direct access to the database and/or has limited experience in performing queries in the database. For those who have direct access to the LIMS database, a simple query can be executed against the LIMS database to see if there were in fact two people working on same TMP case. Using our case scenario, the following query shows that two different individuals were modifying the same TMP case.
Within SQL Management Studio, the following query is executed:
Notice that in the upd_type column there is an INSERT and DELETE. The insert was from when the case (TMP) was initially created by the PR-DE custodian (Username= Justicetrax\Phillipf, Session 54). The delete entry is from the PR-CSI custodian (Username= JusticeTrax\JOEB, Session 59) who accessed the temp case then cancelled case entry. The curr_val and case id have the same information (ACN =CH-0000007777, Case_id = TMP-W0NS0WSW0J). The rec_primkey is the most important aspect of the query. Since both records have the same rec_primkey, this tells us that the same record was modified by two individuals. If a new case had been opened by the PR-CSI custodian, the TMP case_id and rec_primkey would be different.
Listed below is a full history of the database changes that were made to the case_agc table for the rec_primkey associated with the TMP case id, the following query could be executed:
This type of scenario is most common when a lab has multiple property rooms. When a lab has multiple property rooms, it is highly unlikely that property custodians will be in constant communication regarding every single case being created. When a property custodian or lab analyst accesses a case with a given ACN, caution has the be exercised when they are prompted with the notice indicating a case was found with the ACN. This is a cue to the lab analyst/property custodian that a case is still being entered and is in TMP status.
When the case is still in a TMP status, the second property custodian saw the following when accessing the case via ACN
After a TMP case has been saved, a real case number is assigned. In our case scenario, the second property custodian (PR-CSI) would have seen a different message indicating that a duplicate ACN had been found. If the second custodian clicked update case, the case would not have been deleted in an event the PR-CSI custodian choose to cancel out of the case.
In the presence of multiple property rooms, it is strongly recommended that property custodians and/or lab analysts save the TMP case early in the data entry process. By doing so, a real case number is assigned to the record. Typically, this business workflow is defined by organizations prior to using the application in a production environment. Unfortunately, human error is part of our nature. It is easy to forget business workflow when in the midst of multi-tasking. Reviewing business workflow by means of conducting interval training will help prevent the scenario outlined in this article.