Workflow Status

EarthSoft added the WORKFLOW_STATUS column in build 7.0.0.18212 to allow a freeform field for users to define workflows for their data. An database stored procedure, equis.add_workflow, will add this column to tables and can also set up reference or default values. Values added are specific to an individual table; therefore, multiple workflows can be set up within a single database.

The EQuIS Geotech Schema adds WORKFLOW_STATUS to the following tables by default: DT_FACILITY, DT_LOCATION, DT_TASK, and Geotech-only table DT_GEO_LAB_ASSIGN.

Note: Adding the WORKFLOW_STATUS column to a table can affect formats that load data to that table. To avoid conflicts, administrators can turn add the SkipBulkCopy setting for that table in the ST_CONFIG system table.

equis.add_workflow Stored Procedure

A database administrator can add the WORKFLOW_STATUS column to a table by executing the equis.add_workflow stored procedure in SQL Server Management Studio (SSMS):

exec equis.add_workflow @schema_name, @table_name, @workflow_status;

There are 3 variables used in the stored procedure:

1.@schema_name – the database schema for the table, e.g. ‘dbo’

2.@table_name – the name of the table where the WORKFLOW_STATUS column is being added, e.g. ‘DT_COORDINATE’

3.@workflow_status – must be exactly 4 characters in length, e.g. 'surv’ or ‘test’. The @workflow_status parameter defines the default value used to populate any NULL entries in the WORKFLOW_STATUS column in the table specified. If the WORKFLOW_STATUS column is being added to the table for the first time, this means that all existing records will now contain this default value in the WORKFLOW_STATUS column. If an additional WORKFLOW_STATUS value is added to this table, then the last value added becomes the new default value for the table.

This example adds a workflow status of surv to the DT_COORDINATE table:

USE [MyDatabase]

exec equis.add_workflow 'dbo', 'dt_coordinate', 'surv';

Running the equis.add_workflow stored procedure does the following:

•Invokes the equis.add_euid stored procedure to ensure the table has a EUID column (the workflow_status functionality only works on tables with this column in place and populated)

•Ensures existing and future records for the given table have EUIDs via the equis.populate_euid stored procedure

•Adds the WORKFLOW_STATUS column (set to NOT NULL, so required to be populated) to the given table with a default value of the given @workflow_status

oif column already exists, will update the default constraint with the given @workflow_status

•Adds a record to RT_WORKFLOW_STATUS with the given @workflow_status value for the given table

•Invokes equis.refresh_schema to (re)create triggers for the given table

RT_WORKFLOW_STATUS

Fields stored in the RT_WORKFLOW_STATUS reference table include table IDs (will differ by database; contact EarthSoft Support if assistance is required), workflow status codes of exactly 4 characters in length, and remarks. Entries added via the stored procedure (i.e. as default values) will generate remarks with the following structure, although any text can be used in this field:

Workflow enabled for table [dbo].[table_name] on [date] by [SQL account]

Entries other than the @default values added by the stored procedure can be added manually by users with the appropriate permission, as well. Consult your EQuIS Database Administrator regarding proposed changes.

Warning: Editing the contents of RT_WORKFLOW_STATUS does not automatically update the tables where the stored procedure has been run. Update the WORKFLOW_STATUS column in the affected table accordingly. For example, deleting an entry from RT_WORKFLOW_STATUS will not affect the default value. The stored procedure must be rerun with a new @workflow_status to change the default value so that database errors will not occur upon inserting new data into the affected table.

Inserting and Updating WORKFLOW_STATUS

As the equis.workflow_status stored procedure sets a default value (using the @workflow_status variable that was specified when running equis.add_workflow), adding this parameter to EDD formats is not required to initially populate the WORKFLOW_STATUS field. Data inserted into the table will automatically include the default value unless a different WORKFLOW_STATUS is populated in the EDD (i.e. if the format that is used maps to the WORKFLOW_STATUS column of that table).

To update the workflow_status field of a given table to a new status, administrators can use the following approaches:

1.Customize or create a new EDD format, where the WORKFLOW_STATUS column has been added:

a.Export the data if the format has an Export option, or use the original EDD if the data has not changed.

b.Update the field.

c.Reimport the data using the Update commit type.

2.Use Find and Replace (CTRL+F) on selected entries within EQuIS Professional. Filtering by fields such as EBATCH or LAB_SDG could help find these entries.

3.Run an UPDATE script in SSMS (for database administrators).

4.For the DT_SAMPLE, DT_TEST, or DT_RESULT tables, use the SampleTestResult II or SampleTestResult III forms.

Example

This example goes over setting up WORKFLOW_STATUS as a column in DT_COORDINATE. One hypothetical use for this process could be uploading coordinate data from surveyors, approving the data after checking it on a map, then later changing the status on any location coordinates that may have shifted over time. Professional judgment should be used in designing a workflow; the example presented is not intended to represent a recommendation.

1. Run the following statement in SQL Server Management Studio:

USE [MyDatabase]

exec equis.add_workflow 'dbo', 'dt_coordinate', 'surv';

2. In EQuIS Professional, open the RT_WORKFLOW_STATUS reference table and sort the ‘euid’ column by descending order. The first entry will be the record added in step 1.

3. Add additional WORKFLOW_STATUS entries as required (e.g. ‘appr’, ‘redo’), using the TBL_ID from the topmost record if you wish to retain the initial value as the default for new records, or use the stored procedure again to add new values if you wish to update the default value.

Use [MyDatabase]

exec equis.add_workflow 'dbo', 'dt_coordinate', 'appr';

exec equis.add_workflow 'dbo', 'dt_coordinate', 'redo';

4. Upload the coordinate data, e.g. via the Locations EDD. It will populate DT_COORDINATE.WORKFLOW_STATUS = “surv” in the first scenario where records for ‘appr’ and ‘redo’ are added manually to RT_WORKFLOW_STATUS, and DT_COORDINATE.WORKFLOW_STATUS = “redo” in the second scenario where additional values are added via the equis.add_workflow stored procedure.

5. Following review by a data manager:

a. Select the WORKFLOW_STATUS field for approved records in DT_COORDINATE (via use of filters and/or using the Shift or Ctrl keys to select multiple records manually).

b. Click Find and switch to the Replace tab, or press Ctrl + H.

c. Replace ‘surv’ with the new required status, e.g. ‘appr’.

6. If coordinate information is deemed to be out-of-date, the workflow status can be updated, e.g. to ‘redo’, to signify this change.

Reporting

For tables that appear in the Additional Fields report parameter (@extra_fields) for a report, the WORKFLOW_STATUS column will automatically be added as an available selection. Note that Additional Fields available can differ by report. Otherwise, the Additional Fields parameter can be modified to add in WORKFLOW_STATUS with an appropriate JOIN condition, or WORKFLOW_STATUS can be added as a report parameter. Since WORKFLOW_STATUS can appear in multiple tables, it is recommended that the table name is clearly specified in the parameter name.

The Analytical Results II report has a Workflow Status parameter for filtering on values in this field in any table to which it has been added.