The source configuration screen for the Hach WIMS Direct Server-Side Interface to Perkin Elmer Labworks LIMS is used to define the connection to the LIMS database and set options for how data is imported.

In order to configure source connection from the interface, click Configuration and select Source Configuration.

The next screen will display parameters on two tabs. The first is Database Connection:

• Configuration Template - two possible templates are included in Labworks interface; one for MS SQL and one for User Defined. These will fill in the most common values for the database connection.
  
• DBMS Type - Select the database system type from one of these:
  
• Oracle
• Microsoft SQL Server
  
  
• OLE DB Provider - this will be filled in with Configuration Template and DBMS Type, and can be modified if needed.
  
• Data Source - this field specifies, in loose terms, the "Database/Datasource server name" where your source database system is configured to store data.
  
• Database Name - name of the database instance.
  
• Username - The username you want to use to connect to the database. This user should have permission to access the database and tables that contain data.
  
• Password - The password associated with the Username and is used to connect to the database.
  

• Advanced Configuration - these permit adjustment of the command and connection timeout.

• Connection Timeout in Seconds - how many seconds the program should wait when establishing a connection to the source database server.
  
• Command Timeout in Seconds - specifies how many seconds the program should wait for a query to complete before aborting the operation.
  
• View Applied Connection String button will expose the currently Applied connection string to source (must click Apply Settings, but do not have to Save Settings). Copy and Paste operation can then be performed.
  
• Test Connection button will test the Applied connection string (must click Apply Settings, but do not have to Save Settings).

The second tab is Advanced Settings:

• RESULT.RAWRESULT - Allow user to enable this field to import into an additional info field in Hach WIMS Client. NOTE: The field must exist in the Labworks database. If this field exists, the RAWRESULT can be used to import into Hach WIMS Client by setting the Variable's Which Result Field to Import From setting in View/Edit Variables.
  
  
  
• RESULT.PQL - Allow user to enable this field to import into an additional info field in Hach WIMS Client. NOTE: The field must exist in the Labworks database.
  
• Use As Of Date - Allows users to specify the AS OF Date or Collection Date from a different field in either the SAMPLE or RESULT tables. The interface expects the field to be a date field, and the default is SAMPLE.COLDATE. If the Field Name is left blank, the interface will use the default setting.
  

• Verify result validation type - Sets which field to use when the Variable's Import Unvalidated Results field is set to NO.  If Import Unvalidated Results is set to YES or left blank the interface will run the Analysis Validation Query but will not check the VALIDATION (ANALYSIS.AVALUSER) field. Options are:

Sample Validation -  We want records where the sample has been validated (not just individual results).  Labworks sets the Sample.VALUSER when the Sample is verified.  Depending on your version of Labworks and your labs particular workflow you can use either the SAMPLE.VALDATE or the RESULT.RMODDATE to detect modified records for import.  Example query to retrieve records from the LIMS:

Use VALDATE:

MS SQL Server Example:

SELECT SAMPLE.SAMPNO, SAMPLE.COLDATE, RESULT.RESULT, RESULT.RESULT2,RESULT.RLTUNIT as UNITS, RESULT.QUALIFY,SAMPLE.LOCCODE, RESULT.ACODE, RESULT.ANALYTE as ANALYTE, RESULT.RMODDATE, SAMPLE.VALUSER as VALIDATION from DBO.SAMPLE, DBO.RESULT where SAMPLE.SAMPNO=RESULT.SAMPNO and SAMPLE.VALDATE >= '2012-02-28 00:00:00' and SAMPLE.VALDATE < '2012-02-29 00:00:00' ORDER BY SAMPLE.SAMPNO, SAMPLE.VALDATE

Oracle Example:

SELECT SAMPLE.SAMPNO, SAMPLE.COLDATE, RESULT.RESULT, RESULT.RESULT2,ANL_LIST.AUNIT as UNITS, RESULT.QUALIFY,SAMPLE.LOCCODE, RESULT.ACODE, RESULT.ANALYTE as ANALYTE, RESULT.RMODDATE, SAMPLE.VALUSER as VALIDATION from LABWORKS.SAMPLE, LABWORKS.RESULT, LABWORKS.ANL_LIST where SAMPLE.SAMPNO=RESULT.SAMPNO and RESULT.ACODE=ANL_LIST.ACODE and SAMPLE.VALDATE >=  TO_DATE('04-Mar-2012 12:00:00 AM', 'dd-Mon-yyyy HH:MI:SS AM')  and SAMPLE.VALDATE <  TO_DATE('05-Mar-2012 12:00:00 AM', 'dd-Mon-yyyy HH:MI:SS AM')  ORDER BY SAMPLE.SAMPNO, SAMPLE.VALDATE

Use RMODDATE:

MS SQL Server Example:

SELECT SAMPLE.SAMPNO, SAMPLE.COLDATE, RESULT.RESULT, RESULT.RESULT2,RESULT.RLTUNIT as UNITS, RESULT.QUALIFY,SAMPLE.LOCCODE, RESULT.ACODE, RESULT.ANALYTE as ANALYTE, RESULT.RMODDATE, SAMPLE.VALUSER as VALIDATION from DBO.SAMPLE, DBO.RESULT where SAMPLE.SAMPNO=RESULT.SAMPNO and RESULT.RMODDATE >= '2012-02-28 00:00:00' and RESULT.RMODDATE < '2012-02-29 00:00:00' ORDER BY SAMPLE.SAMPNO, RESULT.RMODDATE

Oracle Example:

SELECT SAMPLE.SAMPNO, SAMPLE.COLDATE, RESULT.RESULT, RESULT.RESULT2,ANL_LIST.AUNIT as UNITS, RESULT.QUALIFY,SAMPLE.LOCCODE, RESULT.ACODE, RESULT.ANALYTE as ANALYTE, RESULT.RMODDATE, SAMPLE.VALUSER as VALIDATION from LABWORKS.SAMPLE, LABWORKS.RESULT, LABWORKS.ANL_LIST where SAMPLE.SAMPNO=RESULT.SAMPNO and RESULT.ACODE=ANL_LIST.ACODE and RESULT.RMODDATE >=  TO_DATE('04-Mar-2012 12:00:00 AM', 'dd-Mon-yyyy HH:MI:SS AM')  and RESULT.RMODDATE <  TO_DATE('05-Mar-2012 12:00:00 AM', 'dd-Mon-yyyy HH:MI:SS AM')  ORDER BY SAMPLE.SAMPNO, RESULT.RMODDATE

Analysis Validation is selected (or Import Unvalidated Results is YES or blank), then ANALYSIS.AVALUSER field will be used to verify validation. 

Use AVALDATE:

MS SQL Server Example:

SELECT SAMPLE.SAMPNO, SAMPLE.COLDATE, RESULT.RESULT, RESULT.RESULT2,RESULT.RLTUNIT as UNITS, RESULT.QUALIFY,SAMPLE.LOCCODE, RESULT.ACODE, RESULT.ANALYTE as ANALYTE, ANALYSIS.AVALDATE,ANALYSIS.AVALUSER as VALIDATION from SAMPLE, RESULT, ANALYSIS where (SAMPLE.SAMPNO=RESULT.SAMPNO and SAMPLE.SAMPNO=ANALYSIS.SAMPNO and ANALYSIS.ACODE=RESULT.ACODE) and ANALYSIS.AVALDATE >= '2012-02-28 00:00:00' and ANALYSIS.AVALDATE < '2012-02-29 00:00:00' ORDER BY SAMPLE.SAMPNO, ANALYSIS.AVALDATE

Oracle Example:

SELECT SAMPLE.SAMPNO, SAMPLE.COLDATE, RESULT.RESULT, RESULT.RESULT2,ANL_LIST.AUNIT as UNITS, RESULT.QUALIFY,SAMPLE.LOCCODE, RESULT.ACODE, RESULT.ANALYTE as ANALYTE, ANALYSIS.AVALDATE,ANALYSIS.AVALUSER as VALIDATION from LABWORKS.SAMPLE, LABWORKS.RESULT, LABWORKS.ANALYSIS, LABWORKS.ANL_LIST where (SAMPLE.SAMPNO=RESULT.SAMPNO and RESULT.ACODE=ANL_LIST.ACODE and SAMPLE.SAMPNO=ANALYSIS.SAMPNO and ANALYSIS.ACODE=RESULT.ACODE) and ANALYSIS.AVALDATE >=  TO_DATE('04-Mar-2012 12:00:00 AM', 'dd-Mon-yyyy HH:MI:SS AM')  and ANALYSIS.AVALDATE <  TO_DATE('05-Mar-2012 12:00:00 AM', 'dd-Mon-yyyy HH:MI:SS AM')  ORDER BY SAMPLE.SAMPNO, ANALYSIS.AVALDATE

