Filter Data in Table

Filtering table data by using a drop-down box is possible, if the Table component's Data property is bound to a SQL query, thus dynamically modifying the WHERE clause of a query, or creating a whole custom WHERE clause. To implement this solution, you must have a Table component and a Dropdown List component on a window.

Many SQL queries have WHERE clauses that filter the result returned from the database. When a SQL query examines the contents of a table it evaluates each row against the WHERE clause. If the WHERE clause is true, the row is included in the data-set, otherwise, it is not included in the data-set.

With SQL queries you can use the WHERE clause to your advantage when building interactive screens. For example, the following SQL query will filter the result set in such a way that the data-set returns only tanks from Area A.

SELECT * FROM tanks WHERE area = "Area A"

While the following query will return everything, but still will evaluate the query's WHERE clause.

SELECT * FROM tanks WHERE 1=1

In this case 1=1 will always evaluate as true, therefore the query will return every row.

Imagine if you had a drop-down component near the table, and the drop-down component's Data property was populated with the following data:

Value
Label

0

Area A

1

Area B

2

All

This data can help to create the WHERE clause. The first step is to create a Custom property on the drop-down list.

  1. Right-click on the drop-down component and select Customizers > Custom Properties.

  2. Create a new property and name it WhereClause, and it must be a String data type.

  3. Click OK.

  4. Select the binding icon for the new Custom property and select Expression binding.

  5. The expression binding should look something like this:

if(
{Root Container.Dropdown.selectedStringValue}="All", //Evaluate this and return a true or a false.
"1=1", //Return 1=1 if the statement was true.
"Area = '"+{Root Container.Dropdown.selectedStringValue}+"'") //Otherwise return a different where clause.

When you select a different option from the drop-down component the binding gets re-evaluated on the table's data binding resulting in the query executing with the new WHERE clause.

You may want to turn Polling Mode off on the tables Data property binding in order to limit the periodic querying of the database.