Home : Locate Saskatchewan Lab-OPR
Q14286 - INFO: Locate Saskatchewan Lab-OPR

The Locate Saskatchewan Lab-OPR option allows the user to design a report to output a data file to submit electronic sample/measurement data to Saskatchewan Environment. The eReport option supports the Lab-OPR file. See Saskatchewan Lab-OPR file Structure for more information.

 

Starting File Number: Allows users to specify the file number to be used on the next generated Saskatchewan Lab-OPR eReport. NOTE: When the report is output using Report Pac, Spread Reports, eDMR / Electronic Report File option, this cell will automatically be incremented.

Typically a user would only open the spread report to adjust the next report file number.

File name extension: Specifies the eReports file extension. The file name extension must be an uppercase “M” followed by the 3-digit lab code assigned to the lab.

The file name generated by WIMS will be in the format:

  • Date Generated in YYYYMMDD format
  • A dash "-"
  • Sequential Number
  • File name extension - Extension of "M" followed by the 3 digit lab code assigned to the lab. 

Using the above example, if the report was generated on December 25th, 2014 the filename would be:

20141225-0000001.M118

Second file generated on December 25th, 2014:

20141225-0000002.M118

Next file generated on December 26th, 2014:

20141226-0000003.M118

Get Received Date from: Sets how the Sample Header Received Date Field is populated. 

  • Sample Date/Time - Lab Cal Sample Date / Time (LC_SAMPLE.SAMPLEDTTIME) 
  • COC - The date of the first Chain of Custody record for the sample.  (LC_SAMPLECUSTODY.TRANSFERDATE)

SQL Query Where Statement: Allows advanced users to specify the SQL Where Statement, thereby changing which Lab Cal Samples are reported. Example Query with setting of TASKUD1='Y':

  • The field is entered just after the Where keyword is used in the built-in SQL statement.
  • The default where statement is: TASKUD1 = 'Y'
  • The tables opened via the from statement are:
    • LC_SAMPLE

    • LC_SAMPLETEST

    • LC_SAMPLEDEF

    • VARDESC

SELECT LC_SAMPLE.AUDITTIMESTAMP, LC_SAMPLE.SAMPLEDTTIME, LC_SAMPLE.SAMPLENUM, LC_SAMPLE.ID, LC_SAMPLE.NOTES, LC_SAMPLETYPE.NAME, LC_SAMPLEDEF.TASKUD2, LC_SAMPLEDEF.TASKUD3, LC_SAMPLEDEF.TASKUD4, LC_SAMPLEDEF.TASKUD5, LC_SAMPLEDEF.NOTES AS APPROVAL,LC_SAMPLE.NOTES FROM LC_SAMPLE , LC_SAMPLEDEF , LC_SAMPLETYPE Where TASKUD1 = 'Y' AND LC_SAMPLEDEF.ID = LC_SAMPLE.SAMPLEDEFID AND LC_SAMPLE.SAMPLETYPEID = LC_SAMPLETYPE.ID AND LC_SAMPLE.SAMPLEDTTIME >= '01/12/2012' AND LC_SAMPLE.SAMPLEDTTIME < '01/01/2013'

NOTE: Locate Saskatchewan Lab-OPR will also change the Report Type to "Saskatchewan Lab-OPR " in Report Options.

MAPPINGS

The following tables show how each field of the OPR records are populated by fields in WIMS/Lab Cal. The LAB-OPR file is comprised of 4 record types (Sample Header Record, Measurement Detail Record,Measurement Detail Comment, and Sample Header Comment Record).

 

Sample Header Record

Example Record:

S000001 20110207095000 20110209093200 118BA43503 AB05BM1422 10 1 17151 ANNUL

Field Description Mapping
Record Type Sample Header Auto set to "S"
Record Number Sequential Number of each record in the file Auto set by WIMS
Sample Number Sample Number issued by AENV (not required in Lab-OPR file) Auto Set to "" ie blank
Sample Date Date Sample was taken in YYYYMMDDHHMISS format

