Using CONCAT within the LIKE function to filter Data source elements

Applies to version: 2023 R3 and above; author: Krystyna Gawryał

Introduction

Version 2023 R3 introduced the ability to use the CONCAT function within the LIKE function to create advanced filter conditions on Data sources. This is particularly applicable when configuring Choice fields, allowing the list of returned values to be limited to those required by the user.

This article provides a description of the functionality along with a business case for its use.

Basic definitions and functional assumptions

LIKE and CONCAT are functions that can be used in WEBCON BPS to create business rules, form rules, SQL queries, and to retrieve values from data sources.

CONCAT combines text values into a single string and is available among the functions dedicated to text. With CONCAT you can retrieve global constants, process constants, form field values, and combine them into a more complex text.

Example: CONCAT [‘WI’][‘_‘][‘Wisconsin’] returns “WI_Wisconsin”.

LIKE checks whether a text (e.g. a form field value) contains the given text value. The operators that can be used when defining a value are:

• % – replaces any string of characters,
• _ – replaces a single character,
• [] – replaces a single character within the specified range, 
• [^] – replaces a single character outside the specified range. 

Example: [‘Data for 2022/2023’] LIKE [‘%2024%’] returns”FALSE”.

Previously, when defining a filter applied to a data source, LIKE could only be used to specify single text values, single form fields or system fields, etc.

By making the CONCAT function available here, it is now possible to combine text from different elements to create more complex filter rules.

Below is an example of using the updated LIKE function in a business environment.

Business case

A manufacturer of basic lighting luminaires prepared a product catalogue for their customers, listing all the luminaires in the Ultra LED Panel family. Each product in this family has been registered in the system using a separate form, has its own unique serial number, and is provided with a technical datasheet in the catalogue, which is attached to such a form.

Example of luminaire registration form

The luminaire serial number consists of a sequence of alphanumeric characters separated by dashes. Each section of the serial number (between the dashes) is a designation of a luminaire parameter as indicated on the form. Below is an example of a serial number with explanations:

① – luminaire type, available options: RC – recessed, SP – suspended, SM – surface-mounted

② – power supply unit, available options: SE2 – electonic, SD2 – DALI

③ – luminaire shape and dimensions (in mm), available options: SQ595 – cassette luminaire (square), standard version; SQ622 – cassette luminaire (square), German version; RE295 – rectangular luminaire, dimensions: 295 x 1125 mm

④ – power, available options: 28W, 31W, 34W

⑤ – correlated color temperature (in Kelvins, K), available options: 3000, 4000, 6500

⑥ – luminaire color, available options: WH – white, BL – black, GR – gray

⑦ – glare factor, available options: U19, U22, U25.

Therefore, the serial number of the product in the example indicates that it is a recessed (flush) luminaire with electronic power supply unit, in standard cassette version, 34W, correlated color temperature of 3000 K, white housing, and with a glare factor of U22.

Taking into account the above parameters and their possible combinations, the product list will contain exactly 1,458 luminaires:

However, a customer ordering luminaires for their facility would like a quotation for surface mounted (SM) luminaires with electronic power supply unit (SE2), for cassette mounting (SQ595, SQ622), with a power of 34W, any correlated color temperature (3000, 4000, 6500), in a black housing (BL), and with any glare factor (U19, U22, U25).

The manufacturer's employee responsible for preparing the offer documentation has at their disposal the "Offer" form with the Luminaire Choice field, for which the above "ULTRA LED PANEL – List of luminaires" is the data source. It is worth mentioning that after selecting the value of this field, i.e. the luminaire serial number, the values of the Product name and Unit price in the Quotation item list are automatically filled in, and the technical data sheet of the specified luminaire is downloaded and added as an attachment to the offer.

Example of offer form

However, the employee wants to be able to select only those luminaires on the form that meet the customer's requirements. It is therefore necessary to configure a suitable filter query for the Luminaire choice field, so that only those values of the data source column that meet the specified conditions are returned:

The most effective way to create such a filter condition is to use the LIKE function, which, using CONCAT and the appropriate arguments and operators, will return only those of the 1,458 luminaire serial numbers that relate to the customer-defined parameters.

This query could look like the following:

When testing the query, 18 items are returned and, when checked, it is seen that they all meet the assumptions defined by the customer.

After saving the settings, the values available in the Luminaire field in the form meet the defined criteria:

Summary

Using the CONCAT function within the LIKE function allows users to create precise queries containing text values, operators, and values of different form field types. This solution streamlines the process of filtering data sources and creating rules to automate work with large, complex and dynamically changing values and to manage multi-record databases more efficiently.