Applies to version: 8.x; Author: Bartłomiej Spyrka
This document contains piece of information about data kept in workflow database.
Document is focused on two primary SQL tables:
Table contains information about data provided by uses due to filling the forms (process).
Table contains data provided by users in Field ‘Item List’. Because of Item list ability to storage multiple rows, this data has to be saved in dedicated SQL Table.
All of information contained in this document bases on sample process containing different type of data (text, numbers, choice fields etc.). In the example we will show how to find database columns according to Webcon Designer steps, fields’ workflows configuration.
2. Process – browser view
To make some things simple, we have prepared sample process and to make things clear it will be used and screened in next sections.
3. Process – BPS Designer view
Screen below shows view of process configuration in BPS Designer Studio.
According to any process created in BPS, each includes some stages like:
1) Creating order
2) Manager approval
Steps listed above comes from single workflow. To get information about current step we need to know ‘Step ID’.
3.3 Document types
Document types are used to filter data from databases when we want to report only a part of database, for e.g. only orders without Invoices or HR things. Thanks to that we may focus on essentials.
Each field is represented in two ways:
1) On the view side – this is the view that user sees at browser (text fields, choice fields, numbers etc.)
2) Second side is data kept in database. Each field has its own representation in SQL database tables.
- Fields are kept in WFElements table
- Item lists elements are kept in WFElementDetails table
3.5 BPS Designer view
Now we have first simple division made on two tables from two sides – user view and studio view. It will help us to define more detailed information.
To find out where are Requestor or Manager information, we need to use Studio interface. Simply set focus on field – Requestor:
At the screen above we have three tags:
1) The field which is currently focused
2) Name of the database column in WFElements table
3) ID – this is the unique value of field definition
It is worth to mention that ‘Item list’ field (only definition) is kept in WFElements table, but its columns are kept in WFElementDetails – see below.
When we talk about Item lists we need to use WFELementDetails table
1) Item list – field definition
2) ID – field ID – it is needed when multiple item lists at one document to separate data.
To get information about data kept in ‘Item lists’ column we need to set focus on field ( column for e.g. Category)
1) Name of the column in Item list
2) Database column In WFElementDetails SQL table
3.6 Common view
Ok, as we now know both views – user and configuration, it is good to see pieces together
4. Database queries
Once we know where the data is we can write some simple queries in Microsoft Management Studio to get them and use for reporting.
Since 8.0 version is released Workflow database is separated between three databases:
3) Content (business data)
To get business data, for reporting we need to use last database listed below. Database name is set in workflow installation process.
4.3 Extracting data
4.3.1 WFElements table
When we want to get simple data like Requestor, ID (document number unique for whole database represented by WFD_ID column), document Signature or Order time we should write following Query:
Second part contains data that user provided in form fields (without Item lists). As you can see, at the Requestor, Manager or CCPerson we have field construction
- ID – unique for data source
- # – workflow separator between a and c
- DisplayName – value displayed in user view
To make data look better we can use some standard database functions:
1) Dbo.clearwfelemid – returns data located between first encountered # – sign in provided string as a parameter
2) Dbo.clearwfelem – returns data located after first encountered # – sign in provided string as a parameter
Below you can see an example of function usage.
4.3.2 WFElementsDetails table
To get data from ‘Item lists’ we need to write query according to WFElementDetails table
As you can see query above is similar to query written in WFElements table .The difference is on:
1) Other table in WFElementDetails
2) WHERE clause – because, WFElementDetails is related to WFElements. Both tables are connected on keys
- WFD_ID In WFElements is located in DET_WFDID (like parent) in WFElementDetails table
- DET_WFCONID – located in WFElementdetails – it is the item list ID (please see Item list – definition in Studio View)
To get data from item list we can write following query
To get data from both tables at once we can write SQL query with simple join formula, which looks as follow:
4.3.3 WFSteps table
To get information about current status of order we can use WFD_STPID (please see at Figure 3. Workflow steps and IDs) column from WFSteps and join to WFSteps SQL table as follows: