Views embedded in the content database

Applies to version 2020.1.x, author: Dawid Golonka

Introduction

When creating support for business processes in WEBCON BPS, it is important to remember that even the simplest workflow with a minimum number of steps and form fields will be reflected in the database – all of its instances and settings are represented in the content database – which is the main place of the system’s operation. There are also “virtual tables” automatically created by the system – called SQL views – that contain data from the linked tables.

This article describes some of the most important views that users can refer directly to the WEBCON BPS Designer Studio level or from the database management system level.

Views description

a) Automatically generated process views

A view aggregates all the most important information from the tables related to a given process. The name view contains the process name and their ID to uniquely identify processes, even those with identical names.

Fig. 1. The name view for the selected process is created from their name and the ID number

Fig. 2. The process based on the view from Fig. 1

The view displays data from the columns which are present by default for each process, and additionally from the columns that store the selected form field values created in the process – in the number corresponding to the number of the form fields. This data comes from 7 tables and the column that uniquely identifies each row is the element ID.

The most important information that can be received from this view is:

• The ID and name of the process
• The ID and the name of the workflow
• The ID and the name of the step
• The ID and the name of the form type
• Information about the parent workflow
• Information about the author and the last modifier
• Information about the instance number
• Information about the business entity
• Information about the created and modification date
• Information about form field values (the column names are the same as the form field names on the form)

In the query to the view you can use conditions and results filtering e.g. searching information about instances of a given author etc. The additional uses and features of the process view:

• Allows you to quickly check the number of instances created in a given process.
• Allows you to easily write your own queries to SQL Grids (avoiding the need to write complex structures with connections to many tables). Remember that the process ID changes every environment, causing the view name to change.
• The column names referring to the form field values are analogical to the names of these form fields on the form – this increases the readability of the preview
• The view is constantly updated by the system, always provides a preview of the current configuration

b) The V InlineBUsinessRUlesUsages view

This view contains information about using business rules. It consists of five columns:

• RuleID – the ID of a business rule
• ObjectID – the ID of the object where the business rule is used
• TableName – the table name where the row with the identifier specified by ObjectID is stored
• ObjectIDColumnName – information about the column where the ObjectID is stored
• ColumnName – the column name where the business rule ID used in the object associated with the table contained in TableName is stored

Below is an example of how to read a row from the result of a view query. Entries referring to the WFSteps table refer to the business rules used in the step configuration.

Fig. 3. An example query to the V InlineBusinessRulesUsages view

The business rule has ID = 1079 and is in the object with ID = 271 identified by the STP_ID column in the WFSteps table. The WFSteps table contains information about the steps in the workflow. Finding the step with a given ID, it can be stated that it is a workflow control type step, and the searched rule is added in the ’Flow control’ section, which defines the path through which the instance must travel through.

Fig. 4. The rule in the workflow control step

Information about the rule ID in the WFSteps table is in the “STP_BranchConditionBRID” column:

Fig. 5. The place of storing information about the rule ID in the WFSteps table

Below there is a description several other results which can be obtained in queries of this view:

TableName: WFBusinessRuleParameters,

OBjectIDColumnName: BRP_ID,

ColumnName: BRP_RuleID

Entries referring to the WFBusinessRuleParameters table contain parameters that can be defined when creating the business rules, allow you to enter their values into the formulas executed in the rule. Below there is an example result from the query:

Fig. 6. A result of the query relates to the WFBusinessRuleParameters table

By querying the WFBusinessRuleParameters table with a query narrowed to BRP_ID = 24, you obtain information about the given parameter:

Fig. 7. The query to the WFBusinessRuleParametets table

The screenshot below shows the location of the parameter in WEBCON BPS Designer Studio:

Fig. 8. Location of the parameter

This parameter is used in the rule definition SQL command:

Fig. 9. The parameter used in the SQL query

TableName: WFActionBusinessRules,

OBjectIDColumnName: ABR_ID,

