# Multiple custom filters to a named query

**URL:** <https://forum.inductiveautomation.com/t/multiple-custom-filters-to-a-named-query/93832>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [July 29, 2024, 8:38am UTC](https://forum.inductiveautomation.com/t/multiple-custom-filters-to-a-named-query/93832 "2024-07-29T08:38:35Z")\
**Posts on this page:** 7\
**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:** [July 29, 2024, 8:38am UTC](https://forum.inductiveautomation.com/t/multiple-custom-filters-to-a-named-query/93832/1 "2024-07-29T08:38:35Z")

</div>

Hello!

I have a modified named query

```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 = 'APPLE' AND EquipmentType.company = 'APPLE'
    AND (:tableFilter IS NULL OR Asset.businessUnit = :tableFilter)
    AND (:nivelFilter IS NULL OR Asset.companyLevel = :nivelFilter)
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

```

I added the parameters tableFilter and nivelFilter so I can aply both filters at once

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

-tableFilter custom filter comes from a custom property on my view that comes from a multiselect dropdown

-nivelFilter comes from 4 checkboxes,  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/3/73f73c6027daeb8794820c8046c67e5f2211f012.png)

```python
def transform(self, value, quality, timestamp):

    selected_levels = []
    if value['chk_5']:
        selected_levels.append('5')
    if value['chk_6']:
        selected_levels.append('6')
    if value['chk_7']:
        selected_levels.append('7')
    if value['chk_8']:
        selected_levels.append('8')
    
    
    return ','.join(selected_levels) if selected_levels else None

```

When I choose more than one it doesn´t show any data on the table, just when I choose 1 checkbox it works.

Should I aply the filter to the namedQuery data without a parameter instead?  
as:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/7/270718f25c5d8ad7269683ecc010a4a7baff46a3.png)

```python
def transform(self, value, quality, timestamp):

    output = []
    for row in value['data']:
        nivel = row['Nivel']
        if value['chk_5'] and nivel == 5:
            output.append(row)
        elif value['chk_6'] and nivel == 6:
            output.append(row)
        elif value['chk_7'] and nivel == 7:
            output.append(row)
        elif value['chk_8'] and nivel == 8:
            output.append(row)
    return output

```

Cheers!

---

