---
title: "Filter Rows in a Table"
---

# Filter Rows in a Table

> 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.

## 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.

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

```sql title="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.

```sql title="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 title="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](platform\sql-in-ignition\named-queries\using-named-queries-example\using-named-queries-example.md).
    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 title="SQL - Selecting Values from a Table and Filtering on a Dropdown"
    SELECT * FROM machines WHERE area_number = :dropdownValue OR 0 = :dropdownValue
    ```

    ![](img1.png)

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. 

    ![](img2.png)

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.

    ![](img3.png)