ColumnName: ABR_BRDID

Entries refer to the WFActionBusinessRules table are about the business rules used in the action configuration. An example result is presented below:

Fig. 10. The result of the query to the view

By now querying the WFActionBusinessRules table with the ObjectID you can find out what action the rule applies to:

Fig. 11. The query to the WFActionBusinessRules tables with using the given ObjectID

The rule applies to the action with ID = 1105 (the ABR_ACTID column returns the action ID). This is the “Change value of single field” action and the rule was used in their configuration:

Fig. 12. The rule form in the “Change value of single field” action

TableName: WFAvaiblePaths,

OBjectIDColumnName: PATH_ID,

ColumnName: PATH_OnSubmitUxBRID

Entries referring to the [PATH_OnSubmitUxBRDID] field in the WFAvaiblePaths table apply to the business rules that create additional validation when going through the path. An example result is presented below:

Fig. 13. The result of the query refers to the WFAvaiblePaths table

The rule is performed on the path about ID = 1133.

Fig. 14. The path on which the rule is performed

The place of their configuration is in the “Parameters” tab:

Fig. 15. Configuration of the rule

d) The V_SecuritiesByUser view

The view is built from two columns – instance ID in the workflow and the user name. It allows you to receive information about instances available for each user.

Fig. 16. Instances available to the users

An example query to the view:

Fig. 17. The query to the view

Views storing information about KPI values

WEBCON BPS allows you to generate KPIs that can be used to perform analyses of the workflow instances in the processes.  The system automatically creates 13 views in the database that differ in terms of filtering and aggregating data:

• V_KPI_Elem_ByDefinitions – the view presents statistics of the instance workflow divided into processes
• V_KPI_Elem_ByStep – the view presents statistics of the workflow instances divided into steps
• V_KPI_Elem_ByWorkFlow – the view presents statistics of the instances divided into individual workflows
• V_KPI_Elem_Global – the view presents statistics of all instances in the system
• V_KPI_Tops_Activity_ByDefinitions – the view presents statistics of the users’ activity (the number of pathways) divided into individual processes
• V_KPI_Tops_Activity_ByStep – the view presents statistics of the users’ activity (the number of pathways) divided into individual steps
• V_KPI_Tops_Activity_Global – the view presents statistics of the users’ activity (the number of pathways) for all instances in the system
• V_KPI_Tops_Delegates_ByDefinitions – the view presents statistics about the number of tasks delegated by the system users in the selected processes
• V_KPI_Tops_Delegates_ByStep – the view presents statistics about the number of tasks delegated by the system users in the individual steps
• V_KPI_Tops_Delegates_Global – the view presents statistics about the number of tasks delegated by the system users in terms of the entire system
• V_KPI_Tops_Overdue_ByDefinitions – the view presents statistics about the number of tasks assigned to the specific users that are already overdue, divided into individual processes
• V_KPI_Tops_Overdue_ByStep – the view presents statistics about the number of tasks assigned to the selected users that are already overdue, divided into individual steps
• V_KPI_Tops_Overdue_Global – the view presents statistics about the number of tasks assigned to the selected users that are already overdue in terms of the entire system

Administration views

During the installation of WEBCON BPS in the database, five views are automatically created:

• V_ADMIN_ActiveTasks – the view presents all active user’s tasks
• V_ADMIN_ActiveUsers – the view presents all users who have done something on the workflows in the last 90 days
• V_ADMIN_ActiveUsersByProcess – the view presents all users who have done something on the workflows in the last 90 days along with information about the number of these instances
• V_ADMIN_Licenses – the view presents the number of users who have done something on the instances in the system in the last 90 days
• V_ADMIN_Timeouts – the view with statistics of timeouts created in the system

Action logs view

One of the views created by the system is the view showing the logs of the actions performed. It is available under the “WFLogs” name and contains the same information as in the “WFActionExecutions” table (see -> WFAction Executions table description).