Applies to version 2016.x; Author: Paweł Snoch
In the upcoming BPS 2016 (the version that is coming after 8.3) the SQL Creator tool for the SQL Grid form field has been revamped. In this short article, I will demonstrate how to quickly create a query for displaying a report of related task elements on the current form.
Let’s imagine a workflow for registering and servicing damaged equipment.
Fig. 1. Workflow for registering damaged pieces of equipment.
On the second step (Realization) we would like to have an overview of all active workflow elements registered by the same user who also created and registered the element we are currently viewing.
Using the Creator tool
Add a new SQL Grid form field. At the bottom of the data source section, find the Creator button. This will open a new window – thanks to which we can quickly generate a section of the query we need.
As the Generation mode select Selected child elements with field relation. As the Process, select the one to which the workflow in question belongs. Now in the Relation section – for our scenario we would like to view elements created and registered by the same user, so here we selected the Submitter form field. The last step is to select form fields which we would like to see on the report, as an example:
System fields: Assigned to, Created date
Form fields: Submitter, Title
Clicking the Generate button will construct an appropriate script in the SQL Query field.
Fig. 2. SQL Query creator
Save the process, and we can now go to the form to see if we got the expected results.
Fig. 3. Form view
The Creator allows us to quickly create SQL queries. But if we want to customize the report or include more conditions, we will need to edit the queries manually.
In our example, we wanted to add the option of narrowing down the displayed elements to those assigned to a selected user, and also display only those tasks that are active (i.e. exclude those residing in the end step).
We would need to start by adding a new Choice field with an active Change of value causes postback – name it something like Assigned person filter. Now go to the configuration of this workflow and go to Fields matrix to make this form field visible on the Realization step.
Now we must add a segment to the query that specifies that the content should be filtered according to the value found in the Assigned person filter form field.
I also included a filter to only display active tasks, this is the result on the form:
Fig. 4. Final form
NOTE: I didn’t include the full query since the purpose of this article was only to introduce the SQL Grid Creator tool. If you would like a full tutorial please let me know in the comments!
One final thing I would like to mention is the Load ID button – If we want to test the generated SQL Query, we need to provide it with a sample Element ID so the system can find its related elements. You can either find and enter one manually, or use the Load ID which will use the most recent element added for this workflow.
Fig. 5. Testing the query using the Creator tool