Use RMODDATE:

MS SQL Server Example:

SELECT SAMPLE.SAMPNO, SAMPLE.COLDATE, RESULT.RESULT, RESULT.RESULT2,RESULT.RLTUNIT as UNITS, RESULT.QUALIFY,SAMPLE.LOCCODE, RESULT.ACODE, RESULT.ANALYTE as ANALYTE, RESULT.RMODDATE,ANALYSIS.AVALUSER as VALIDATION from SAMPLE, RESULT, ANALYSIS where (SAMPLE.SAMPNO=RESULT.SAMPNO and SAMPLE.SAMPNO=ANALYSIS.SAMPNO and ANALYSIS.ACODE=RESULT.ACODE) and RESULT.RMODDATE >= '2012-02-28 00:00:00' and RESULT.RMODDATE < '2012-02-29 00:00:00' ORDER BY SAMPLE.SAMPNO, RESULT.RMODDATE

Oracle Example:

SELECT SAMPLE.SAMPNO, SAMPLE.COLDATE, RESULT.RESULT, RESULT.RESULT2,ANL_LIST.AUNIT as UNITS, RESULT.QUALIFY,SAMPLE.LOCCODE, RESULT.ACODE, RESULT.ANALYTE as ANALYTE, RESULT.RMODDATE,ANALYSIS.AVALUSER as VALIDATION from LABWORKS.SAMPLE, LABWORKS.RESULT, LABWORKS.ANALYSIS, LABWORKS.ANL_LIST where (SAMPLE.SAMPNO=RESULT.SAMPNO and RESULT.ACODE=ANL_LIST.ACODE and SAMPLE.SAMPNO=ANALYSIS.SAMPNO and ANALYSIS.ACODE=RESULT.ACODE) and RESULT.RMODDATE >=  TO_DATE('04-Mar-2012 12:00:00 AM', 'dd-Mon-yyyy HH:MI:SS AM')  and RESULT.RMODDATE <  TO_DATE('05-Mar-2012 12:00:00 AM', 'dd-Mon-yyyy HH:MI:SS AM')  ORDER BY SAMPLE.SAMPNO, RESULT.RMODDATE

 

• Source Tables Owner - Owner or schema (Oracle) of the Labworks database tables.

• Units Table - Sets where to get the units for the result from. Set to RESULT to use the RESULT.UNITS field, or set to ANL_LIST to use the ANL_LIST.UNITS field. The units are used when Units Conversion option is enabled.
  
• Ignore Case when matching between Source Identifiers and WIMS Variable Cross References - enable this setting when you need to ignore case during matching to WIMS variable cross references.
  

• Import Hach WIMS Comments - Enables or disables importing of comments into Hach WIMS Result Comments.  Comments can come from two sources,  the Labworks COMMENTS.COMMENTS field and/or invalid results.  For example, we combine the Labworks RESULT.QUALIFY and RESULT.RESULT (or RAWRESULT depending) and import that string as if we are entering that data into WIMS. If value to be import is <2 and “<” is a valid symbol (data qualifier) in WIMS the result is imported. If the value to be imported is E2 and “E” is NOT a valid symbol in WIMS no value is imported and a result comment of E2 is created.  If a Labworks Comment exists for the Sample/Analyte in the COMMENTS.COMMENTS field and the result is invalid the WIMS Result comment will be set to the invalid result followed by ";" followed by the Labworks Comment (e.g. "E2;Sample is invalid as the hold time was exceeded")
  
  
  

When the Use Custom SQL Query button is clicked, the following appears:

• Reset Default Query button resets the query back to the default query sample:

