Collect – COC and eCOC Report

<< Click to Display Table of Contents >>

Navigation:  EQuIS Library > Reports > EarthSoft Standard Reports by Product > Standard with Collect >

Collect – COC and eCOC Report

Report Name: Collect – COC and eCOC

File Name: EarthSoft.Reports.Library.149680.dll

Dependencies: Licensed Collect Module and SPM Schema or EDGE Schema

Software: EQuIS Collect, EQuIS Enterprise

Source: EQuIS Collect install

Language: English

Output Type: ZIP file containing a PDF file and an XML file

 

Description: The Collect – COC and eCOC report returns Chain of Custody (COC) data exports based on data from Collect forms. The report can be run from EQuIS Professional, Enterprise, or the Collect mobile app (if the device is connected to the Internet). This report can be setup as an EQuIS Information Agent (EIA).

 

Installation Instructions: This report requires a licensed EQuIS Collect module, the Collect Schema, and either the SPM schema or EDGE schema applied to the database. For use, this report should be published to the database. Download the EQuIS Collect .xcopy zip from the community center, unblock the *.zip file, and extract the EarthSoft.Reports.Library.149680.dll file to the EQuIS Professional installation folder. For default installations, this directory is C:\Program Files\EarthSoft\EQuIS. The report will be available for selection once it has been published.

 

Overview:

The report uses a Microsoft Word template (DOTX) to generate a PDF output. This template must be uploaded to EQuIS with a DT_FILE.FILE_TYPE like ".dotx" to be an option in the report's Collect Word Template parameter. Use the Docs and Photos form in Professional (similar to instructions for uploading SPM COC Templates) or the Explorer Widget in Enterprise to upload this file. The report will not return any data when run without a "Chain of Custody" parameter populated.

 

The Word template can be customized to add logos, hide or show columns, and much more, as detailed later in this article. Example Collect COC Word template files are available on the EarthSoft Community Center within the "EQuIS Collect Templates" folder.

 

To enable this report to generate COCs and eCOCs in the mobile app, do the following:

1.Save a user report for the Collect – COC and eCOC report. Note: Do not set a Chain of Custody on this saved report; the COC value will be determined dynamically by Collect mobile when the report is run for a selected form.

2.Set the user report as the Mobile Report for the Collect Template.

 

The report will not return any data when manually run without a "Chain of Custody" parameter populated. When this parameter is left blank and an EIA is set to run triggered on New Data, the report will take the EBATCH from a new submission of data and use it to locate any COCs that the report could retrieve.

 

Tables: AT_SPM_SAMPLE_CHAIN_OF_CUSTODY, AT_SPM_CONTRACT_MTH_CONTAINER, DT_CHAIN_OF_CUSTODY, DT_CHAIN_OF_CUSTODY_MAG, DT_CHAIN_OF_CUSTODY_MAG_CONT, DT_FACILITY, DT_FIELD_SAMPLE, DT_RESULT, DT_SAMPLE, DT_SAMPLE_PARAMETER, DT_SPM_CONTRACT_MAG, DT_SPM_CONTRACT_MAG_MEMBERS, DT_SPM_CONTRACT_METHOD, DT_SPM_SCHEDULED_CONTAINER, DT_SPM_SCHEDULED_SAMPLE, DT_TEST, RT_ANALYTE, RT_COMPANY, RT_SPM_CONTAINER

 

Report Parameters

Export Type

Select export type. COC will produce a PDF file, eCOC will produce an XML file, and COC|eCOC will produce a ZIP file with both a PDF and XML. Defaults to COC|eCOC. See the Note below for information about EIA generated outputs.

Source of COC Data

Select either Collect or SPM.

Source of eCOC Data

Filters the Chain of Custody output based on the source selected. Set to "Collect" to only export information from Collect. Default is empty.

Search Chain of Custody By

Select the input type of COC by which to search.

Chain of Custody ID

Select one or more COC(s) to print by selecting CHAIN_OF_CUSTODY_ID

Chain of Custody

Select one or more COC(s) to print by selecting CHAIN_OF_CUSTODY

Lab

Select laboratory.

Print Latest COC Only from Multiple eBatches

Select Yes or No.

Hide Location Code

Check box to show/hide Location_Code. Default is False.

Hide Turnaround Time Standard

Check box to show/hide Turnaround_Time_Standard. Default is False.

Hide Relinquished By

Check box to show/hide Relinquished_By. Default is False.

COC Word Template

Select COC Word template.

COC ID Barcode Type

Select the type of barcode for COC (1D or QR).

Print Sample ID Barcode

Select Yes or No.

Show Individual Containers

Select Yes or No.

Analyte(s)

Select one or more analyte(s). Not applicable for SPM COC.

Single Container per Sample?

Select Yes or No.

Analysis Header

