WFHistoryElements and WFHistoryElementDetails tables description

Applies to version: 2020.1.x and above; author: Franciszek Sakławski

Introduction

When designing processes and individual workflows in WEBCON BPS is it good to know how data is stored in the table, what relationships are and how particular information is displayed on the form.

The following article describes the WFHistoryElements and WFHistoryElementDetails tables.

Table description

The tables store data o the edition of the instances and changes of the values when going through the path. The WFHistoryElements table stores information about the instance and form fields value and the WFHistoryElementDetails table – information about values from the “Item list” form field.

Every time when the instance goes to the next step in the workflow, in the WFHistoryElements table a new entry will be created. If the form has the “Item list” form fields, the same will happen in the WFHistoryElementDetails table.

Relation in the table

The WFHistoryElements table contains all data about the instance, including user input and system data.

Selected keys in the WFHistoryElements table:

• WFH_ID – the main key (row ID)
• WFH_OrgID – the instance ID, the main key (WFD_ID) in the WFElements table
• WFH_DTYPEID – form type ID, the main key (DTYPE_ID) in the WFDocTypes

The WFHistoryElementDetails table contains all data about item lists in the instance, including user input and system data.

Selected keys in the WFHistoryElementDetails table:

• HDT_ID – the main key (row ID)
• HDT_DETID – the item list row iD, the main key (DET_ID) in the WFElements table
• HDT_WFDID – the instance ID, the main key (WFD_ID) in the WFElements table
• HDT_DTYPEID – the form type iD, the main key (DTYPE_ID) in the WFDocTypes table

Data storage

The WFHistoryElements table stores all information about the progress of the instance. Selected columns of this table are presented below:

1. WFH_Version – informs which entry is which version of the instance. After starting the workflow, the first entry in the WFHistoryElements table will have always WFH_Version = 1.
2. WFH_STPID – informs at which step, the instance was in the given version
3. WFH_LeftBy – informs who changed the instance version
4. WFH_LeaveCurrentStepPath – informs which path the instance in the given version was moved
5. WFH_LeaveCurrentStepDate – the date and time when the version has been changed

Business case

A simple “Car Fleet Management” workflow was created. The workflow is in the second step – you can see it on the right side on the screen or by clicking the “Clock” icon. The system will move us to the history of the element edition.

Fig. 1. The instance form

Seeing the history of the instance, you know by whom it was edited and what paths the workflow went through and what fields were changed.

Fig. 2. The history of the instance

The workflow was changed only once. In the WFHistoryElements table two entries were made – to check this go to the SSMS (“SQL Server Managements Studio”) and create a query about this particular table. Check also the WFElements table – they should be one entry with the current data.

Fig. 3. The query to the tables

If you query the WFHistoryElements table, knowing only the instance ID, you should limit the search to the WFH_OrigID = instance ID.

The table not only stores the entries of the form changes on the transition to the step, but also the information after saving of the instance.

Fig. 4. The instance form

After clicking the “Save” button, the system will save the changes and add an entry to the WFHistoryElements table.

Fig. 5. The query to the WFHistoryElements table

Another entry was added in the table – this entry also exists on the instance history card:

Fig. 6. The history of the instance

To verify the correctness of the displaying the history, the following SQL query was created:

Fig. 7. The SQL query to the WFHistoryElements table

The WFH_LeaveCurrentStepPath and WFH_LeaveCurrentStepDate tables have the NULL values in the 2 and 3 rows. This is due to the fact that the transition between them was not executed by using the workflow moving, but by saving the instance from the form level.