Skip to end of metadata
Go to start of metadata

 

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

On this page ...


IULocgo


Filter Data in Table

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:

idmachine_namearea_number
1Conveyor1
2Press2
3Tank1
4Packer3
5Loader3
6Oven 13
7Oven 22
8Wrapper1
9Mixer3
10Cold Storage2
11Dryer2

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:

      ValueLabel
      1Area 1
      2Area 2
      3Area 3
      0All 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.


Related Topics ...

 

  • No labels