Select source for Sample Analysis header. Data will be retrieved from DT_CHAIN_OF_CUSTODY_MAG for Collect COC data and DT_SPM_CONTRACT_MAG for SPM COC data.

Sort Samples By

Select an option to sort the samples.

Sample Parameter

Select a sample parameter code to show in sample analysis table. Not applicable for SPM COC.

Subfacility(s)

Select Subfacility(s) to show in COC output.

 

Notes:

EIAs are unable to generate ZIP file pagckages and will only generate a PDF file regardless of the Export Type setting.

If report parameter “Hide Location Code” is set to True, then Location_Code under Sample node will be hidden.

 

 

Collect – eCOC Information

 

Mapping Information

 

Field Name in eCOC

Mapped Database Column

Field Type
(Max Length)

Description

COC

COC_Number

dt_chain_of_custody.chain_of_custody

Text (40)

Chain of Custody number (COC).

Facility_code

dt_facility.facility_code

Text (50)

The user-defined code of a specific facility (e.g., "Springfield").

Project_Number

dt_chain_of_custody.project_ID

Text (20)

Project number listed on the COC.

Client_Code

dt_spm_contract.contract_name

OR

dt_facility.client

Text (50)

Client code associated with the COC.

Client_Name



Must always be empty.

Site_Address

Concat(dt_facility.address_1,', ',dt_facility.city,', ',dt_facility.state,', ',dt_facility.postal_code)

Text (116)

Contains the street address of the facility.

Site_PM_Name

dt_chain_of_custody.project_manager

Text (50)

Project manager field listed on the COC.

Site_PM_Phone

dt_person.phone_number

(Linked to dt_chain_of_custody.project_manager)

Text (30)

Project manager phone number.

Site_PM_Email

dt_person.email_address

(Linked to dt_chain_of_custody.project_manager)

Text (100)

Project manager email address.

Sampler_Name

dt_chain_of_custody.sampler_1

Text (50)

Sampler name on the COC.

Sampler_Phone

dt_person.phone_number

(Linked to dt_chain_of_custody.sampler_1)

Text (30)

Sample phone number.

Sampling_Company

dt_person.company_code

Text (40)

Sampling Company Code.

Email_Report_To

dt_chain_of_custody.email_report_to

Text (100)

Person receiving the report.

Email_Invoice_To

dt_chain_of_custody.email_invoice_to

Text (100)

Person receiving the invoice.

Lab_Code

dt_chain_of_custody.lab_code

Text (40)

Lab associated with the COC.

Lab_Contact

rt_company.contact_name

Text (50)

Lab Contact.

Lab_Phone

rt_company.phone_number

Text (30)

Lab contact phone number.

Lab_Email

rt_company.email_address

Text (100)

Lab contact email address.

Quote_Code

dt_chain_of_custody.custom_field_5

Text (255)

Internal Lab value associated with the Quote Number.

Quote_Number

dt_chain_of_custody.quote_number

Text (50)

Quote Number associated with the COC.

PO_Number

dt_chain_of_custody.po_number

Text (50)

Purchase Order number provided by client.

Turnaround_Time

dt_chain_of_custody.turn_around_time

Text (50)

Lab turnaround time.

Turnaround_Time_Units

“Days”

Text (4)

Turnaround unit.

Turnaround_Time_Standard

dt_chain_of_custody.custom_field_4

Text (255)

Rush or Standard turnaround time.

Relinquished_By

dt_chain_of_custody.relinquished_by

Text (100)

The person that relinquished the COC.

Relinquished_Date

dt_chain_of_custody.relinquished_date

DateTime

Relinquished date (Date format = yyyy/MM/dd HH:mm).

Cooler_Number

dt_chain_of_custody.cooler_id

Text (10)

Cooler ID associated with a specific COC.

Cooler_Count

dt_chain_of_custody.cooler_count

Number

Total number of coolers.

Shipping_Company

dt_chain_of_custody.shipping_company

Text (40)

The company used to ship samples to Lab.

Shipping_Tracking_Number

dt_chain_of_custody.shipping_tracking_number

Text (50)

The number used to track a sample shipment.

Special_Instructions

dt_chain_of_custody.remark

Text (2000)

General instructions on the COC.

Sample

Sample_Code

dt_sample. sys_sample_code

Text (40)

The alpha-numeric code that identifies the sample.

Matrix

dt_sample.matrix_code

Text (10)

The code that defines a matrix (media) for the specific sample.

Medium

rt.matrix.medium_code

Text (10)

This field is intended to describe a general grouping for the sample (e.g., "air", "water", "biological", "waste" or "soil").

Sample_Date

dt_sample.sample_date

DateTime

The date the sample was collected (Date format = yyyy/MM/dd HH:mm).

Start_Depth

dt_sample.start_depth

Number

