Supported variable configurations for the Hach WIMS Direct Server-Side Interface to Custom Q13445 StarLIMS

To configure a variable to hold values from the LIMS, select Edit/View Variables in the client and select the Interface tab.

Then click Interface To radio button. The drop down box next to the option is now enabled, click the drop down arrow and choose the LIMS interface name (name given when configuring the interface).

Each variable can be configured to have data imported either for LIMS, PROCESS, MANUAL, TRUCKIN or TRUCKOUT data.

Each type of configuration is explained below

LIMS data

For LIMS data, a typical variable looks like this:

The TAG name is filled in as LIMS:TEMP:NWQ_EFF. The name consists of three parts, each delimited by the default delimiter (":")

1. LIMS - identifies the variable as tracking LIMS data
2. ANALYTE (TEMP) - this is the analyte. In the source database, this is the value that will be in the "ANALYTE" column of the "RESULTS" table
3. SAMPLE (NWQ-EFF) - this is the sample name. In the source database, this is the value of the "SAMPNAME" column of the "ORDERS" table.
4. COMP/GRAB - Optional field to look for general or specific composite or grab values in ORDERS.COMPTYPE field
• 1 character means LIKE or similar, adds to query: "and ORDERS.COMPTYPE LIKE '%G%'" if a 'G' is added like: PROC:SSV-05:NWQ-ML 1:G
• 2 or more characters means exact match, adds to query: "and ORDERS.COMPTYPE = 'COMP1'" if 'COMP1' is added like: PROC:SSV-05:NWQ-ML 1:COMP1

In other words; to specify the COMPTYPE in this variable, simply add the value to the end. So in our example, if we only wanted values that had a CompType of 24C, we would enter: LIMS:TEMP:NWQ_EFF:24C. If only one (1) character is entered (i.e., "G") then the search will be values LIKE that character. If more than one (1) character is entered (i.e., "3C") then the search will be for an exact match.

With the above values filled in, and taking the values for "Schema owner" from the "Advanced Source Configuration" described here, the query to get the data will look like this:

SELECT QUAL, FINALQ,NUMRES,NUMTEXT, RESULTS.COMMENTS FROM LIMSSUPER.ORDERS INNER JOIN LIMSSUPER.RESULTS ON ORDERS.ORDNO = RESULTS.ORDNO WHERE ORDERS.SAMPNAM = 'NWQ-EFF' AND ANALYTE = 'TEMP' AND ORDERS.SAMPDATE >= TO_DATE('01-Mar-2011 12:00:00 AM','dd-Mon-yyyy HH:MI:SS AM') and ORDERS.SAMPDATE < TO_DATE('02-Mar-2011 12:00:00 AM','dd-Mon-yyyy HH:MI:SS AM') and ORDERS.DELFLAG IS NULL and RESULTS.SA = 'Approved'  ORDER BY SAMPDATE ASC

The cross-reference parts are highlighted. Note that for LIMS data, we're only interested in data that has been approved.

Process Data

In this case, the tag name is PROC:SSV-05:NWQ-ML 1. The various parts mean:

1. PROC - the variable is tracking PROCESS data
2. ANALYTE (SSV-05) - the analyte (same as in the LIMS example above)
3. SAMPLE (NWQ-ML 1) - the sample (same as in the LIMS example above)
4. COMP/GRAB - Optional field to look for general or specific composite or grab values in ORDERS.COMPTYPE field
• 1 character means LIKE or similar, adds to query: "and ORDERS.COMPTYPE LIKE '%G%'" if a 'G' is added like: PROC:SSV-05:NWQ-ML 1:G
• 2 or more characters means exact match, adds to query: "and ORDERS.COMPTYPE = '24C'" if '24C' is added like: PROC:SSV-05:NWQ-ML 1:24C

