Data operations

Applies to version 2020.1.3; author: Konrad Wojtycza

Introduction

In WEBCON BPS data stored e.g. in the external database can be retrieved and displayed by:

1. Defining data connection and SQL query
2. Creating a data source and using filters

This article describes both approaches to use data in WEBCON BPS, differences between them, and problems of their configuration.

Example data and workflow

The article uses data provided by EMSI, Economic Modeling Specialists Inc. that contains information on the change in the employment structure in UK cities in 2011 and 2014, broken down by industry.

The selected datasheet that contains over 1300 records, has been imported to the UK_JOB_CHANGE database (created and configured for this article).

The following databases have been configured in the test workflow:

1. Data table – connection – the Data table form field with the directly configured connection to the database containing the example data. The SQL query defined on the form field returns all data from the EMSI_JobChange_UK table.
2. Data table – detailed query – the Data table form field with the directly configured connection to the database. The SQL query returns data selected by the detailed query.
3. Data table – data source – the Data table form field with the directly configured connection to the database. Data displayed in the table is limited by the use of the filter set on this form field with the detailed data filter.

Fig. 1. The tested workflow and form fields

Configuration

Connection to the database – general SQL query

In the “Data source” field, select the connection to the database from the “Connections” tab (in this case – WDR04). In the “SQL query” field, define a query to be sent to the database. In this example, the query returns all data from the EMSI_JobChange_UK table - each time the form is loaded, the contents of the entire data table will be downloaded.

Fig. 2. The configuration of the data table with the directly connection to the database

For queries that return very large amounts of data, you may experience performance issues when loading the form and viewing it. The solution is to enable the paging option on the Data table form field – in the “Advanced configuration” tab, select the “Paging” option and set the number of rows displayed on a single page (by default – 10).

Fig. 3. Enabling of the paging option

The form view with paging:

Fig. 4. The form view with the paging option

Connection to the database – SQL query with a condition

The configuration in the present example (Fig. 5) differs from the one presented in Fig. 2 in the narrowing of the query defined in the "SQL query" field by adding the WHERE condition. Only rows for which the Industry = Education value will be retrieved from the EMSI_JobChange_UK table.

Fig. 5. The configuration of the data table with the directly connection to the database

The data on the form:

Fig 6. The presented data on the form

Due to form loading performance, a good solution is to narrow down the amount of data that is selected through the SQL query. You can also do this using the data source with a filter (Fig. 7).

In the case of configuring the connection to the database and defining the query (Fig. 5), you can edit the query only on the form field belonging to the specific application, unlike the solution using the MSSQL data source (Fig. 8) which will be generally available in the Data sources -> MSSQL database.

Fig. 7. Data source with narrowed database query

Data source with the filter

You can configure the MSSQL data source in the Data source -> MSSQL database – set the connection to the database (the “Connection” field) and SQL query (the “SQL query” field).

Fig. 8. The UK Jobs Change data source

The above query will return all records from the EMSI_JobChange_UK table. When you use the data source, records may be narrowed down with the filter (Fig. 9).

From the point of view of the form's performance, this is not an optimal solution, because when loading the form, all records from the EMSI_JobChange_UK table will be downloaded first, which will be filtered only in the next step, and the filtering result will be displayed on the form. However, from a business point of view, it is not necessary to know the SQL syntax when using filters.

Fig. 9. The configuration of the data table

From the point of view of the presented data, applying the filter from Fig. 9 will return the same data as using the query from Fig. 7.

Fig. 10. The data presentation

Summary

The table below presents a comparison of the methods of data access configuration in WEBCON BPS.