The start depth of the sample.

End_Depth

dt_sample.end_depth

Number

The end depth of the sample.

Depth_Unit

dt_sample.depth_unit

Text (15)

Sample depth unit.

Location_Code

dt_sample.sys_loc_code

Text (20)

Location identifier of sample collection, soil boring, or well installation. This value is optional depending on the client setup.

Task_Code

dt_sample.task_code

Text (40)

The task code associated with this record.

Comments

dt_sample.remark

Text (2000)

General remark about the sample.

Analysis Group

Selected_At_This_Level

Calculated field.

Text (1)

“True” or “False”. If “True”, then all members of the Analysis Group are selected at this level.

Name

dt_spm_contract_mag.method_analyte_
group_code

Text (40)

Name of the method analyte group (MAG) code for this record.

Description



MAG description.

SPM_Group

Selected_At_This_Level

dt_spm_contract_mag.can_sub_select_yn

Text (1)

“True” or “False”. If “True”, then all members of the SPM Analysis Group are selected at this level.

Name

dt_spm_contract_mag.remark

Text (2000)

Contract MAG. A contract MAG is a subset of a MAG. This field is displayed on the COC.

Description

dt_spm_contract_mag.contract_mag_desc

Text (255)

Description of the contract MAG.

Method

Code

dt_spm_contract_mag_members.analytic_method

Text (20)

Laboratory analytic method name or description.

Name

rt_analytic_method.preferred_name

Text (255)

The full name of the analytic method (e.g., Volatile Organic Compounds by Gas Chromatograph/Mass Spectrometry for the 'VOC by GC/MS' method).

Description

rt_analytic_method.remark

Text (2000)

Remark field allowing additional information about the record to be stored.

Leachate_Method

dt_spm_contract_method. leachate_method

Text(15)  

Laboratory leachate generation method name or description.

Prep_Method

dt_spm_contract_method. prep_method

Text(20)

Controlled Code for sample preparation method. Lookup to rt_prep_method.

Analyte

Name

rt_analyte.chemical_name

Text (255)

Name of a chemical used in conjunction with its respective Chemical Abstract Service Registry Number (CAS-RN).

CAS_Number

dt_spm_contract_mag_members.cas_rn

Text (15)

Chemical Abstracts Service Registry Number, or other analyte identifying code.

Container

Code

dt_chain_of_custody_mag_cont.container_code

Text (50)

The code that defines the specific container.

Lab_ID



Must always be empty.

Description

dt_chain_of_custody_mag_cont.container_desc

Text (255)

Description of container code.

Nb_of_Containers

dt_chain_of_custody_mag_cont.nb_of_containers

Number

Numeric value representing number of containers.

Preservative

dt_chain_of_custody_mag_cont.preservative

Text (60)

Preservative used.

Filtered

dt_chain_of_custody_mag_cont.filtered

Text (10)

Indicates if the sample is filtered on the field.

Holding_Time

dt_chain_of_custody_mag_cont.holding_time

Number

Technical holding time for the sample.

Holding_Time_Units

dt_chain_of_custody_mag_cont.holding_time_unit

Text (15)

Holding time unit.

Remark

dt_chain_of_custody_mag_cont.remark

Text (2000)

General remark.

Barcodes

dt_chain_of_custody_mag_cont.barcodes

Text (255)

Container barcodes.

 

 

Collect – COC Information

 

Example Output

 

The example output below is a populated Word template converted to a PDF document. The document has three data sourced sections: COC related information, sample and analysis column headers, and rows of sample details and requested analyses.

 

30304_Col-COC_Output_Example

 

COC Word Template Layout

 

First Data Sourced Section

 

The first data sourced section (i.e., COC related information) has the Chain of Custody ID at the top followed by the Facility and Laboratory Info table. The Facility and Laboratory section will print the following info:

 

EQuIS Table

Field

Word Template Field Name

AT_SPM_SAMPLE_CHAIN_OF_CUSTODY

SCHEDULED_TASK_CODE

scheduled_task_code

DT_CHAIN_OF_CUSTODY

CHAIN_OF_CUSTODY

chain_of_custody

DT_CHAIN_OF_CUSTODY

SHIPPING_COMPANY

shipping_company

DT_CHAIN_OF_CUSTODY

CONTACT_NAME_1

coc_contact_name

DT_CHAIN_OF_CUSTODY

COOLER_COUNT

cooler_count

DT_CHAIN_OF_CUSTODY

COOLER_DESC

cooler_desc

DT_CHAIN_OF_CUSTODY

COOLER_ID

cooler_id

DT_CHAIN_OF_CUSTODY

COOLER_TEMP

cooler_temp

DT_CHAIN_OF_CUSTODY

CUSTOM_FIELD_1

custom_field_1

DT_CHAIN_OF_CUSTODY

