Summary

This article covers the ability to lookup local lab ranges. 

Table of Contents

• Lookup Table
  • Purpose
  • Overview
• Property Configuration
  • LocalLabQuestions

    • Example

  • LookupFile

    • Examples of file stored in File Manager

    • Finding the Document ID

    • Examples of file stored in Trial Directory
      

    • LookupFile Definitions
      

  • RowMatch

    • Example

  • ValueMapping

    • Example

  • RowMatchExpression

    • Example

  • Friendly Error Messages
  • ColumnKey

    • Example

  • Expression

    • Generic

    • Question Specific

    • Dependency Example
      

  • Implementation Steps
  • Full Example
• Export Configuration
  • RangeDataExpression Property

    • Example

  • Add files to export
  • Export range data to custom export

Local lab ranges can be uploaded into the eClinical system as a reference file. The trial can be configured to match a row in the reference file using data points like site ID, gender, age, date of lab, etc. The Fountayn clinical research platform can then use this row as the reference ranges for the lab value being supplied. When updates need to be made to the ranges, all that is required is an update of the reference file in eClinical.

Lookup Table

Purpose

The Lookup Table dependency allows a user to configure their application to dynamically check against a list of values in a separate file. This feature is very useful when many values are present that must be checked; for example local lab normal ranges. In the local lab example, we want to ensure that the lab value for a specific patient for specific lab type is within range. We may need to ensure that the lab value is above the Normal Lower and below the Normal Upper value. The ranges may also vary by variables such as gender and may only be valid for certain time periods.

Overview

In order to use the custom Lookup Table the user must setup certain properties that tell the custom dependency (edit check) how to fire. An association must be made between the questionType and the file that is to be used for lookup. Also, criteria must be defined that must be met to “locate” the correct row of data that should be evaluated. Value mappings may need to be provided so that values in the lookup table can be translated so that they will properly be compared against the data from the record. The dependency (edit check) must be defined and finally a lookup table must exist in comma separated format (other formats will be available in the future). The lookup table file can either be uploaded to the File Manager tool of the Workgroup that owns the DataCollector Tool, or to the trial directory using Trial Manager. In the lookup file below, the hemoglobin normal lower range for females is 6 and the normal upper range is 11.5. Female patients should fall between the range of 6 and 11.5 and this range is valid between the dates of 12/Jan/2004 and 31/Jan/2004.

SiteNumber,LabName,LabTest,Gender,NormalLower,NormalUpper,Unit,StartDate,StopDate 101,St. Andrew's Hospital,hemoglobin,F,6,11.5,mmol/L,12/Jan/2004,31/Jan/2004 101,St. Andrew's Hospital,hemoglobin,M,7,12.5,mmol/L,12/Jan/2004,31/Jan/2004

Property Configuration

The list below describes the properties used for interfacing with the lookup table. The properties are given in their general form, where questionTypeId would be replaced with the questionTypeId from the DataArchitect file and columnHeader would be replaced with the column name from the lookup file.

LocalLabQuestions

This property is used to store the question types that will be checked against local lab ranges. The purpose of this property is for a quick check if a question has local lab instead of going through all local lab properties to find the question.

Example

LookupFile

This property is used to locate the local lab file. Local lab files can be a file stored in file manager or a file stored in the trial directory. The value for this property can be either a documentID or a file name. It is treated as a documentID first, if there is no document loaded, it is treated as file name.

Examples of file stored in File Manager

Note: The ‘Value’ in the table below are example values. The ‘Value’ is the document ID within files manager for the specified environment (Secure, Approve, Design, and Test). Refer to this section on how to acquire this ID.

Finding the Document ID

Note: Document IDs are unique to the environment that the LookupFile applies to. Secure, Approve, Design, and Test will all have a different document ID.

