Converting Array Value from Query

Ignition
, ,
1

Hello all,

I am currently struggling with displaying data as an integer that is being pulled via a query. I have created a view where I use the following script to sum up the total value of that platform so I can display it in several textboxes, one for each of the platforms. I run this script from a dropdown filter set to current day (unless modified):

#Variables for table filter
date = self.props.value
shift = self.getSibling("Shift").props.value
dateStart = self.custom.filterDate
dateEnd = self.custom.filterEndTime

#Filter the table on startup
if date != None and shift != "":
	#Fill compact totals
	returnedData = system.db.runNamedQuery("Totals/Compact", {"dateStart" :dateStart, "dateEnd" :dateEnd, "shift" :shift})
	self.parent.getChild("Body Box").getChild("Units Box").getChild("Compact Units").props.text = returnedData
	#Fill FSOU totals
	returnedData1 = system.db.runNamedQuery("Totals/FSOU", {"dateStart" :dateStart, "dateEnd" :dateEnd, "shift" :shift})
	self.parent.getChild("Body Box").getChild("Units Box").getChild("FSOU Units").props.text = returnedData1
	#Fill horizon front totals
	returnedData2 = system.db.runNamedQuery("Totals/Horizon Front", {"dateStart" :dateStart, "dateEnd" :dateEnd, "shift" :shift})
	self.parent.getChild("Body Box").getChild("Units Box").getChild("Horizon Front Units").props.text = returnedData2
	#Fill horizon rear totals
	returnedData3 = system.db.runNamedQuery("Totals/Horizon Rear", {"dateStart" :dateStart, "dateEnd" :dateEnd, "shift" :shift})
	self.parent.getChild("Body Box").getChild("Units Box").getChild("Horizon Rear Units").props.text = returnedData3
	#Fill VMAX totals
	returnedData4 = system.db.runNamedQuery("Totals/VMAX", {"dateStart" :dateStart, "dateEnd" :dateEnd, "shift" :shift})
	self.parent.getChild("Body Box").getChild("Units Box").getChild("VMAX Units").props.text = returnedData4
	#Fill VMW classic totals
	returnedData5 = system.db.runNamedQuery("Totals/VMW Classic", {"dateStart" :dateStart, "dateEnd" :dateEnd, "shift" :shift})
	self.parent.getChild("Body Box").getChild("Units Box").getChild("VMW Classic Units").props.text = returnedData5
	#Fill VMW HE totals
	returnedData6 = system.db.runNamedQuery("Totals/VMW HE", {"dateStart" :dateStart, "dateEnd" :dateEnd, "shift" :shift})
	self.parent.getChild("Body Box").getChild("Units Box").getChild("VMW HE Units").props.text = returnedData6
	#Fill Total
	returnedData7 = system.db.runNamedQuery("Totals/Total Units", {"dateStart" :dateStart, "dateEnd" :dateEnd, "shift" :shift})
	self.parent.getChild("Body Box").getChild("Units Box").getChild("Total Units").props.text = returnedData7

This sets the textboxes to an array (as it is pulled from a query). For example: for today's date, I have [{"":2}] for the unit value for the "Compact" platform. Is there any way to get this to display the 2 without the additional array info?

Thanks in advance.

2

If your queries are returning a singular value that you want to display, then change the query type in the named query to Scalar Query. This will cause the query to return a single value and not a dataset/array.

Side note, I have no idea what your database structure is, but I would consider using a single query with table joins to get all your totals in one shot. Then you can assign each value to the corresponding label.

3

You are my hero. Thank you very much, I've been wondering what Scalar Query did. I'm still newer to developing, so this was a huge help. I will look into using table joins.

4

If you do a table join and get multiple totals in a single query, then you can't use scalar. Instead, you would do this

# Example data
row | total1 | total2 | total3
------------------------------
0   | 22     | 44     | 66   

results = system.dataset.runNamedQuery("someQuery",{})
pyData = system.dataset.toPyDataSet(results)
for row in pyData:
    label1= row['total1']
    label2= row['total2']
    label3= row['total3']
5

You wouldn't need a loop though.
something like

label1, label2, label3 = pyData[0][1:]

should work

6

True, since the OP is newer to Ignition, I'm sure they will need to loop over a dataset at some point which is why I did it that way.
Good point though for a one-liner option, but the first column is just an artifact of printing the dataset, so it would be this I believe

label1, label2, label3 = pyData[0]
7

You don't need the [:] then.

8

Ah, yeah you're right. Python would unpack it automatically :slight_smile:

EDIT:
One thing to note about the one-liner method, if the query was ever changed where the column order is altered, then the label assignment would be incorrect.

By explicitly indexing by column name, ie label1 = pyData[0]['total1'], the script would be more robust