# Filter table data

**URL:** <https://forum.inductiveautomation.com/t/filter-table-data/64087>\
**Category:** General Discussion\
**Tags:** perspective\
**Created:** [September 15, 2022, 12:50am UTC](https://forum.inductiveautomation.com/t/filter-table-data/64087 "2022-09-15T00:50:48Z")\
**Posts on this page:** 5\
**Page:** 2

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [September 19, 2022, 8:53pm UTC](https://forum.inductiveautomation.com/t/filter-table-data/64087/21 "2022-09-19T20:53:26Z")

</div>

The table is bound to the parameterized query. If I bind the parameters to the numeric input boxes, then I cannot see a list of all the parts, only what is determined by the value in the box. And, if I bind multiple parameters, then I doubt I would see anything returned in the data set.

If I unbind the table from the name query, set the table data to the returned set of the named query, I get an error

```auto
File "<function:runAction>", line 10, in runAction AttributeError: 'NoneType' object has no attribute 'props'

```

line 10 being

```auto
self.getSibling("Item Data").props.data = system.db.runNamedQuery(namedQuery, params)

```

I checked the table name, query params.  
Oh, I see. The Item Data table is no in the same container. Need to figure out how to reference an object in the parent container.

```auto
self.parent.getSibling("Item Data").props.data = system.db.runNamedQuery(namedQuery, params)

```

This works to filter down to one row.

Is Python the scripting language used in configuring script actions?

I’m thinking I could use nested IF statements to build the parameter list to send to the named query, however, I don’t know how to handle the situation to filter for nothing, basically return the full data set.

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [September 20, 2022, 12:23am UTC](https://forum.inductiveautomation.com/t/filter-table-data/64087/22 "2022-09-20T00:23:17Z")

</div>

So, here is what I’ve got so far:

```auto
	if catalog == 0:
		if master == 0:
			if len(desc) == 0:
				namedQuery = "inv_itemList"
				self.parent.getSibling("Item Data").props.data = system.db.runNamedQuery(namedQuery)
			else: 
				namedQuery = "item_search_filter"				
				params = {"description":desc}
				self.parent.getSibling("Item Data").props.data = system.db.runNamedQuery(namedQuery, params)

```

This produces an error, if the description box has any characters:

```auto
File "<function:runAction>", line 18, in runAction
Conversion failed when converting the nvarchar value 'ring' to data type int.

```

Which seems to tell me that even though I pass the name of the parameter and value to use, the query simply takes that string and applies it to one of the other parameters. Does this sound right?

The query:

```auto
SELECT CatalogNumber, MasterNum, Description
FROM Items
WHERE (:catalogValue =-1 OR CatalogNumber = :catalogValue)
	OR (:masterNum =-1 OR MasterNum = :masterNum)
	OR (:description =-1 OR Description = :description)

```

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [September 20, 2022, 12:46am UTC](https://forum.inductiveautomation.com/t/filter-table-data/64087/23 "2022-09-20T00:46:06Z")

</div>

I’ve got the Catalog and Master number filters to work.

```auto
catalog = self.getSibling("Catalog Number").props.value
	master = self.getSibling("Master Number").props.value
	desc = self.getSibling("Item Desc").props.text

if catalog == 0:
		if master == 0:
			if len(desc) == 0:
				namedQuery = "inv_itemList"
				self.parent.getSibling("Item Data").props.data = system.db.runNamedQuery(namedQuery)
			else: 
				namedQuery = "item_search_filter"				
				params = {"description":desc}
				self.parent.getSibling("Item Data").props.data = system.db.runNamedQuery(namedQuery, params)
		elif master != 0:
			namedQuery = "item_search_filter"
			params = {"masterNum":master}
			self.parent.getSibling("Item Data").props.data = system.db.runNamedQuery(namedQuery, params)
	elif catalog != 0:
		namedQuery = "item_search_filter"
		params = {"catalogValue":catalog}
		self.parent.getSibling("Item Data").props.data = system.db.runNamedQuery(namedQuery, params)

```

But I can’t seem to get the string filter to work. I’ve changed the query a tad:

```auto
SELECT CatalogNumber, MasterNum, Description
FROM Items
WHERE (:catalogValue =-1 OR CatalogNumber = :catalogValue)
	OR (:masterNum =-1 OR MasterNum = :masterNum)
	OR (Description = :description)

```

I tried using an empty string where the original had:

```auto
(:description =-1 OR Description = :description)

```

since the -1 gave me the conversion error, and I’ve tried NULL as well. So far nothing.

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [September 20, 2022, 8:41pm UTC](https://forum.inductiveautomation.com/t/filter-table-data/64087/24 "2022-09-20T20:41:22Z")

</div>

Yeah, that’s a MY BAD. The Description filter should be a LIKE not =.

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [April 11, 2023, 1:48am UTC](https://forum.inductiveautomation.com/t/filter-table-data/64087/25 "2023-04-11T01:48:34Z")

</div>

If anyone happens upon this thread, looking for a named query with SQL and parameters...  
I found that my previous query was not working the way I intended. (I did take time off to attend to other projects btw).

So, my finished query is this:

```auto
SELECT ii.ItemID, ii.CatalogNumber, ii.MasterNum, ii.Description, iis.PartNumber, ic.Category, isa.StorageName
FROM inv.Items as ii inner join inv.Categories as ic on ii.CategoryID = ic.CategoryID 
	inner join inv.Item_Supplier as iis on ii.ItemID = iis.ItemID_FK
	inner join inv.Bin_Item as ib on ii.ItemID = ib.ItemID 
	inner join inv.Bin as bin on ib.BinID = bin.BinID
	inner join inv.StorageArea as isa on bin.StorageAreaID = isa.StorageAreaID
WHERE (CatalogNumber = IIF(:p_cat>99999, :p_cat, NULL)
	OR MasterNum = IIF(:p_mast>0, :p_mast, NULL)
	OR PartNumber LIKE IIF(len(:p_partNum)>0, :p_partNum + '%', NULL)
	OR Description LIKE IIF(len(:p_desc)>0, '%' + :p_desc + '%', NULL)
	OR ii.CategoryID = IIF(:p_category>0, :p_category, NULL)
	OR isa.StorageAreaID = IIF(:p_area>0, :p_area, NULL))

```

Hope this helps someone.

I just realized this is not the finished product, as I will be querying an SQL view, rather than writing long queries like this in Ignition.

Mike

[Previous page](https://forum.inductiveautomation.com/t/filter-table-data/64087.md?page=1)