Filter table data

General Discussion
21

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

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

line 10 being

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.

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.

22

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

	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:

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:

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

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

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:

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:

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

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

24

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

1 Like
25

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:

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

3 Likes