CUSTOM_FIELD_2

custom_field_2

DT_CHAIN_OF_CUSTODY

CUSTOM_FIELD_3

custom_field_3

DT_CHAIN_OF_CUSTODY

CUSTOM_FIELD_4

custom_field_4

DT_CHAIN_OF_CUSTODY

CUSTOM_FIELD_5

custom_field_5

DT_CHAIN_OF_CUSTODY

SHIPPING_TRACKING_NUMBER

shipping_tracking_number

DT_CHAIN_OF_CUSTODY

CONTACT_NAME_2

contact_name_2

DT_CHAIN_OF_CUSTODY

COMPLETE_YN

complete_yn

DT_CHAIN_OF_CUSTODY

SAMPLER_SIGNATURE

sampler_signature

DT_CHAIN_OF_CUSTODY

TURN_AROUND_TIME

turn_around_time

DT_CHAIN_OF_CUSTODY

QUOTE_NUMBER

quote_number

DT_CHAIN_OF_CUSTODY

SHIPPING_DATE

shipping_date

DT_CHAIN_OF_CUSTODY

EMAIL_INVOICE_TO

email_invoice_to

DT_CHAIN_OF_CUSTODY

EMAIL_REPORT_TO

email_report_to

DT_CHAIN_OF_CUSTODY

RELINQUISHED_BY

relinquished_by

DT_CHAIN_OF_CUSTODY

RELINQUISHED_DATE

relinquished_date

DT_CHAIN_OF_CUSTODY

REMARK

remark

DT_CHAIN_OF_CUSTODY

LAB_RECEIPT_DATE

lab_receipt_date

DT_CHAIN_OF_CUSTODY

LAB_COOLER_COUNT

lab_cooler_count

DT_CHAIN_OF_CUSTODY

LAB_COOLER_DESC

lab_cooler_desc

DT_CHAIN_OF_CUSTODY

LAB_SECURITY_SEAL

lab_security_seal

DT_CHAIN_OF_CUSTODY

LAB_COOLER_TEMP

lab_cooler_temp

DT_CHAIN_OF_CUSTODY

SAMPLER_1

sampler_1

DT_CHAIN_OF_CUSTODY

SAMPLER_2

sampler_2

DT_CHAIN_OF_CUSTODY

SAMPLER_3

sampler_3

DT_CHAIN_OF_CUSTODY

PROJECT_MANAGER

project_manager

DT_CHAIN_OF_CUSTODY

PROJECT_ID

project_ID

DT_CHAIN_OF_CUSTODY

PO_NUMBER

po_number

DT_CHAIN_OF_CUSTODY

DEPARTMENT

department

DT_CHAIN_OF_CUSTODY

NB_OF_BOTTLES_RETURNED

nb_of_bottles_returned

DT_CHAIN_OF_CUSTODY

BOTTLES_RETURNED_DESC

bottles_returned_desc

DT_CHAIN_OF_CUSTODY

LAB_CODE

lab_code

DT_FACILITY

FACILITY_CODE

facility_name

DT_FACILITY

FACILITY_NAME

facility_name

DT_FACILITY

FACILITY_TYPE

fac_facility_type

DT_FACILITY

ADDRESS_1

fac_streetaddress

DT_FACILITY

ADDRESS_2

fac_address_2

DT_FACILITY

CITY

fac_city

DT_FACILITY

COUNTY

fac_county

DT_FACILITY

STATE

fac_state

DT_FACILITY

COUNTRY

fac_country

DT_FACILITY

POSTAL_CODE

fac_postal_code

DT_FACILITY

PHONE_NUMBER

fac_phone_number

DT_FACILITY

EMAIL_ADDRESS

fac_email_address

DT_FACILITY

CUSTOM_FIELD_1

fac_custom_field_1

DT_FACILITY

CUSTOM_FIELD_2

fac_custom_field_2

DT_FACILITY

CUSTOM_FIELD_3

fac_custom_field_3

DT_FACILITY

CUSTOM_FIELD_4

fac_custom_field_4

DT_FACILITY

CUSTOM_FIELD_5

fac_custom_field_5

DT_SAMPLE

SAMPLE_DESC

sample_desc

DT_SPM_SCHEDULED_SAMPLE

CUSTOM_FIELD_1

sample_custom_field_1

DT_SPM_SCHEDULED_SAMPLE

CUSTOM_FIELD_2

sample_custom_field_2

DT_SPM_SCHEDULED_SAMPLE

CUSTOM_FIELD_3

sample_custom_field_3

DT_SPM_SCHEDULED_SAMPLE

CUSTOM_FIELD_4

sample_custom_field_4

DT_TASK

CLIENT

task_client

DT_TASK

CUSTOM_FIELD_1

task_custom_field_1

DT_TASK

CUSTOM_FIELD_2

