# Filtering table from multiselection dropdown

**URL:** <https://forum.inductiveautomation.com/t/filtering-table-from-multiselection-dropdown/91859>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [June 14, 2024, 10:45am UTC](https://forum.inductiveautomation.com/t/filtering-table-from-multiselection-dropdown/91859 "2024-06-14T10:45:57Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [June 14, 2024, 10:45am UTC](https://forum.inductiveautomation.com/t/filtering-table-from-multiselection-dropdown/91859/1 "2024-06-14T10:45:57Z")

</div>

Hello,

I keep looking for solutions to filter a large table. Based on previous idea from @Transistor

I have a table  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/1/4190f0f4b04830b7e6ef2218b938ffbcf0469bda.png)

I created a custom property custom.data so I can filter the data from the named query before I show it on the table.  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/f/ff95d2bea727ab412ea77b11f53a436e4a766f05.png)

I added a multiselection dropdownto the view with values that match the ones in one of the table columns  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/7/b750fd78ab15589d031ff0b57eb079c1e74e202c.png)

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/b/db1be16e87ca52234627969af3159faf6dcf5221.png)

The column wich match the values of the multiselection dropdown is this one

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/f/2f309dbb5f9ec70996711ee5fc5650cdbe13957d.png)

this one column  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/1/e1746922b2d35752336bfe59ec7d2d57f16507dc.png)

Is there a way I can filter the table with the multiselection dropdown?

