Updating numeric text field

I have about 10 different buttons that reflect a department for each one. I am wanting to be able to select a button and it fire a sql query to retrieve information from a table that have the rows that equate to that department. So if I select the safety department, my designated numeric text field updates with the average safety number of all the rows that have the safety area in the column. here is what I have so far:
//event.source.parent.getComponent(‘Saverage’).intValue = system.db.runQuery("SELECT AVG(CAST(Safety AS int)) AS SafetyNumber FROM SQDCME.dbo.SQDCME where Area = ‘Stacker1’ ")//

The problem is that I get an error back saying:

File “event:actionPerformed”, line 23, in

AttributeError: ‘NoneType’ object has no attribute ‘intValue’

Basically I am trying to get the average safety number from the Safety column that has all the rows with the Stacker1 area in it.

This lookup is failing - getComponent('Saverage') is returning None, Python's stand-in for null, which doesn't have an intValue property, and thus is throwing the error you're seeing. Double check the component name, whether it's inside any groups/containers, etc. Use the property selector panel on the right hand side of the script editor to confirm the path to the component you want.

Yeah, so I went back and checked the scrip editor and it was in a container:

event.source.parent.getComponent(‘Container’).getComponent(‘Saverage’).intValue

and after I ran that I got:

File “event:actionPerformed”, line 23, in

TypeError: can’t convert Dataset [1R ? 1C] to int

Okay, so now the problem is the other half of that line :slight_smile:
system.db.runQuery returns a dataset. If you know your query will only return one result, you can just switch your call from runQuery to runScalarQuery - it'll automatically pull out the first row/column of the result.

Because the Query is returning a dataset which contains a column “SafetyNumber” and a row (the value). You can either directly assign a value form the dataset, or you can change the query to return a scalar value.
I think you could do

event.source.parent.getComponent(‘Container’).getComponent(‘Saverage’).intValue = system.db.runQuery("SELECT AVG(CAST(Safety AS int)) AS SafetyNumber FROM SQDCME.dbo.SQDCME where Area = ‘Stacker1’ ").getValueAt(0,0)

Well I am trying to get the safety number average of all the rows that have the Stacker1 value in the Area Column.

We understand that, but the container you’re placing the value into only cares about the value - not the name. Switching to runScalarQuery will return just the simple value of the query, instead of the result set, which is structured as - in your case - a dataset with a named column and a value row.

1 Like

Right, but your query is doing that aggregation (on the DB side). When it comes into Ignition, it will always be one value. But, the runQuery function returns a dataset in all cases - whether you’re returning 100 rows x 5 columns or 1 row x 1 column.

1 Like