1. Login to the environment that you need to grab the document ID from.
2. Navigate to Files manager by hovering over your name at the top right and selecting ‘Files’.
3. On the left hand side of the files manager under the navigation tree expand ‘My Studies’ or ‘My Workgroups’ and the trial you are grabbing the document ID from.
4. Select the folder underneath that trial that contains the local lab file (LookupFile).
5. Right click the local lab file (LookupFile) that is within that folder and select ‘Copy Link Location’.
6. Paste the link into Notepad or a Word doc and pull out the document ID.

Examples of file stored in Trial Directory

Note: The ‘Value’ in the table below are example values. The ‘Value’ is the name of the file that will be uploaded through trial manager.

LookupFile Definitions

Note: ###### is the six digit tool ID that is found within Trial Manager. XXXXX is the five digit app ID that is found within Trial Manager

• Staging: Staging includes Approve, Design, and Test environments.
• Production: Production includes the Secure environment.
• Tool: The tool ID of the site for production or staging within Trial Manager.
• App: The application ID of the production or staging environments within Trial Manager.
• LookupFileStagingTool######: The tool ID of the site within staging that the LookupFile applies to.
• LookupFileStagingAppXXXXX: The application ID of the specified staging environment that the LookupFile applies to.
• LookupFileProductionTool######: The tool ID of the site within production that the LookupFile applies to.
• LookupFileProductionAppXXXXX: The application ID of the production environment that the LookupFile applies to.

RowMatch

This property is the first round key criteria used to identify a row in the local lab file. The format is questionAlias 1||column name 1::question alias 2|| column name 2. Each question value identified in this property has to match the corresponding value from the local lab file with the specified column name.

Example

ValueMapping

This property is used only if the values of the questions specified in the RowMatch property cannot be directly mapped to the values in the local lab file.

For example if labName is a choice question, the possible values for labName are 1, 2, 3, 4, but the possible values in the lab file for Lab Name are National Lab, Chicago Lab, TX Lab, NY Lab, we can use ValueMapping property in this case.

Example

RowMatchExpression

Once a row is identified by RowMatch property, it is going to use the RowMatchExpression property to evaluate the row again.

Note: the question specific RowMatchExpression and general RowMatchExpression have a relationship of AND, all other properties have OR relationship.

In the RowMatchExpression the variable names from the local lab range data file do not have quotes, everything else (variable names from the CRF, logical operators, etc.) contain quotes. A plus sign ‘+’ links the elements of the expression.

Example

RowMatchExpression date format considerations:

• Local lab files must either use the dd/MMM/yyyy format, no matter what format is being used in the trial/question or the RowMatchExpression must contain the dateFormat annotation as follows:
  

‘type == ‘LBb’ AND ({dateformat=dd-MMM-yyyy}’ + startDt + ‘ <= lbbdat AND {dateformat=dd-MMM-yyyy}' + endDt + ' >= lbbdat)’

A RowMatchExpression can be written to allow for a match if the endDt lab range is intentionally left blank, for a logic of type:

startDt + ‘ <= lbsdat AND (lbsdat <= endDt OR endDt == ”)

where the lab range data file dates are: startDt, endDt; and eCRF lab date is: lbsdat
The expression syntax is:
startDt + ‘ <= lbsdat AND (lbsdat <= ‘ + endDt + ‘ OR ” + endDt + ” == ”)’

A RowMatchExpression can be written to automatically match to a site Id based on the enrollment site without the need for a lab facility or site number to be present on the lab form or anywhere on the CRF.

The RowMatchExpression is:
sitenum + ‘ == CALLSCRIPT{getSiteID}’
The script (here comparing to the first two digits of the lab name) is:

<script scriptId="getSiteID"> 
    <body> 
        <s:substr> 
            <s:toolName /> 
            <s:number value="0"/> 
            <s:number value="2"/> 
        </s:substr> 
    </body> 
</script>

A RowMathExpression can be written to automatically match to the age at time of lab test.
The RowMatchExpression is:
ageLo + ‘ <= CALLSCRIPT{getAgeInYears} AND CALLSCRIPT{getAgeInYears} <= ' + ageHi

