<< Click to Display Table of Contents >> Navigation:  EQuIS Library > Reports > EarthSoft Standard Reports by Product > Standard with Collect > Collect – COC Report

Report Name: Collect – COC (example)

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

 

Description: The Collect – COC 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 and the Collect 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 in the mobile app, do the following:

1.Save a user report for the Collect – COC 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

 

 

 

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.

 

 

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:

 

 

 

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.

 

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.

 

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.

 

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.

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.

 

 

 

 

 

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.

 

 

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

 

 

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.

 

 

 

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.

 

 

 

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.

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.

 

 

 

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.

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.

 

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.

 

 

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.

 

 

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.

 

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.