•equis.add_workflow Stored Procedure
•Inserting and Updating WORKFLOW_STATUS
EarthSoft added the WORKFLOW_STATUS column in Build 7.0.0.18212 to allow a free-form field for users to define workflows for their data. A 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 the WORKFLOW_STATUS column to the following tables by default: DT_FACILITY, DT_LOCATION, DT_TASK, and the Geotech-only table DT_GEO_LAB_ASSIGN.
Note: Adding the WORKFLOW_STATUS column to a table may cause the "ExecuteNonQuery Connection Property not Initialized" error when loading data to that table. To resolve this error, administrators can add the SkipBulkCopy setting in ST_CONFIG for the specific 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. Multiple WORKFLOW_STATUS values can be added to a table using the equis.add_workflow stored procedure. The last value added using the equis.add_workflow stored procedure becomes the new default value for the table. When running the equis.add_workflow stored procedure for the first time on a table, WORKFLOW_STATUS will be populated with the default value for all existing records in that table. Any new records added to the table without a value specified for the WORKFLOW_STATUS field will automatically have the WORKFLOW_STATUS field populated with the default value.
This example adds a workflow status of surv to the DT_COORDINATE table:
USE [MyDatabase]
GO
exec equis.add_workflow 'dbo', 'dt_coordinate', 'surv';
GO
Running the equis.add_workflow stored procedure does the following:
•Invokes the equis.add_euid stored procedure to ensure the target table (DT_COORDINATE in the example above) 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 target table (DT_COORDINATE in the example above) 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 target table (DT_COORDINATE in the example above) with a default value of the specified @workflow_status
oif the column already exists, will update the default constraint with the specified @workflow_status — the value found in the WORKFLOW_STATUS field will not change for existing records
• Adds a record to RT_WORKFLOW_STATUS with the given @workflow_status value for the target table
•Invokes equis.refresh_schema to (re)create triggers for the target 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]
GO
exec equis.add_workflow 'dbo', 'dt_coordinate', 'surv';
GO
2.In EQuIS Professional, there are two ways that the default value can be determined for a table:
a.Using the SQL Form
Enter the following SQL query into the SQL Form. This will show the default values for each table in the database that has a WORKFLOW_STATUS column.
select TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT
from information_schema.columns
where COLUMN_NAME = 'workflow_status';
b.Add new row to target table
Navigate to the target table and add a new row. The value that appears in the WORKFLOW_STATUS field is the default value. Data does not need to be added to the new row or the new row saved in order to see the default value.
3.Add additional WORKFLOW_STATUS entries as required (e.g. ‘appr’, ‘redo’), either manually (using the same TBL_ID as the record for the default value added using the equis.add_workflow stored procedure) or using the stored procedure again — the default value for a table will always be the workflow_status that was most recently added for that table via the equis.add_workflow stored procedure.
Use [MyDatabase]
GO
exec equis.add_workflow 'dbo', 'dt_coordinate', 'appr';
GO
exec equis.add_workflow 'dbo', 'dt_coordinate', 'redo';
GO
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.