Lab Cal Sample Date / Time (LC_SAMPLE.SAMPLEDTTIME)

Sample End Date Not required in Lab-OPR file Auto Set to "" ie blank
Send Date Not required in Lab-OPR file Auto Set to "" ie blank
Received Date Date Sample was received by Lab.

Depends on the Get Received Date from: setting.

  • Sample Date/Time - Lab Cal Sample Date / Time (LC_SAMPLE.SAMPLEDTTIME)
  • COC - The date of the first Chain of Custody record for the sample. (LC_SAMPLECUSTODY.TRANSFERDATE)
Returned Date Not required in Lab-OPR file Auto Set to "" ie blank
Lab Code ID Code of lab where measurements are done

Lab Cal Sample Definition User Defined field 2. LC_SAMPLEDEF.TASKUD2. See Sample Setup for setting the User Defined Fields.

Lab Sample Number Sample number used internally by the lab Lab Cal Sample Number (LC_SAMPLE.SAMPLENUM)
Station No. Station number, issued by Saskatchewan Enviroment, where sample was obtained

Lab Cal Sample Definition User Defined field 3. LC_SAMPLEDEF.TASKUD3. See Sample Setup for setting the User Defined Fields.

Project No. Not required in Lab-OPR file
Agency Code Not required in Lab-OPR file
Sample Matrix Code Saskatchewan Enviroment Code to id the sample matrix.

Lab Cal Sample Definition User Defined field 4. LC_SAMPLEDEF.TASKUD4. Uses only the first 2 characters of the field, i.e. if you set UD4 to 10 - Treated, 10 will be used.

Number Caught Not required in Lab-OPR file
Number Kept Not required in Lab-OPR file
Sample Type Code

Saskatchewan Enviroment code that identifies the type of sample. Valid Values: 1 - Discrete / Grab, 19 - Calculated, 96 - Continous, 32 - Bacti Resample, 33 - Bacti Resample NOT for protocol, 35 - Sample not for compliance

Lab Cal Sample, Sample Type Setting. Uses the number in Parenthesis of the LC_SAMPLE.SAMPLETYPE Field.

Use Sample Type Setup to enter your sample types with the AENV codes in parenthesis. Example for grab:

Collection Code Not required in Lab-OPR file
Group Sample No Not required in Lab-OPR file
Sample Cross Ref For Labs, if the data is associated with a particular approval (e.g. for bacteriological data), this field will contain the approval id
associated with the measurement.

Lab Cal Sample Definition Notes. Enter the Approval as Approval=AB05BM1422

Sample Depth Not required in Lab-OPR file
Sampler ID 1 Not required in Lab-OPR file
Sampler ID 2 Not required in Lab-OPR file
Sampler ID 3 Not required in Lab-OPR file
Sample Frequency Code

Code to indicate the frequency of the sampling.

Annual = ANNUL (annual or semi annual chemistry samples)
Daily = DAILY
Monthly = MONTH (calendar month)
Weekly = WEEK (usually a 7 day week)
Weekly (except Statutory holidays) = WKGW (this typically for small groundwater operations that can have a 5 day week)

Lab Cal Sample Definition User Defined field 5. LC_SAMPLEDEF.TASKUD5:

Reading Type Not required in Lab-OPR file

 

Sample Header Comment Record

Example Record:

C     2WWTP20136198

Field Description Mapping
Record Type C Autoset to "C"
Record Number Sequential Number of each record in the file Autoset by WIMS
Lab Sample Number Sample Number used internally by the lab Lab Cal Sample Number
Comment Text Comment describing Sample Autoset to ""

 

 

 

Measurement Detail Record

Example Record:

M000004BA43503 000000002 20110210105408106221000000.00050L

