TcxFilterControl and TcxDBFilterControl - advanced queries in the hands of the user

In the work of almost any database, there comes a time when showing all the data does not make sense, and sometimes even the possibility. And here everyone starts enthusiastically to make all sorts of forms for the initial selection of data, often with presets, for example - data only for today, only in “working” status, etc. If we are talking about displaying a list of complex documents, for example, invoices with 2 dozens of fields, then the window for setting conditions can have several tabs and still not solve all problems. But, as is often the case, everything is stolen thought up to us;)

Fig.1 TcxFilterControl in work


The described problem is wonderfully solved by TcxFilterControl and TcxDBFilterControl from DevExpress. These 2 components are visual condition builders with the ability to get the corresponding SQL. Actually, DevExpress has a .Net product line, roughly the same as what they do under Delphi, but it wasn’t necessary to use it, so further only about the VCL branch ...

Fig.2 TcxFilterControl / TcxDBFilterControl in the designer.

In contrast to the independent implementation of the “search wizard”, this solution will be more scalable at first - when adding new fields in the query that we filter, the form must be completed, and FilterControl will pick up the fields right away. Additional configuration will be needed only if we want to see the list of substitutions in FilterControl. The main difference between these controls is that TcxDBFilterControl connects to TDataSet, and TcxFilterControl c TcxGridDBTableView. So TcxDBFilterControl can be used even if the project does not have a cxGrid, which can be useful when upgrading old projects.
')
In TcxFilterControl and TcxDBFilterControl, the conditions are built in a tree-like form, so it is quite easy to understand which text the component will generate. To add a condition at the current level, select the item “Add condition”.

Fig.3 adding a condition to FilterControl

To add a sublevel, select "Add group".

Fig.4 adding a group to FilterControl

What is captivating is that out-of-the-box field substitutions work, condition substitutions (equal / not equal, more / less, for dates there is generally a huge selection of preconditions), substitution of the desired values ​​from lists, calendars, ...

Fig.5 dropdown list of fields


Fig.6 drop-down list of conditions on the field of type Date


Fig.7 drop-down list of employee names from the reference table

You can also specify various Boolean operations for values ​​within a group and between condition groups.

Fig.8 Boolean operations for values ​​within a group

The result of TcxFilterControl / TcxDBFilterControl is that a string is generated that is suitable for use in the Filter property of any data source (DataSet's) - so we get a local filter. With some reservations (the uniqueness of the field name within the current query), this condition can be added to the WHERE part of the query. For example, for Figure 1, our FilterControl of class TcxFilterControl when accessing FilterControl.FilterText returns

((ORDER_STATE = 1) OR (ORDER_STATE = 2) OR (ORDER_STATE = 3))
AND (EMP = 2304)

which is suitable for both local filter and database query.

The bottom line is by doing something that programmers who use powerful tools are often reproached for by putting a component on a form;) we gave the user the opportunity to build queries in a very flexible way. The downside is 2 - an ascetic appearance and some complexity for untrained users. Pros are a scalable and flexible means of generating conditions at minimal cost.