Home : Configuration - Source Configuration Q13618
Q13625 - INFO: Configuration - Source Configuration Q13618

You must configure the connection and query to the Source LIMS database in order for the interface to work.  

1. Click Configuration and select Source Configuration from the main menu:

 

2. Configure the Database Connection


Configuration Template - the template was specifically designed for this custom interface and is an example of MS SQL online.

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.

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. Copy and
Paste operation can then be performed for use as a .udl file or with the Hach WIMS SQL_Console.exe program. 


3. Configure the Advanced Settings. 

Import Invalid Results as Hach WIMS Comments - When importing a result if the result is not a valid value for WIMS, import the result as a result comment.  For example, the Result in LIMS is BB (for broken sample bottle) which is not a value that can be entered into WIMS (i.e. BB is not a symbol) the BB is imported as a Result Comment and the value in WIMS is left blank.  NOTE:  Will overwrite any existing result comment.

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.

Data Approval Settings - Results can be imported at certain WIMS data approval levels depending on this
setting:


Import from Source Result/Sample Status (RESULTSTATUS) Field - This setting will import the Data Approval Status based on the ResultStatus field in the Source Query. The ResultStatus field MUST map to the Data Approval Setup Status values in Hach WIMS. In Hach WIMS, select System Setup, System Tables, This Facility, Data Approval Setup to review your setup:

 


The SourceQuery MUST have a RESULTSTATUS field and MUST contain values from your setup. For example, if you want the result to be imported as "ENTERED", RESULTSTATUS must equal -1024. Therefore your SourceQuery may require a CASE statement to map LIMS Status to WIMS Data Approval Status. Example case statement in MS SQL (see examples):


RESULTSTATUS=Case WHEN RESULTS.S = 'Done' THEN 0 WHEN
RESULTS.S='Pending' THEN -512 ELSE -1024 END


Set to Variable's "Data Approval Level to Write With" Interface Setting - The imported data's data approval level will be set the the variable's setting on the interface tab.

Source Query: SQL Statement (query) that returns the LIMS Results to be imported. The SQL must return the following fields:

  • FINAL - The value (result) with qualifer to be imported. Only valid WIMS results will be imported. The general rule is if you can't enter the value in a WIMS data entry form it cannot be imported. For example E2 will not be imported if E is not a valid WIMS symbol(See Section ) . <2 (less than, a space, and then 2) cannot be imported. Invalid results can be imported to Result Comments using the "Import Invalid Results as Hach WIMS Comments" setting described above.
  • SAMPDATE - The sample/collection date. The result will be stored in WIMS on this date/time.
  • ANALYTE - The test/analyte for the result.
  • SAMPLOC - The Sample Location
  • UNITS - The result units (i.e. mg/L, %, ppb, etc...)

 

The following fields are optional and are used to import data to the WIMS additional Info fields:

  • RESULTCOMMENT
  • LAB
  • ORIGINAL_SAMPNUM
  • EXTLAB_SAMPNUM
  • SAMPLENOTE
  • SAMPLEDBY
  • SAMPNUM
  • SAMPLETYPE
  • ANALYZEDBY
  • ANALYSIS_START
  • ANALYSIS_END
  • ANALYSIS_METHOD
  • APPROVEDDATE
  • UD1
  • UD2
  • MDL
  • RL
  • ML
  • ANALYTE
  • SAMPLEDATE

You will need to use the "AS" SQL syntax to alias a field in your Table to the required field names.

Select COLLECTDATE AS SAMPDATE, SAMPLE_LOCATION AS SAMPLOC, ....

You may need to use Concat statements to combine the qualifier field with your result.

Oracle: Select CONCAT(Qualifier,Result),...

MS SQL: Select Qualifier + Result,...


 

Related Articles
No Related Articles Available.

Article Attachments
No Attachments Available.

Related External Links
No Related Links Available.
Help us improve this article...
What did you think of this article?

poor 
1
2
3
4
5
6
7
8
9
10

 excellent
Tell us why you rated the content this way. (optional)
 
Approved Comments...
No user comments available for this article.
Created on 12/20/2011 11:44 AM.
Last Modified on 6/11/2021 2:13 PM.
Last Modified by Scott Dorner.
Article has been viewed 4394 times.
Rated 4 out of 10 based on 4 votes.
Print Article
Email Article