task_custom_field_2

DT_TASK

CUSTOM_FIELD_3

task_custom_field_3

DT_TASK

CUSTOM_FIELD_4

task_custom_field_4

DT_TASK

CUSTOM_FIELD_5

task_custom_field_5

DT_TASK

TASK_CODE

task_task_code

DT_TASK

TASK_DESC

task_task_desc

DT_TASK

DELIVERY_ORDER

task_delivery_order

DT_TASK

MAX_TURNAROUND_DAYS

task_max_turnaround_days

DT_TASK

PO_NUMBER

task_po_number

DT_TASK

REMARK

task_remark

DT_TASK

CONTACT

task_contact

DT_TASK

TASK_TYPE

task_task_type

DT_TASK

START_DATE

task_start_date

DT_TASK

END_DATE

task_end_date

DT_TASK

MONITORING_PROGRAM

task_monitoring_program

DT_TASK

MONITORING_TYPE

task_monitoring_type

DT_TASK

PERMIT_NUMBER

task_permit_number

RT_COMPANY

COMPANY_NAME

lab_name

RT_COMPANY

ADDRESS_1

lab_streetaddress

RT_COMPANY

CITY

lab_city

RT_COMPANY

STATE

lab_state

RT_COMPANY

POSTAL_CODE

lab_postal_code

RT_COMPANY

PHONE_NUMBER

lab_phone_number

RT_COMPANY

CONTACT_NAME

lab_contact_name

RT_COMPANY

EMAIL_ADDRESS

lab_email_address

 

To repeat this section on all pages of the COC, put this section in the header of the Word template document. In the header, simply type the Word template field name to be populated. The image below is the example “Collect_COC_template,” illustrating how and where information can be controlled within the template.

LIB-Col-COC_Output_Example_Section1-Template

 

Second Data Sourced Section

 

The second data sourced section is the sample and analysis column header. The first 20 column headers under Sample Details are fixed as “Bar Code” (CH0), “Sample ID” (CH1), “Location” (CH2), “Date” (CH3), “Total # of Containers” (CH4), “Matrix” (CH5), “Start Depth” (CH5A), “End Depth” (CH5B), “Depth Unit” (CH5C), “Sample Type” (CH5D), “Sample Method” (CH5E), “Sample Name” (CH5F), “Num” (CH5G), “Sample_Custom_Field_4” (CH5H), “Sample Parameter” (CH5I), “Sample_Filtered” (CH5J), “Sample Start Date” (CH5K), “Sample End Date” (CH5L),  “Composite_YN” (CH5M), and “Medium Code” (CH5N). The next 17 column headers under Analysis Requested are variable in number and will list all the METHOD_ANALYTIC_GROUP_CODEs included in the COC. These MAG codes are all the analyses requested for the samples included in the COC. Users can select what value to show as MAG by selecting different options in Analysis Header report parameter.

Col-COC_Output_Example_Section2

EQuIS Table

Field

Word Template Field Name

DT_CHAIN_OF_CUSTODY_MAG

(Collect COC)  

or

DT_SPM_CONTRACT_MAG / DT_SPM_CONTRACT_METHOD

(SPM COC)

METHOD_ANALYTE_GROUP_CODE
or REMARK

 

 

METHOD_ANALYTE_GROUP_CODE
or REMARK / ANALYTIC_METHOD

“CH6” to “CH22”

 

Users also have the option to show Containers Description, Filtered, Preservatives and Holding Time in separate header rows. The image below is the example “Collect_COC_template,” illustrating the sample and analysis column header section.

LIB-Col-COC_Output_Example_Section2-Template

 

If "Show Individual Containers" was set to "YES" when the report was run, then Analysis will be repeated in the header depending on its row count in the DT_CHAIN_OF_CUSTODY_MAG_CONT table. If "Show Individual Containers" was set to "NO", then analysis will appear only once.

 

Third Data Sourced Section

 

The third data sourced section is immediately below the column headers of the second section and contains the rows or list of samples that have been added to the COC.

Col-COC_Output_Example_Section3

If the bar code option is set to “YES,” the first column cell is the bar code of the Sample ID. The next five column cells to the right are the Sample ID, the sampling location and date, the sample’s matrix, and the total number of containers used for the sampling event.

 

EQuIS Table

Field

Word Template Field Name

DT_SAMPLE

SYS_SAMPLE_CODE

DS1

DT_SAMPLE

SYS_LOC_CODE

DS2

DT_SAMPLE

SAMPLE_DATE

DS3

DT_SAMPLE

NB_OF_CONTAINERS

