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