Select SAMPLE.SAMPNO as SAMPNO, SAMPLE.COLDATE as COLDATE, RESULT.RESULT as RESULT, RESULT.RLTUNIT as UNITS, RESULT.QUALIFY as QUALIFY, SAMPLE.LOCCODE as LOCCODE, RESULT.ACODE as ACODE, RESULT.ANALYTE as ANALYTE, ANALYSIS.AVALDATE as RMODDATE, ANALYSIS.AVALUSER as VALIDATION FROM SAMPLE, RESULT, ANALYSIS WHERE (SAMPLE.SAMPNO=RESULT.SAMPNO and SAMPLE.SAMPNO=ANALYSIS.SAMPNO and ANALYSIS.ACODE=RESULT.ACODE) and SAMPLE.VALDATE>= #SD# and SAMPLE.VALDATE < #ED# order by SAMPLE.SAMPNO, SAMPLE.VALDATE

NOTE: In order to import data from the following fields into additional info fields, they must be added to your custom query:

• RESULT.MDL
• RESULT.PQL
• RESULT.StartDate
• RESULT.EndDate
• RESULT.RESULT2
• RESULT.Analyst

For example: SELECT RESULT.MDL, RESULT.PQL, RESULT.StartDate, RESULT.Analyst, SAMPLE.SAMPNO as SAMPNO, SAMPLE.COLDATE as COLDATE ......

NOTE: The RESULT.PQL field is not in all Labworks databases!

Example query when Import Hach WIMS Comments is enabled.

In order to import the Labworks comments, you MUST add a comments field into the query:

Select SAMPLE.SAMPNO as SAMPNO, COMMENTS.COMMENTS, SAMPLE.COLDATE as COLDATE, RESULT.RESULT as RESULT, RESULT.RLTUNIT as UNITS, RESULT.QUALIFY as QUALIFY, SAMPLE.LOCCODE as LOCCODE, RESULT.ACODE as ACODE, RESULT.ANALYTE as ANALYTE, ANALYSIS.AVALDATE as RMODDATE, ANALYSIS.AVALUSER as VALIDATION
FROM SAMPLE, ANALYSIS, RESULT LEFT OUTER JOIN COMMENTS ON RESULT.SAMPNO=COMMENTS.SAMPNO and RESULT.ACODE = COMMENTS.ACODE
WHERE (SAMPLE.SAMPNO=RESULT.SAMPNO and SAMPLE.SAMPNO=ANALYSIS.SAMPNO and ANALYSIS.ACODE=RESULT.ACODE)
and SAMPLE.VALDATE>= #SD# and SAMPLE.VALDATE < #ED#
order by SAMPLE.SAMPNO, SAMPLE.VALDATE

Using UserDefined1 and UserDefined2 as Additional Info fields in the Custom Query

You can map any field in the database to an Additional Info field using the UserDefined1 and UserDefined2 fields.  For example, to import data from the SAMPLE.SAMPCOL field you would add the field to your query aliased as UserDefined1:

Select SAMPLE.SAMPNO as SAMPNO, SAMPLE.SAMPCOL as USERDEFINED1, SAMPLE.COLDATE AS ...

Test button will test the query and return up to 500 records and will let you know if the query will work, or report where it will have problems.

When you have a successful SQL Query, you will see the following message:

• Hide Test Results button will hide the results window and you can continue editing the SQL Query.

NOTE: The following must have fields associated with them: RESULT, COLDATE, LOCCODE, ANALYTE, ACODE, UNITS, VALIDATION, SAMPNO, and RMODDATE. Also there must be at least one #SD# which signifies the start date parameter, and at least one #ED# which signifies the end date parameter.

The message box between Test button and Hide Test Results button will display an error if any fields are missing. For example, the following screen shows that we do not have a field identified for COLDATE:

 

RULES

Rules are used to fix results/qualifiers that are in a different format from WIMS.  Typically, it is used to replace results to match what WIMS needs.  Examples:

• The table contains a 0 (zero) for E COLI results, WIMS has a text variable that expects an "A" (Absent). The first rule will convert the zero(s) to A for Analytes equal to E COLI.
• The second rule handles values from the lab such as L 0.45 which means <0.45. Notice also the space between the L and 0.45.  To specify leading or trailing spaces use <sp>. So L 0.45 will be imported as <0.45
• The third rule will look for a " I" after the result value (0.45 I).  <mdl> in the with column tells the rule to use the MDL value for the result.
• The fourth rule will look for a " U" after the result value (0.45 U).  The < in the with column will be placed before the result value.  Result will be <0.45.

When specifying, the value "Anything" means all records will be looked at.  If you need to also replace zeros with A for Total Coliform, you would need to add the rule for Total Coliform as you would not want to change all zeros to A.