Collect – eCOC Report

Report Name: Collect – eCOC (example1) (example2)

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: XML

Description: The Collect – 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. First 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. 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 in the Downloads section within the "EQuIS Collect Templates" folder.

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 if run without a "Chain of Custody" parameter populated. Select a value from the parameter’s list when running manually from EQuIS Professional or Enterprise’s EZView widget. Leave the “Chain of Custody” parameter blank when setting up a User Report for use in Collect Mobile as a Mobile Report or an EIA (triggered on New Data); this allows the report to find the appropriate COCs at runtime based on associations between the submitted Collect form and EBATCH numbers.

Since the Collect - eCOC report is generated through the server, data from a Collect Data Form must be committed through EQuIS Data Processor (EDP) in order to generate the report.

Tables: DT_SAMPLE, DT_CHAIN_OF_CUSTODY, DT_CHAIN_OF_CUSTODY_MAG

Report Parameters

Chain of Custody	Select one or more Chain of Custody.
Lab	Select laboratory.
Print Latest COC Only from Multiple eBatches
Source of Data	Filters the Chain of Custody output based on the source selected. Set to "Collect" to only export information from Collect (for COCs that have a DT_CHAIN_OF_CUSTODY_PARAMETER row with PARAM_CODE = 'Source' and PARAM_VALUE = 'Collect'), or "SPM" to only export information from SPM (for COCs that do not have a DT_CHAIN_OF_CUSTODY_MAG row). Default value is empty, which allows for exporting information from any source.
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.

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

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.
Complete_YN	dt_chain_of_custody.complete_yn	Text (1)	"Y" or "N". If "Y", the sampling event is complete and results should be included in the report output.
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.