In other words; to specify the COMPTYPE in this variable, simply add the value to the end. So in our example, if we only wanted values that had a CompType of 24C, we would enter: PROC:SSV-05:NWQ-ML 1:24C. If only one (1) character is entered (i.e., "G") then the search will be values LIKE that character. If more than one (1) character is entered (i.e., "3C") then the search will be for an exact match.

LIMS and PROCESS are identical with one exception - the query that gets PROCESS data ignores the 'Approved' status. With the tag setup as in the example, it will result in the following query to the LIMS database:

SELECT QUAL, FINALQ,NUMRES,NUMTEXT, RESULTS.COMMENTS FROM LIMSSUPER.ORDERS INNER JOIN LIMSSUPER.RESULTS ON ORDERS.ORDNO = RESULTS.ORDNO WHERE ORDERS.SAMPNAM = 'NWQ-ML 1' AND ANALYTE = 'SSV-05' AND ORDERS.SAMPDATE >= TO_DATE('01-Mar-2011 12:00:00 AM','dd-Mon-yyyy HH:MI:SS AM') and ORDERS.SAMPDATE < TO_DATE('02-Mar-2011 12:00:00 AM','dd-Mon-yyyy HH:MI:SS AM') and ORDERS.DELFLAG IS NULL  ORDER BY SAMPDATE ASC

Note that we ignore the "Approved" flag.

Manual data

In the above example, the tag name is MANUAL:MANUAL_NWQ2:POLY_TOTAL. The parts in the tag name mean:

1. MANUAL - this variable is tracking MANUAL data
2. TABLE (MANUAL_NWQ2) - identifies the table we're getting our data from
3. COLUMN (POLY_TOTAL) - the column in the MANUAL_NWQ2 table that identifies the value we want

The query for the above variable would look like this:

SELECT DATE1,"POLY_TOTAL" FROM LIMSSUPER.MANUAL_NWQ2 WHERE DATE1 >= TO_DATE('01-Mar-2011 12:00:00 AM','dd-Mon-yyyy HH:MI:SS AM') AND DATE1 <= TO_DATE('02-Mar-2011 12:00:00 AM','dd-Mon-yyyy HH:MI:SS AM') AND DELFLAG IS NULL AND "POLY_TOTAL" IS NOT NULL ORDER BY DATE1

Note that "DATE1" is taken from the Advanced Configuration as explained here

Truck-In Data

The tag in the above example is TRUCKIN:Leachate:FEV. The individual parts mean:

1. TRUCKIN - identifies this as a variable that tracks TRUCKIN data
2. PROJECT (Leachate) - identifies the project name
3. PREFIX (FEV) - This is the prefix for Sample Name (-TRUCK) as shown in query below (highlighted: '%FEV-TRUCK%')

The following query will be used:

SELECT SAMPDATE,TRUCK_CAPACITY FROM LIMSSUPER.ORDERS WHERE SAMPDATE >= TO_DATE('01-Jan-2011 12:00:00 AM','dd-Mon-yyyy HH:MI:SS AM') AND SAMPDATE < TO_DATE('02-Jan-2011 12:00:00 AM','dd-Mon-yyyy HH:MI:SS AM') AND Delflag is null AND Project LIKE '%Leachate%' AND Sampnam LIKE '%FEV-TRUCK%' AND TRUCK_CAPACITY IS NOT NULL order by SAMPDATE

Note that the value we're importing - TRUCK_CAPACITY, the Sample Name - FEV_TRUCK, the Date column - SAMPDATE, and the Table name - ORDERS - are all specified in the interface Advanced Configuration as explained here

Truck-Out Data

For Truck-Out you can refer to the same image used for Truck-In.

The tag in the above example is TRUCKOUT:LIMS:NM:WEIGHT2. The individual parts mean:

1. TRUCKOUT - identifies this as a variable that tracks TRUCKOUT data
2. PROJNAME (LIMS) - is the value looked for in the ProjName field
3. DEST (NM) - is the value looked for in the Dest field
4. VALUE (WEIGHT2) - defines the value field
5. PLANT (FEV) - is a value looked for in the Plant Field

The following query will be used: