Filter Rows in a Table

Creating the Components

Filtering table data by using a Dropdown box is possible if the Table component's Data property is bound to a SQL query. We can create a dynamic WHERE clause that will allow us to select all the data or a specific subset of it. To implement this solution, you must have a Table component and a Dropdown List component on a window. With a Dropdown component, we can create an option for each way of filtering the data, each with a unique value associated with it. An additional "all" entry can also be added, with its own unique value.

We can then use the value of the Dropdown to drive the query. When you select a different option from the Dropdown component the binding gets re-evaluated on the table's data binding resulting in the query executing the WHERE clause with the new parameters. You may want to turn Polling Mode off on the tables Data property binding in order to limit the periodic querying of the database.

Here we can use a value of 1 to show only entries which match that filter column value.

Pseudocode - Filtering for Area 1

SELECT * FROM table WHERE filterColumn = 1

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

Pseudocode - Return All Rows

SELECT * FROM table WHERE 0=0

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

We can combine the logic of each WHERE with an OR into a single query, and substitute in our Dropdown value. The SQL Query binding would then look something like this:

SQL - Where Clause Combined with OR Condition

SELECT * FROM table WHERE filterColumn = {dropdown value} OR 0 = {dropdown value}

This way, the WHERE clause will only be true when the filter column matches with our selected option, or it will return all rows, if we setup our "all" option to have a value of 0.

Example - Filtering Data on an Area Number

We can put together an easy example on how this might with data that stores what machines are in which areas. Our data table should look something like this:

id	machine_name	area_number
1	Conveyor	1
2	Press	2
3	Tank	1
4	Packer	3
5	Loader	3
6	Oven 1	3
7	Oven 2	2
8	Wrapper	1
9	Mixer	3
10	Cold Storage	2
11	Dryer	2

We can then put together the query and components necessary to get this working:

1. Create a new Named Query. Set up security to fit your needs, and name it appropriately. For more information on creating Named Queries, see Using Named Queries - Example.

   1. Create a single Value type Parameter that is an Int2 data type. I called mine dropdownValue
   2. Add in the combined query that we went over above, but use the machine table name and column names.
   SQL - Selecting Values from a Table and Filtering on a Dropdown

   SELECT * FROM machines WHERE area_number = :dropdownValue OR 0 = :dropdownValue

   

2. Create a new Main Window and add a Power Table component and a Dropdown component to the window.

   1. On the Dropdown's Data property, create a dataset that looks like this:

      Value	Label
      1	Area 1
      2	Area 2
      3	Area 3
      0	All Areas

3. On the Power Table component, create a Named Query Binding on the Data property.

   1. Select the Named Query that was created in step 1.
   2. For the parameter, bind its value to the Selected Value property of the Dropdown component, and the click the OK button.

   

4. Put the Designer into Preview mode and try out the Dropdown Filter. You will see that selecting a different value in the dropdown filters the data coming back so that only certain rows are shown.