thanks

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [June 14, 2024, 11:11am UTC](https://forum.inductiveautomation.com/t/filtering-table-from-multiselection-dropdown/91859/2 "2024-06-14T11:11:07Z")

</div>

(For anyone else, this is a follow up to [Checkbox filter, selected box and non selected boxes - #9 by Transistor](https://forum.inductiveautomation.com/t/checkbox-filter-selected-box-and-non-selected-boxes/91754/9))

Here are two ways:  
First, create a custom property `custom.tableFilter` on the view and make a bidirectional binding to it from the dropdown `props.value`. (This avoids all the getParent / getSibling syntax and will make your script less "brittle".)

1: Use the custom property as a parameter in the SQL query. This will automatically refresh the binding if the dropdown selection changes.  
2: Use the custom property to trigger a script transform on the SQL results. Here's how I would do that:

- On the Table component add a custom property, `custom.data`. Create a query binding on that so that it retrieves all the records.
- On `props.data` create a property binding to `view.custom.tableFilter`. (Now when the dropdown selection changes this binding will refresh.)
- Add a Script Transform to script the filter. The data will be available from `self.custom.data` and the filter will be passed as `value` into the script.

Have fun.

---

<div class="post-metadata">

**Author:** ![Brandon\_Peterson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/brandon_peterson/32/32960_2.png) [@Brandon\_Peterson](https://forum.inductiveautomation.com/u/Brandon_Peterson)\
**Post date:** [June 14, 2024, 12:25pm UTC](https://forum.inductiveautomation.com/t/filtering-table-from-multiselection-dropdown/91859/3 "2024-06-14T12:25:23Z")

</div>

I would slightly modify step 2 here.

We want changes from `view.custom.tableFilter` and the table's `self.custom.data` to both send updates to the table's `self.props.data` property.

So instead of a single property binding on `self.props.data` to `view.custom.tableFilter`, use an [expression structure](https://www.docs.inductiveautomation.com/docs/8.1/ignition-modules/perspective/working-with-perspective-components/bindings-in-perspective/expression-structure-bindings-in-perspective) binding:

```json
{
	'tableFilter': '{view.custom.tableFilter}',
	'tableData': '{self.custom.data}'
}

```

_Then_ create the script transform to process the data (`value.tableData`) and your filter values (`value.tableFilter`).

This way, when your filter updates or the data itself updates, then your table will update accordingly.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [June 14, 2024, 12:28pm UTC](https://forum.inductiveautomation.com/t/filtering-table-from-multiselection-dropdown/91859/4 "2024-06-14T12:28:11Z")

</div>

Good point, Brandon. I was assuming the underlying data would be fairly static, had considered the expression structure but didn't follow through with it. Your way is better.

---

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [July 11, 2024, 10:16am UTC](https://forum.inductiveautomation.com/t/filtering-table-from-multiselection-dropdown/91859/5 "2024-07-11T10:16:29Z")

</div>

Hello!,

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/1/f14031940e69a9309cc2a32cf28df69a8c189c88.png)

and I have my

Table custom.data binded to my namedQuery

SELECT DISTINCT  
Asset.asset AS "Activo",  
Asset.assetName AS "Descripcion",  
Asset.EquipmentType || ' \<' || EquipmentType.EquipmentTypeName || '\>' AS "Clase equipo",  
Asset.parentAsset AS "Activo padre",  
Asset.businessUnit AS "Ud. negocio",  
ALD.Value AS "Dato de nivel",  
Asset.companyLevel AS "Nivel",  
Asset.priority AS "Prioridad"  
FROM  
Asset  
LEFT JOIN EquipmentType ON Asset.equipmentType = EquipmentType.EquipmentType  
LEFT JOIN AssetLevelData ALD ON Asset.asset = ALD.asset  
WHERE  
Asset.company = 'ORANGE' AND EquipmentType.company = 'ONE'  
AND (:tableFilter IS NULL OR Asset.businessUnit IN (:tableFilter))  
ORDER BY  
....  
ELSE 1  
END,  
Asset.businessUnit,  
Asset.parentAsset,  
Asset.asset

and parameter {parent.custom.tableFilter}

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/0/70a80d3dffefbd351889e2360b8907b64c963b63.png)

then my table props.data binded to this custom.data

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/b/cb18316214f07146af6d53987839ce7827a7e3a7.png)

Then I have my dropdown with a dataset in options  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/a/9a9f872c777faabaf8d6de9c80b3d445621745be.png)

and the value binded to the TabContainer.custom.tableFiter Bidirectional

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/9/892c452222846c53a37a347215192ffe6b04c41b.png)

Then I have my TabContainer.custom.tableFilter with one fixed value to show something in the table at the begining  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/4/c481bf8315124a79974d32e126a192721157856b.png)

but the filter doesn´t seem to work, any idea of something I may be missing?

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [July 11, 2024, 11:21am UTC](https://forum.inductiveautomation.com/t/filtering-table-from-multiselection-dropdown/91859/6 "2024-07-11T11:21:40Z")

</div>

1. Why use a bidirectional binding on the dropdown's value ? Use a unidirectional binding to the dropdown's value on `custom.tableFilter` instead
2. You're using `in` to filter in your query. Why ? The parameter is a string, use `=` or `like`, not `in`.

---

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [July 23, 2024, 9:13am UTC](https://forum.inductiveautomation.com/t/filtering-table-from-multiselection-dropdown/91859/7 "2024-07-23T09:13:58Z")

</div>

Hello, I adding a custom property on my view wich is tableFiler-1,

when I choose the whole custom property so I can **filter multiple values with the dropdown** the filter doesn´t work.  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/5/6564b09d32e1d912adcb5180ca1bf5bccce9b27b.png)

When I choose just one element of the array the filter works  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/9/d9fc90d7c9eefa837f55e059863d902963b0a0a4.png)

The table where I get the binding to filter the named query is like this

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/e/8ee10939b773aaab7667c3267fee4e2686d219a4.png)

and the named query is like this:

```python
SELECT DISTINCT
    Asset.asset AS "Activo",
    Asset.assetName AS "Descripcion",
    Asset.EquipmentType || ' <' || EquipmentType.EquipmentTypeName || '>' AS "Clase equipo",
    Asset.parentAsset AS "Activo padre",
    Asset.businessUnit AS "Ud. negocio",
    ALD.Value AS "Dato de nivel",
    Asset.companyLevel AS "Nivel",
    Asset.priority AS "Prioridad"
FROM
    Asset
LEFT JOIN EquipmentType ON Asset.equipmentType = EquipmentType.EquipmentType
LEFT JOIN AssetLevelData ALD ON Asset.asset = ALD.asset
WHERE
    Asset.company = 'ORANGE' AND EquipmentType.company = 'OEA'
    AND (:tableFilter IS NULL OR Asset.businessUnit IN :tableFilter)
ORDER BY
    CASE
        WHEN SubStr(Asset.businessUnit, 1, 3) IN ('CAT', 'BAL', 'MUR', 'VAL', 'AND', 'ARA', 'AST', 'CAL', 'EUS', 'GAL', 'NAV', 'RIO') THEN 2
        WHEN SubStr(Asset.businessUnit, 1, 5) = 'MAD-F' THEN 0
        WHEN SubStr(Asset.businessUnit, 1, 7) = 'MAD-PAR' THEN 0
        ELSE 1
    END,
    Asset.businessUnit,
    Asset.parentAsset,
    Asset.asset

```

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/5/35b2b12d4b6b32f2527165f8118ff91f065fd84b.png)

Basically when I choose just one item the dropdown filter works, when I choose multiple ones it doesnt.

I´m not sure why this happen, any ideas?

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [July 23, 2024, 11:16am UTC](https://forum.inductiveautomation.com/t/filtering-table-from-multiselection-dropdown/91859/8 "2024-07-23T11:16:33Z")

</div>

You cannot pass lists into single JDBC parameters.

If you script your query with `runPrepQuery` instead of using a named query, you can dynamically generate question mark placeholders for all of the list elements, then pass the correct length argument list.

---

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [July 23, 2024, 11:22am UTC](https://forum.inductiveautomation.com/t/filtering-table-from-multiselection-dropdown/91859/9 "2024-07-23T11:22:59Z")

</div>

Thanks a lot, I try it!