Using of the SQL table form field

Applies to version: 2020.3.x and above; author: Michał Kastelik

Introduction

The data table form field allows you to display data in the form of a table with defined columns. This data can be provided from any data source defined in WEBCON BPS Designer Studio.

This article describes the configuration and data relations based on the “Employee’s report” form field in the “Expense report” application template.

The form field displays the previous requests of an employee and allows them to be opened in the preview window on the right side.

Configuration of the form field: Data table and filter

To work properly, the data table form field requires the definition of a valid data source – they should be defined in the “Data source” tab in Designer Studio and then indicated in the form field configuration.

In this example, it is the “[Expense report] Expense history” BPS internal view data source.

The following columns have been defined:

• Instance number
• Step
• Assigned to
• Days in the step
• Title
• Total
• Employee

Since the previous requests/instances of a given employee are to be displayed on the form, data from the data source must be related to the currently open instance form – in the example there is the “Employee” field.

In the configuration of the data table form field there is a “Filter” field where the following query has been defined.

To create a filter using fields from the data source, select the “Values” tab and then expand the “Data source columns” group. Then all columns from the data source indicated on the previous screen will be displayed. The access to the form field is possible after expanding the “Form fields” group.

The described filter is defined in the following way:

• Condition 1: The Employee ID in the data source is to be equal to the Employee ID in the opened form.
• Condition 2: The ID of the currently opened instance must be different from the ID in the data source (we do not want to display the current instance in the data list on the form).

Advanced configuration: Displayed columns

Enter the “Advanced configuration”.

Displaying columns

The upper part of the configuration window displays all columns defined in the associated data source. To make a given column visible on the form, select the “Show column” field. You can set the order of the columns using the arrows on the right.

Instance hyperlink

If a given form field column is to be a link to the instance at the same time, the “Instance hyperlink” field should be checked. In the case of the described configuration, the instance hyperlink is specified in the “Signature” field. After clicking the value in this column, the instance preview will be opened on the right side of the form.

After selecting the column in the “Instance link” option enabled, you can use the button with three dots to open the advanced configuration of this option. In the “Working mode” field you can select whether the element should be open in a new or an existing window.

There is also the ability to configure how the hyperlink is opened and how it is to be displayed.

Summary row

For a numerical column, you can display the sum or average in the table summary. To enable this option, select the “Show summary row” and then choose something in in the “Aggregation type” column, e.g. “Sum”.

Paging

In sources that contain lots of elements, the number of displayed rows can be divided into tabs. This option is useful when you want to keep the appearance of the displayed form.

Hiding names and descriptions

Depending on the requirements, there is the ability of displaying or hiding the following columns:

• Column names -> the “Show column names” option
• Form field description -> the “Show form field description” option
• Form field names -> the “Show form field names” option

When the form field is placed inside a from field group, hiding the form field name saves space on the form. If there is a need for an additional description of the data displayed in the form field, you can complete the form field description and select the “Show form field description” option. For new applications, this makes it easier for new platform users to get to know the application.

Data source options

The form field configuration contains additional options for data sources:

• Query data source if variables return empty values – when in the query there are empty tags (e.g. Employee does not return any result), the system does not signalize a form field error – It is simply ignored.
• Load on demand – disables the option of automatically loading data into the form field when opening the form. To load data, use the button on the upper right corner; this button also allows you to refresh data on demand.

• Allow Excel export – after enabling this option, an additional button will be displayed that exports displayed data to the Excel file.

The data table form field can be very useful in applications that have several related workflows and there is a need to display data from other workflows on one form – juxtaposing a plethora of useful data on one page. The possibility of using any SQL source as a data source in the form field allows you to build forms that integrate information from many different databases.

The Expense report application template is available for download here: Expense report (webcon.com)