<< Click to Display Table of Contents >> Navigation: SPM > SPM > Formats > EZSPM Format |
Format Name: EZSPM
File Name: EZSPM.zip
Software: EQuIS SPM
Source: SPM install (and xcopies)
Export(s): Not Applicable
Description: The EZSPM format is a simplified version of the SPM format. The EZSPM format allows users to easily create multiple planned and scheduled tasks, samples, results, containers, and activities with limited manual data entry. This format assumes the plan already exists in SPM and the SPM_CONTRACT_MAG information is already setup. Due to the simplified nature of the EZSPM format, there is not a distinct one-to-one relationship between the SPM format sections and the EZSPM format sections.
Installation Instructions: This format requires a licensed SPM and the SPM Schema applied to the database.
•For SPM usage, no additional steps are necessary.
Overview: The Analysis_Request_General, Analysis_Request_Detail, and Activity_Request_Detail sections automatically create multiple Task, Sample, and Activity rows, respectively, on EDD load. The Task, Sample, and Activity sections can then be reviewed and modified before creating and committing. The Analysis_Request_General, Analysis_Request_Detail, and Activity_Request_Detail sections do not map to any database tables—these sections just create rows in other sections of the EZSPM format.
The EZSPM format contains the following sections:
•Analysis_Request_General – No data table mappings, automatically populates and creates values in the Task section of the EDD. Some reference values can be added, but it is not advised to add reference values using this format. This section must be pre-populated before loading into EDP to populate the Task section.
•Analysis_Request_Detail – No data table mappings, automatically populates and creates values in the Sample section of the EDD. Some reference values can be added, but it is not advised to add reference values using this format. This section must be pre-populated before loading into EDP to populate the Sample section.
•Activity_Request_Detail – No data table mappings, automatically populates and creates values in the Activity section of the EDD. Some reference values can be added, but it is not advised to add reference values using this format. This section must be pre-populated before loading into EDP to populate the Activity section.
•Task – Parts of this section can be automatically populated by the Analysis_Request_General section. This section is for data related to sampling events that were planned or scheduled in SPM.
•Sample – Parts of this section can be automatically populated by the Analysis_Request_Detail section. This section is used for data related to the samples involved in the associated SPM task.
•Activity – Parts of this section can be automatically populated by the Activity_Request_Detail section. This section is used for data related to specific actions being performed at specific locations during a sampling event.
•Collect_Members – EQuIS Collect users associated with the SPM plan can be defined here.
•Files – Any files that are associated with a sample delivery group can be reported here.
The workflow for configuring SPM plans and tasks with this format may include:
1.Open the EZSPM format in EDP.
2.Add a single row to the Analysis_Request_General section. This will correspond to one Task row. Note that each EDD can only have one Analysis_Request_General row.
3.Add one or more rows to Analysis_Request_Detail section. Each row in Analysis_Request_Detail will create one to many Sample rows. Analysis_Request_Detail.location_group or Analysis_Request_Detail.location dictates how many Sample rows will be created. One Sample row will be created for every location within the location_group, or individual location listed. The Sample section creates samples, results, and containers when committed. One container per SPM_CONTRACT_MAG will be created.
4.Add one or more rows to Activity_Request_Detail. Each row in Activity_Request_Detail will create one to many Activity rows. Activity_Request_Detail.location_group or Activity_Request_Detail.location dictates how many Activity rows will be created. One Activity row will be created for every location within the location_group, or individual location listed.
5.Save the EDD.
6.Clear EDP or reopen the EZSPM format.
7.Open the EDD saved in Step 5.
8.Review the Task, Sample, and Activity rows for errors.
9.Add Collect_Members rows as desired. This section allows users to add specific user_names to existing Collect groups.
10.Create and Commit.
Limitations/Assumptions: The checks and mappings in any EDD format involve assumptions. The following are assumptions and limitations for the EZSPM format.
•The Samples table contains a check to ensure the records plan_code, lab_code, contract_start_date, method_analyte_group_code, and spm_contract_mag corresponds to a row in DT_SPM_CONTRACT_MAG. A "plan_code, lab_code, contract_start_date, method_analyte_group_code, and spm_contract_mag must correspond to a row in dt_spm_contract_mag" error will occur if a record does not match or fields called upon by the check are NULL in DT_SPM_CONTRACT_MAG. The fields included in this check are outlined below:
•DT_SPM_CONTRACT_MAG.SPM_CONTRACT_ID must be populated for the associated DT_SPM_CONTRACT record to provide the plan_code (DT_SPM_CONTRACT.PLAN_CODE), lab_code (DT_SPM_CONTRACT.CONTRACTOR_CODE) and contract_start_date (DT_SPM_CONTRACT.CONTRACT_START_DATE).
•DT_SPM_CONTRACT_MAG.REMARK must be populated with the spm_contract_mag_id to provide the spm_contract_mag.
•DT_SPM_CONTRACT_MAG.METHOD_ANALYTE_GROUP_CODE must be populated to provide the method_analyte_group_code.
•When populating the Analysis_Request_Detail and Activity_Request_Detail sections, either populate location_group or location. Do not populate both.
Recurrence:
The Task.Recurrence field can be used to define the frequency with which a task takes place. This field requires a specific notation to be properly parsed by SPM. See below for the specific notation and examples.
Note: There is a known issue where a "Conversion from type 'DBNull' to type 'String' is not valid" error is thrown when attempting to open tasks that were created via this format in SPM. Users can get around this behavior by opening DT_SPM_PLANNED_TASK in the database and populating the DURATION_TYPE field with ‘d’. |
•When using the Analysis_Request_Detail section to help populate the Task section, the recurrence field is populated by default with a value of ‘n’. This will create a single task instance that spans the entire length of the Plan, from the start date to the end date. To change this, users must configure the recurrence using one of the notations defined below.
•The individual sections within the notation are separated by a pipe delimiter ( | ), the number of which will vary depending on the specifications of the recurrence.
•Daily
•d|x, where x = every x days| x (same value as previous x)|True/False, where True = tasks occur only on weekdays
•Example: d|5|5|False – Tasks will take place on a daily schedule, every 5 days, weekends included.
•Weekly/Bi-Weekly/N-Weekly
•w|Comma separated list of days on which tasks occur1|x, where x = every x number of weeks
1 Days of the week must be represented by their numerical equivalents, where Monday =1, Tuesday = 2, and so on until Sunday = 7.
•Example: w|2,5|2 – Tasks will take place on a weekly, bi-weekly, or n-weekly schedule, every Tuesday and Friday, every 2 weeks.
•Monthly/Quarterly/Semi-annually
•m|Day of the week|nth1|Day of the week2|nth3|Month Count
1 The first ‘nth’ value has 6 valid values: First, Second, Third, Fourth, Fifth, and Last.
▪This section should be populated if a user intends to enter a specific day of the week in the first ‘Day of the week’ section, such as “on the Second Tuesday of every Month”.
▪This section should be left blank if a user intends to just use ‘Day’, such as “on the 2nd Day of every Month”.
2 If a day of the week is entered at all, it must be entered twice. There should be no difference between the second section of the notation and the fourth.
3 The second ‘nth’ value has 31 valid values (1-31). This field should always be populated. If a value is entered in the first ‘nth’ section of the notation, such as ‘Second’, then enter a ‘1’ in this section.
Note: In the case where some sections of the notation will be left blank, the pipe delimiters should still be entered but nothing should come between them. |
•Example: m|friday|second|friday|1|2 – Tasks will occur on a monthly, quarterly, or semi-annual schedule, on the second Friday of every 2 months.
•Example: m||||4|5 – Tasks will occur on a monthly, quarterly, or semi-annual basis, on the 4th day of every 5 months.
In this case, the Day of the week and first ‘nth’ sections were not necessary, so 4 pipe delimiters were entered with nothing in between to signify that the value of these 3 sections is null.
•Annually
•y|Day of the week|nth1|Day of the week2|nth3|Month4|x, where x = every x number of years
1 The first ‘nth’ value has 5 valid values: First, Second, Third, Fourth, and Last.
▪This section should be populated if a user intends to enter a specific day of the week in the first ‘Day of the week’ section, such as “on the Second Tuesday of every October”.
▪This section should be left blank if a user intends to just use ‘Day’, such as “on the 2nd Day of every October”.
2 If a day of the week is entered at all, it must be entered twice. There should be no difference between the second section of the notation and the fourth.
3 The second ‘nth’ value has 31 valid values (1-31). This field should always be populated. If a value is entered in the first ‘nth’ section of the notation, such as ‘Second’, then enter a ‘1’ in this section.
4 Months must be represented by their numerical equivalents, where January = 1, February = 2, and so on until December = 12.
Note: In the case where some sections of the notation will be left blank, the pipe delimiters should still be entered but nothing should come between them. |
•Example: y|monday|third|monday|1|10|2 – The task will occur on an annual basis, every third Monday of October every 2 years.
•Example: y||||2|4|3 – Tasks will occur on an annual basis, on the 2nd of April every 3 years.
In this case, the Day of the week and first ‘nth’ sections were not necessary, so 4 pipe delimiters were entered with nothing in between to signify that the value of these 3 sections is null.
Note: Task, Sample, and Activity rows will be automatically created each time an EZSPM EDD is loaded with Analysis_Request_General, Analysis_Request_Detail, and Activity_Request_Detail information. If there needs to be changes to the Analysis_Request_General, Analysis_Request_Detail, or Activity_Request_Detail sections after opening the EDD (e.g., Step 7 above, which automatically populated Task, Sample, and Activity), ensure that Task, Sample, and Activity are cleared before saving the EDD. Not doing so will result in duplicate Task, Sample, and Activity rows when the EDD is opened again. |
Note: When adding a new record to the RT_COMPANY table from the Analysis_Request_General, Analysis_Request_Detail, Task, Sample, or Activity sections of the format, the record will be generated with a COMPANY_TYPE value of "LAB" as set in the RT_COMPANY_TYPE table. The value of "LAB" or a variation (e.g., "Lab") must be present in RT_COMPANY_TYPE table. To disable the automatic addition of a new RT_COMPANY table record, set the EDP configuration settings to not accept warnings. |
The tables below provide detailed automatic population information for the Task, Sample, and Activity sections. Blank cells indicate that the field is not automatically populated.
Task |
Populated By |
---|---|
plan_code |
Analysis_Request_General.plan_code |
planned_task_code |
Analysis_Request_General.task_code |
planned_task_name |
|
scheduled_task_code |
Analysis_Request_General.task_code + _+ Analysis_Request_General.start_date |
start_date |
Analysis_Request_General.start_date |
end_date |
Analysis_Request_General.end_date |
remark |
Analysis_Request_General.other |
task_type |
Analysis_Request_General.activity_type |
company_code |
Analysis_Request_General.company_code |
max_turnaround_days |
Analysis_Request_General.max_turnaround_days |
task_desc |
|
recurrence |
n |
custom_field |
Analysis_Request_General.use_collect |
create_scheduled_tasks |
Analysis_Request_General.create_scheduled_tasks |
include_dates |
Analysis_Request_General.start_date + | + Analysis_Request_General.start_date + || |
naming_schema_task |
Sample |
Populated By |
---|---|
plan_code |
Analysis_Request_General.plan_code |
lab_code |
Analysis_Request_Detail.lab_code |
contract_start_date |
dt_spm_contract where contractor_code = Analysis_Request_Detail.lab_code and plan_code = Analysis_Request_General.plan_code or is null |
method_analyte_group_code |
Analysis_Request_Detail.method_analyte_group_code |
spm_contract_mag |
Analysis_Request_Detail.spm_contract_mag |
sys_sample_code |
Analysis_Request_Detail.location (or location from Analysis_Request_Detail.location_group) + _ + Analysis_Request_Detail.matrix_code + _ + Analysis_Request_General.start_date + _ + an incremental three-digit number |
sample_name |
|
scheduled_task_code |
Analysis_Request_General.task_code + _+ Analysis_Request_General.start_date |
parent_sample_code |
|
sys_loc_code |
|
start_depth |
|
end_depth |
|
depth_unit |
|
matrix_code |
Analysis_Request_Detail.matrix_code |
sampler |
|
sampling_company_code |
|
sample_class |
|
sample_source |
|
sample_type_code |
Analysis_Request_Detail.sample_type_code |
sample_method |
|
medium_code |
|
composite_yn |
|
fraction |
Analysis_Request_Detail.total_or_dissolved |
sample_remark |
|
hold_YN |
|
hold_release_date |
Activity |
Populated By |
---|---|
plan_code |
Analysis_Request_General.plan_code |
scheduled_task_code |
Analysis_Request_General.task_code + _+ Analysis_Request_General.start_date |
sys_loc_code |
Activity_Request_Detail.location (or location from Activity_Request_Detail.location_group) |
activity_code |
Activity_Request_Detail.activity_code |
equipment_code |
Activity_Request_Detail.equipment_code |
sampler |
|
sampling_company_code |
|
remark |
|
task_type |
|
activity_type |
Activity_Request_Detail.activity_type |
activity_order |
|
count |
|
unit |
EQuIS Table Mappings:
•DT_FILE •DT_SPM_PLAN •DT_SPM_PLANNED_ACTIVITY •DT_SPM_PLANNED_RESULT •DT_SPM_PLANNED_SAMPLE |
•DT_SPM_PLANNED_TASK •DT_SPM_SCHEDULED_ACTIVITY •DT_SPM_SCHEDULED_SAMPLE •DT_SPM_SCHEDULED_TASK |
•RT_COMPANY •RT_GROUP_MEMBER •RT_MATRIX •RT_SAMPLE_TYPE |
Copyright © 2025 EarthSoft, Inc. • Modified: 28 Mar 2025