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”

EQuIS Table

Field

Word Template Field Name

DT_CHAIN_OF_CUSTODY_MAG

(Collect COC)

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.

Collect – COC and eCOC Report