<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 29, 2024, 9:05am UTC](https://forum.inductiveautomation.com/t/multiple-custom-filters-to-a-named-query/93832/2 "2024-07-29T09:05:05Z")

</div>

> [@Alejandro\_Alaco](#):
>
> When I choose more than one it doesn´t show any data on the table, just when I choose 1 checkbox it works.

Because the equality is not true anymore.  
Let's say `Asset.companyLevel` is 6. When you select the option that corresponds to 6, you get a match. But if you select another option, let's say 8, your parameter becomes `"6, 8"`, and 6 is not equal to `"6, 8"`.  
I don't think there's a perfect solution to this with named queries, but you could explore:

- using a query string parameter
- use `like '%:nivelFilter%'` instead of an equality comparison
- build your query dynamically with a dynamically built list of placeholders then use `runPrepQuery`
- maybe you could solve this with sql scripts and stored procedures ?

---

<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 29, 2024, 9:06am UTC](https://forum.inductiveautomation.com/t/multiple-custom-filters-to-a-named-query/93832/3 "2024-07-29T09:06:44Z")

</div>

Thanks Pascal!

The approach where the query filter works without using parameters just Expression Structure script works fine. Now, I need to find a way to apply multiple filters using this method.

One potential solution is to use bound Expression Structure scripts. I can start by binding an Expression Structure script to the original named query. This script will handle the first filter. Then, I can apply another bound Expression Structure script to the result of the first one to handle the second filter. By repeating this process, I can apply multiple filters step by step.

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

```python
def transform(self, value, quality, timestamp):

    output = []
    for row in value['data']:
        nivel = row['Nivel']
        if value['chk_5'] and nivel == 5:
            output.append(row)
        elif value['chk_6'] and nivel == 6:
            output.append(row)
        elif value['chk_7'] and nivel == 7:
            output.append(row)
        elif value['chk_8'] and nivel == 8:
            output.append(row)
    return output

```

After using this filter, aply another filter to the result and repeat it as many times as filter inputs I need.  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/3/a38f2c0c942bedd1d3a633afce544b371534513d.png)

---

<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 29, 2024, 10:55am UTC](https://forum.inductiveautomation.com/t/multiple-custom-filters-to-a-named-query/93832/4 "2024-07-29T10:55:45Z")

</div>

Another option I may have is using dynamic query construction via script.

---

<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 29, 2024, 11:21am UTC](https://forum.inductiveautomation.com/t/multiple-custom-filters-to-a-named-query/93832/5 "2024-07-29T11:21:20Z")

</div>

That was in the list:

> [@pascal.fragnoud](#):
>
> build your query dynamically with a dynamically built list of placeholders then use `runPrepQuery`

---

<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:** [August 5, 2024, 9:31am UTC](https://forum.inductiveautomation.com/t/multiple-custom-filters-to-a-named-query/93832/6 "2024-08-05T09:31:11Z")

</div>

ok, in case somebody wants to know what I did to aply the multiselect dropdown and checkboxes group as filters for the dataset, using a query directly on the script and not as a named query

I add a onclick event to my button so it does as follows:

```python
	
	import system.dataset
	
	
	tab_container = self.getSibling("TabContainer")
	
	# Define the parameters for the query
	params = {
	    "tableFilter": tab_container.custom.tableFiler, # This should be a list of strings
	    "nivelFilter": [tab_container.custom.nivelFilter_1] # This should be a list of integers
	}
	
	# Ensure tableFilter is not empty
	if not params['tableFilter']:
	    params['tableFilter'] = ['DEFAULT_VALUE'] # Provide a default value or handle as needed
	
	# Convert the list of tableFilter values to a comma-separated string
	tableFilter_str = ",".join(["'{}'".format(item) for item in params['tableFilter']])
	
	# Convert the list of nivelFilter values to a comma-separated string
	nivelFilter_str = ",".join(map(str, params['nivelFilter']))
	
	# Construct the SQL query with parameters
	query = """
	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 = 'APPLE AND EquipmentType.company = 'APPLE'
	    AND Asset.businessUnit IN ({tableFilter})
	    AND Asset.companyLevel IN ({nivelFilter})
	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
	""".format(tableFilter=tableFilter_str, nivelFilter=nivelFilter_str)
	
	# Execute the query
	try:
	    results = system.db.runQuery(query, "PRIMA") #database connection
	    
	    #the table component has the path "TabContainer/TablaInventario"
	    self.parent.getChild("TabContainer").getChild("TablaInventario").props.data = system.dataset.toDataSet(results)
	    
	    # Log success message
	    self.getSibling("textArea-1").props.text = "Query executed successfully and table data updated."
	except Exception as e:
	    # Log the error
	    self.getSibling("textArea-1").props.text = "Error executing query: {}".format(e)

```

is working fine

---

<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:** [August 5, 2024, 10:03am UTC](https://forum.inductiveautomation.com/t/multiple-custom-filters-to-a-named-query/93832/7 "2024-08-05T10:03:11Z")

</div>

That's not a prep query.  
The proper way to do this is to put placeholders in your query ("`?`") and then pass the list of values to `runPrepQuery`.

Also, quick tip:  
instead of

```python
params = {
	"tableFilter": tab_container.custom.tableFiler, # This should be a list of strings
	"nivelFilter": [tab_container.custom.nivelFilter_1] # This should be a list of integers
}
	
# Ensure tableFilter is not empty
if not params['tableFilter']:
	params['tableFilter'] = ['DEFAULT_VALUE'] # Provide a default value or handle as needed

```

you can do this:

```python
params = {
	"tableFilter": tab_container.custom.tableFiler or ['DEFAULT_VALUE'], # This should be a list of strings
	"nivelFilter": [tab_container.custom.nivelFilter_1] # This should be a list of integers
}

```