Applies to version 2020.1.x, author: Marcin Pisarek
When designing processes and individual workflows in WEBCON BPS, it is good to know how data is stored in the tables, what are the relations between them and how the particular information is displayed on the form.
The article describes the table that stores header data – WFElements.
Relations in the table
The WFElements table contains data about all instances (forms) in the system. They store both system data and form field values defined in the processes. Each workflow instance is identified by a unique ID (WFD_ID).
Fig. 1. The form – system and configured fields
On the screenshot above (Fig. 1) data has been marked with the following colors:
- Red – system fields e.g. instance ID, instance number, process, workflow, current step, form type, modification date, created date, author, modified by, current version, comment field.
- Blue – the fields defined in the process (configured) e.g. text fields, numerical fields, choice fields.
Keys in the WFElements table:
- WFD_ID – main key (instance ID)
- WFD_STPID – workflow step ID in which is the given instance. The main key (STP_ID) in the WFSteps table
- WFD_WFDID – parent instance ID. The main key (WFD_ID) in the WFElements table
- WFD_WFRID – registration point ID. The main key (WFR_ID) in the WFRegisterPoints table
- WFD_COMID – business entity ID. The main key (COM_ID) in the Companies table
- WFD_DTYPEID – form type ID. The main key (DFTYPE_ID) in the WFDocTypes table
Depending on the given form field type, data is stored in the appropriate field in the table. This field is invariable in the table and automatically assigned when you add the form field in the system.
Fig. 2. The form field – fields in the table
Below, the selected types of form fields along with their equivalents in the WFElements table are described:
- Single line of text – AttText column, varchar(255) type
- Multiple lines of text – AttLong column, text type
- E-mail – AttLong column, text type
- Yes/No choice – AttBool column, bit type
- Choice field – AttChoose column, varchar(1000) type
- Date and time – AttDateTime column, datetime type
- Person or group – AttPeople column, varchr(1000) type
- Integral number – AttInt column, int type
- Floating-point number – AttDecimal column, decimal(21,6) type
A simple Car Fleet Management workflow has been used in this example. It consists two workflows: Car Management and Use Registry – they are used to register a car and submit the requests for their retail:
Fig. 3. The configuration of the “Car management” workflow
The first step is the “Registration” step in which a user fills out data about the car and then, goes the “Move to Car Management” path which causes saving data for the given instance.
Fig. 4. The form – filling out necessary data
Data are presented in the following way:
Fig. 5. The form
The values of some of the fields (dates, numerical fields, choice fields…) are stored in the WFElements table in a different format from the one in which they are displayed on the form of the given instance. The following SQL query returns all the completed fields on the form for the above instance (Fig. 4) – Infiniti registered car.
Fig. 6. The SQL query – storing data about the instance
In this SQL query the “Year of production” and “Fuel type” form field values are saved in the “ID#Name” format – also known as the “BPS format”, it is the combination of the value’s ID and name. It allows you to identify the value by using their unique ID.
For more information about SQL queries see -> https://howto.webcon.com/admins-handbook-useful-sql-functions/ .