DS4 (total # of containers)

DT_SAMPLE

MATRIX

DS5

DT_SAMPLE

START_DEPTH

D5A

DT_SAMPLE

END_DEPTH

D5B

DT_SAMPLE

DEPTH_UNIT

D5C

DT_SAMPLE

SAMPLE_TYPE_CODE

D5D

DT_SAMPLE

SAMPLE_METHOD

D5E

DT_SAMPLE

SAMPLE_NAME

D5F

--

--

D5G (Sequential Number)

DT_SAMPLE

CUSTOM_FIELD_4

D5H

DT_SAMPLE_PARAMETER

PARAM_VALUE

D5I (For selected Sample Parameter)

DT_FIELD_SAMPLE

FILTERED_DATE

D5J (Yes/No depending if field is populated or not)

DT_SAMPLE

SAMPLE_START_DATE

D5K

DT_SAMPLE

SAMPLE_END_DATE

D5L

DT_FIELD_SAMPLE

COMPOSITE_YN

D5M

DT_SAMPLE

MEDIUM_CODE

D5N

 

 

Note: DateTime fields can be formatted in the COC word template. For example, Sample Date (DS3) is a DateTime field. The Sample Date and Sample Time in the Word Templates can be in any format. Sample Date can be formatted as “MM/dd/yyyy”, right-click on <<DS3>> and then Edit Field. Click on the Field Codes button at bottom left corner of the dialog. Copy the text below into the Field Codes textbox:

MERGEFIELD  DS3  \@ "MM/dd/yyyy"

To format Sample Time in 24-hour format, use:

MERGEFIELD  DS3  \@ "HH:mm"

 

 

Like the second section column headers, the next 17 column cells will refer to the MAG codes under the Analysis Requested with “X” markings—which means that the MAG code analysis has been requested for this sample row.

 

EQuIS Table

Field

Word Template Field Name

DT_CHAIN_OF_CUSTODY_MAG

SAMPLE_ID

“DS6” to “DS22”

 

The image below is the example “Collect_COC_template,” illustrating the sample and analysis column header section.

Col-COC_Output_Example_Section3-Template

 

 

SPM COC

 

A COC created in SPM can be generated by selecting Source of COC Data as “SPM” and selecting SPM COC in Chain of Custody ID or Chain of Custody.

 

EQuIS Table

Field

Word Template Field Name

DT_SPM_SCHEDULED_SAMPLE

SYS_SAMPLE_CODE

DS1

DT_SPM_SCHEDULED_SAMPLE

SYS_LOC_CODE

DS2

DT_SPM_SCHEDULED_SAMPLE

SAMPLE_DATE

DS3

DT_SPM_SCHEDULED_CONTAINER

NUMBER_OF_CONTAINERS

DS4 (total # of containers)

DT_SPM_SCHEDULED_SAMPLE

MATRIX

DS5

DT_SPM_SCHEDULED_SAMPLE

START_DEPTH

D5A

DT_SPM_SCHEDULED_SAMPLE

END_DEPTH

D5B

DT_SPM_SCHEDULED_SAMPLE

DEPTH_UNIT

D5C

DT_SPM_SCHEDULED_SAMPLE

SAMPLE_TYPE_CODE

D5D

DT_SPM_SCHEDULED_SAMPLE

SAMPLE_METHOD

D5E

DT_SPM_SCHEDULED_SAMPLE

SAMPLE_NAME

D5F

--

--

D5G (Sequential Number)

DT_SPM_SCHEDULD_SAMPLE

CUSTOM_FIELD_4

D5H

--

--

D5I (Not Applicable)

--

--

D5J (Not Applicable)

--

--

D5K (Not Applicable)

--

--

D5L (Not Applicable)

DT_SPM_SCHEDULED_SAMPLE

COMPOSITE_YN

D5M

DT_SPM_SCHEDULED_SAMPLE

MEDIUM_CODE

D5N

 

 

COC Word Template with Single Container Per Sample

 

This COC Word template layout can be used by selecting “YES” for the "Single Container Per Sample" parameter. Each Sample row will have only one container. The MAG code and container details will be displayed on the same sample row. The header row of Sample Analysis table can have columns in any order and can have any column headings. However, the third data sourced section can be populated as per below in the mapping table.

 

EQuIS Table

Field

Word Template Field Name

DT_SAMPLE

SYS_SAMPLE_CODE

DS1

DT_SAMPLE

SYS_LOC_CODE

DS2

DT_SAMPLE

SAMPLE_DATE

DS3

DT_CHAIN_OF_CUSTODY_MAG_CONT

NB_OF_CONTAINERS

DS4 (total # of containers)

DT_SAMPLE

MATRIX

DS5

DT_SAMPLE

START_DEPTH

D5A

DT_SAMPLE

END_DEPTH

D5B

DT_SAMPLE

DEPTH_UNIT

D5C

DT_SAMPLE

SAMPLE_TYPE_CODE

D5D

DT_SAMPLE

SAMPLE_METHOD

D5E

DT_SAMPLE

SAMPLE_NAME

D5F

--

--

D5G (Sequential Number)

DT_SAMPLE

CUSTOM_FIELD_4

D5H

DT_SAMPLE_PARAMETER

PARAM_VALUE

D5I (For selected Sample Parameter)

DT_FIELD_SAMPLE

FILTERED_DATE

D5J (Yes/No depending if field is populated or not)

DT_SAMPLE

SAMPLE_START_DATE

D5K

DT_SAMPLE

SAMPLE_END_DATE

D5L

DT_FIELD_SAMPLE

COMPOSITE_YN

D5M

DT_CHAIN_OF_CUSTODY_MAG

METHOD_ANALYTE_GROUP_CODE

DS6

DT_CHAIN_OF_CUSTODY_MAG

REMARK

DS7

DT_CHAIN_OF_CUSTODY_MAG_CONT

CONTAINER_CODE

DS8

DT_CHAIN_OF_CUSTODY_MAG_CONT

CONTAINER_DESC

DS9

DT_CHAIN_OF_CUSTODY_MAG_CONT

PRESERVATIVE

DS10

DT_CHAIN_OF_CUSTODY_MAG_CONT

FILTERED

DS11

DT_CHAIN_OF_CUSTODY_MAG_CONT

HOLDING_TIME + HOLDING_TIME_UNIT

DS12

DT_CHAIN_OF_CUSTODY_MAG_CONT

REMARK

DS13

 

 

More Collect COC Report Features

 

The Collect COC report has the capability to include more than one COC. The first report parameter is the “Chain of Custody”. Multiple COCs can be selected or included in this report. When more than one COC is selected, the next COC document will print on a new page. Conveniently, only one run and one PDF document is needed to print multiple COCs.

Col-COC_Output_Example_Second_Page

The DT_CHAIN_OF_CUSTODY table has a batch number assigned to each COC. On Enterprise, instead of selecting the Chain of Custody, one may instead use batch numbers. Do not forget to also set “YES” to the parameter “Print Latest COC Only from Multiple Batches” if only the latest batch number is desired to be included in the report.

 

Users can sort the samples in the sample analysis table by selecting an option under the “Sort Samples By” report parameter. Available options are: DT_SAMPLE.SYS_SAMPLE_CODE, DT_SAMPLE.SAMPLE_DATE (Default), DT_CHAIN_OF_CUSTODY_MAG.CUSTOM_FIELD_1, DT_CHAIN_OF_CUSTODY_MAG.CUSTOM_FIELD_2, DT_CHAIN_OF_CUSTODY_MAG.CUSTOM_FIELD_3, DT_CHAIN_OF_CUSTODY_MAG.CUSTOM_FIELD_4 and DT_CHAIN_OF_CUSTODY_MAG.CUSTOM_FIELD_5.

 

Field Results data (e.g., temperature, pH, DO) can be added to COC Word Template. To display Analyte header, use field names <<FR1>>, <<FR2>>, and so on. To display their respective values in the sample row, use <<FRV1>>, <<FRV2>>, and so on. The Analyte header will be displayed as Analyte Name (Unit of Measure). The number of Field Results values displayed on the Chain of Custody are limited to the number of available columns in the Word template.

 

EQuIS Table

Field

Word Template Field Name

RT_ANALYTE

ANL_SHORT_NAME or CHEMICAL_NAME or CAS_RN

FR (Analyte Name)

DT_RESULT

RESULT_UNIT

FR (Unit of Measure)

DT_RESULT

RESULT_TEXT

FRV

 

 

Word Template Technical Notes

 

Mail Merge is the Microsoft Word feature that is used to populate the template with data. Key to moving the correct data to a specific field in the document is the merge field name. It is important to use the merge field names that the current Collect COC report uses. Below is the example “Collect_COC_template” where the field names—colored in RED—matches the Word template field name values from the tables above.

Col-COC_Output_Example-Template

The second section (column headers) and the third section (sample rows) uses a little trickery to be able to print variable length information. For the column header section, field names “CH1” to “CH5” are fixed to print “Sample ID”, “Location”, “Date”, “Matrix”, “Total # of Containers”, “Start Depth”, “End Depth”, “Depth Unit”, “Sample Type”, “Sample Method”, “Sample Name”, “Sample Name”, “Num”, “Sample_Custom_Field_4”, “Sample Parameter”, “Sample_Filtered”, “Sample Start Date”, “Sample End Date”, “Composite_YN”, and “Medium Code”, respectively. Field names "CH6" to "CH22" are set to print the MAG codes sequentially. It is important to leave all 17 fields in the document in case the list of MAG codes reaches that number.

 

To add a header row in the second section, use merge fields <<BeginGroup:TableName>> and <<EndGroup:TableName>> in the first column and last column. Field Names "CH6" to "CH22" are used to print containers information sequentially with respect to MAG codes. Please refer to the table below for the appropriate TableName of different header rows.

 

Header Row

EQuIS Table

Field

TableName

Sample Analysis

DT_CHAIN_OF_CUSTODY_MAG

METHOD_ANALYTE_GROUP_CODE

or REMARK1

CH

Container Description

DT_CHAIN_OF_CUSTODY_MAG_CONT

or

DT_SPM_SCHEDULED_CONTAINER / RT_SPM_CONTAINER

CONTAINER_DESC / CONATINER CODE2

or

REMARK / CONTAINER_CODE

Containers

Preservatives

DT_CHAIN_OF_CUSTODY_MAG_CONT

or DT_SPM_CONTRACT_CONTAINER

PRESERVATIVE

Preservatives

Filtered

DT_CHAIN_OF_CUSTODY_MAG_CONT or

AT_SPM_CONTRACT_MTH_CONTAINER

FILTERED

Filtered

Holding Time

DT_CHAIN_OF_CUSTODY_MAG_CONT

or DT_SPM_CONTRACT_CONTAINER

HOLDING_TIME+ HOLDING_TIME_UNIT

or REMARK3

HoldingTime

No. of Containers

DT_CHAIN_OF_CUSTODY_MAG_CONT

or DT_SPM_SCHEDULED_CONTAINER

NB_OF_CONTAINERS

Nb_Containers

Container Type

DT_CHAIN_OF_CUSTODY_MAG_CONT

or DT_SPM_SCHEDULED_CONTAINER

REMARK3

Container_Type

Container Size or

Volume

DT_CHAIN_OF_CUSTODY_MAG_CONT

or DT_SPM_SCHEDULED_CONTAINER

REMARK3

Container_Size

1Depending on selection of the “Analysis Header” report parameter.

2If first column value is empty, then it will show second column value.

3If remark is used to save Container Type, Container Size and Holding Time separated by “|” in the exact same order.

 

The sample rows section field names “DS1” to “D5F” are the actual data value corresponding to the column header above. If the column headers “CH1” to CH5” are switched around or excluded, the sample row fields should follow the header changes. The “D5G” column will show sequential number for sample rows. Field names “DS6” to “DS22” should also align with the “CH6” to “CH22” headers.

 

Note: It is important to leave the <<BeginGroup:CH>> and <<EndGroup:CH>> tags where they are placed in the column headers sections as they control the begin and end of one COC. Thus, if there are multiple COC in the report, the current placement of these controls will force the next COC to print on a new page.

 

For the sample rows section, it is important to leave the <<BeginGroup:Samples>> and <<EndGroup:Samples>> tags where they are placed as these control where the row of data will print.

 

The barcode, the first column in the sample row section, has the field name “image:bc_sys_sample_code”. The barcode header and bar code image can be relocated. EarthSoft recommends to not delete this field since the report parameter has the option to print a bar code and the report will generate an error if the bar code printing option is selected and the bar code field is not in the template.

 

The COC ID Barcode can be added to the COC Word Template using the field name <<Image:bc_chain_of_custody>>. If the COC Word Template has a repetitive header or footer, then use simple text as bc_chain_of_custody. The COC ID Barcode Type can be selected as "1D" or "QR" using the report parameter.

 

All other non-merge field items on the document template are free to be modified, deleted, and/or added. Bit mapped images or pictures of company logos can be inserted anywhere in the document.

 

Note: The date format used on various date/time fields can be customized in the DOTX file. Perform the following steps to customize the date format in the template file:

1.Open the DOTX file.

2.Find the merge field for the date value (e.g., ‹‹DS3›› for the sample date).

3.Right-click it and select Toggle Field Codes.

4.Add a date modifier inside the curly brackets ({}) to set the new date format. The following example sets the date format to MM/dd/yyyy.

{ MERGEFIELD DS3 \@ "MM/dd/yyyy" \* MERGEFORMAT }

5.Save the template (DOTX).

 

The template needs to be loaded to the DT_FILE table (as noted above in this article) before the template is available to be selected in the report’s “COC Word Template” parameter. It is recommended to create a backup of every template used and created.

 

To create a new Word template, it is helpful to start with the example “Collect_COC_template”, save as a new filename, and then modify the template as desired. Lastly, before working on any template changes, familiarity with the Word Field screen (as seen below) is recommended.

Col-COC_Word_Field_Screen

 

The COC Word templates support Conditional fields. For example, if Composite_YN field is “Y” then show “Composite” as an output else “Grab”. The formula for IF condition looks like:

{ IF merge-field-name = "expression" "if condition met" "if condition not met" }

 

The correct way is to add IF condition first. Then Toggle Field Code using Shift+F9. Then, add merge field at the position of merge-field-name.