Field Description Mapping
Record Type M (if standard measurement record)
B (if biological measurement record)
Always "M"
Record Number Sequential Number of each record in the file Autoset by WIMS
Lab Sample Number Sample Number used internally by the lab Lab Cal Sample Number
Measurement No. Measurement sequence number within a sample Autoset by WIMS
Project No. N/A Autoset to ""
Tissue Item No. N/A Autoset to ""
Measurement Date Date Measurement was made LC_SAMPLETEST.DATECOMPLETE
VMV Code for Measurement VMV parameter code for measurement Variable's Storetcode field. VARDESC.STORETCODE
Value Numeric Result Variable's Result for Sample/Analyte.
Flag Flag to qualify measurement value If WIMS Result is "<", report as "L". If WIMS Result is ">", report as "G". Otherwise report as blank.
Preatreatment Code N/A Autoset to ""
Sample Detect Limit Detection limit for measurement Autoset to ""
Value Type Code N/A Autoset to ""
Qualifier 1 Qualifier for measured value

Autoset to "". If reported the following list shows the valid values:

<RDL LESS THAN RELIABLE DETECTION LIMIT
>MRV VALUE IS GREATER THAN MRV BUT LESS THAN MDL
B ANALYTE FOUND IN BLANK AND SAMPLE. BLANK VALUE SUBRACTED
BNS ANALYTE FOUND IN BLANK AND SAMPLE(BLANK NOT SUBRACTED)
CG CONFLUENT GROWTH
CR CORRECTED FOR RECOVERY
DR DILUTION REQUIRED TO ANALYZE THE SAMPLE AND MDL ADJUSTED ACCORDINGLY
E ESTIMATE - LESS THAN MDL
FSE FALLS WITHIN STANDARD ERROR
HT HOLDING TIME EXCEEDED
HTA ANALYSIS TIME EXCEEDED (21 DAYS)
HTE EXTRACTION TIME EXCEEDED (7 DAYS)
I ALL CONFORMATION IONS NOT PRESENT
ISB INAPPROPRIATE SAMPLE AMOUNT OR BROKEN
ISH INSUFFICIENT HEAD SPACE
IST INAPPROPRIATE SAMPLE TEMPERATURE
M CALCULATED BY AN ALTERNATE RAW DATA SET
QIR QUALIFYING IONS PRESENT BUT FAILED THE ION RATIO LIMITS
R REPLICATE CRITERIA NOT MET
RER RERUN/RECHECK OF MEASUREMENT
SPK VARIABLE HAS BEEN SPIKED
SPNF STANDARD PROCEDURE NOT FOLLOWED
SUS VALUE IS SUSPECT
TAM TEMPORARY ALTERNATE METHOD
VER VALUE HAS BEEN VERIFIED

Qualifier 2
Qualifier 3
Qualifier 4
Qualifier 5
Qualifier 6
Qualifier 7
Missing Meas Code

Set to "" by WIMS. If reported the following list shows the valid values:

Analyzer Broken = ANB
Laboratory Error = LBE
Transposing Error = TPE
Either / Or Missing Measurement (re; Chlorine Residual) = EIO

Measurement Detail Comment Record
Example Record:
K   194WWTP20140027        M        1Suspended Solids

Field Description Mapping
Record Type K Autoset to "K"
Record Number Sequential Number of each record in the file Autoset by WIMS
Lab Sample Number Sample Number used internally by the lab Lab Cal Sample Number
Measurement Type M (if standard measurement record)
B (if biological measurement record)
Defaults to M.  If the Test Ud1 field (LC_TEST.LC_TESTUD1) is equal to B it will be reported as "B", otherwise "M" is reported.
Measurement No. Measurement sequence number within a sample Autoset by WIMS
Comment Describes Measurement The Test Name field (LC_TEST.NAME)

 

BUTTONS:

OK - Accepts the entries and places them into the Spread Report

Cancel - Closes the form without loading any changes

Restore Defaults - Resets the starting number to 000001, File Extension to M118, and the Where Statement to TASKUD1 = 'Y'

Help - Pulls up a knowledgebase article to further assist users

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/1/2014 1:46 PM.
Last Modified on 12/18/2014 3:26 PM.
Last Modified by Scott Dorner.
Article has been viewed 2795 times.
Rated 0 out of 10 based on 0 votes.
Print Article
Email Article