<script scriptId=’getAgeInYears’ >
   <body>
       <s:if>
           <s:and>
               <s:isSet>
                   <s:question>
                       <s:question path=’/visit_Xv/dm_Xv.brthdat’ type=’question’ />
                   </s:question>
               </s:isSet>
               <s:isSet>
                   <s:question>
                       <s:question path=’.lbdat’ type=’question’ />
                   </s:question>
               </s:isSet>
           </s:and>
           <s:dateDiff unit=”year” method=”integer”>
               <s:from>
                   <s:datePath path=”/visit_Xv/dm_Xv.brthdat” />
               </s:from>
               <s:to>
                   <s:datePath path=”.lbdat” />
               </s:to>
           </s:dateDiff>
       </s:if>
   </body>
   <target action=”update” shouldReconcile=”true” typeId=”lbdat” when=”preprocess” />
</script>

Friendly Error Messages

A user friendly error message can be defined with the following property:

The error message is a general error message and can be seen on the eCRF if something happened when finding the local lab file.

Similarly, other error messages can be defined too:

Error messages are optional. If no error message defined on eCRF, ‘Hint’ will show the default message if something wrong.

ColumnKey

This property is used to identify the column header in the reference file that contains the lab test name. QuestionKey is used with ColumnKey. ColumnKey is a statement of a column header variable, without any expression and QuestionKey specifies which EDC question will match with the values within the column.

Example

Expression

It is used to evaluate the custom dependency called LookupTable.

In the Expression the variable names from the local lab range data file do not have quotes, everything else (variable names from the CRF, logical operators, etc.) contain quotes. A plus sign ‘+’ links the elements of the expression.

Generic

Question Specific

Dependency Example

Implementation Steps

1. Configure Properties – Open your specific DataArchitect spreadsheet and define the properties for each questionType that needs checks against the lookup table. Use the information above to set these properties. The value of the LookupFile property may need to be set after steps three and four depending on whether it currently exists.
2. Configure Dependencies – Add dependencies using the example above or refer to the Trial Configuration Guide for general information on dependencies (edit checks).
3. Obtain Lookup File – Either obtain the lookup file from the client or create it. Ensure that it is of the proper format.
   • Upload Lookup File (File Manager) – Login into the system and manage the Workgroup that owns the DataCollector tool. Upload the file into File Manager and take note of the file id by "mousing-over" the file name in the main list after is uploaded. The id will be visible in the bottom of your browser window.
   • Upload Lookup File (Trial Directory) – Upload the file as a “Data File” into Trial Manager. Make note of the exact name of the file being uploaded.

Note: Publishing the file pushes the new file to the design server. Committing the file creates a new version that can be flagged and loaded to other environments just like DataArchitect versions.

Mid-Study Change Tips for Updated Local Lab File:

• Uploading the new lab ranges makes the file and data available.
• The Display of the lab ranges on the eCRF are pulled from new lab file automatically when a page is displayed (no post production utility is needed).
• If labrange dependencies exist (lab lookup), Reconcile without Process Assignments should be executed.
• If labrange assignments exist (high/low flags), Reconcile with Process Assignments should be executed.

Full Example

Export Configuration

RangeDataExpression Property

Used to identify what data need to be show on the eCRF, form PDF, and PDF export. This is configured on the App Properties worksheet. The viewLocalLab screen permission also has to be defined per role.

Example

Add files to export

It is possible to include the lab file in each standard export (Text, Excel and SAS). You will need to add a writer parameter to the export configuration.

JSP files can’t be included and simply ignored. Any file that can’t be found in the trial directory is also ignored.

Export range data to custom export

A new instruction handler has been added to the extensible custom formatter. The instruction name is localLab, question alias has to be specified in the question Id column and the column name of the local lab file has to be specified in the value column in the custom export portion of export worksheet.

Need more help?

Please visit the Fountayn Contact Information page.