Need help with datasets

Hello friends,

I come to you today with some issues regarding datasets and I am hoping you guys can help me figure this out and maybe even optimize what I am doing currently.

I am attempting to scan in some barcodes, store those barcodes in a table then iterate through them to query a MySQL script and pull the data for each of the barcodes and then store the data from a query into another table so people can view the information.

I currently have the SQL query set and it loops through fine and stores it into a list but I am stuck in trying to get it to loop through that list and populate the secondary table with the SQL information. Below is the code, the error, and a picture for reference of what is going on with my screen.

#grabbing the tables from our screen so we can manipulate them
inputTable = event.source.parent.getComponent('inputTable').data
outputTable = event.source.parent.getComponent('outputTable').data
componentID = event.source.parent.getComponent('inputTextField').text
randomTable = event.source.parent.getComponent('randomTable').data

#empty list constructors for later
params = []
dataList = []
data = []

#create the headers for our tables
headers = ['Cell_ID', 'Pre_EFill_Weight', 'Preweigh_Lower_Spec_Okay', 'EFill_Number', 'Fill_Head', 'Boat_Num', 'Boat_Position', 'Weight_Post_Efill', 'Weight_Delta', 'Date']

#create a dataset so we can apply it to outputTable later
updatedTable = system.dataset.toDataSet(headers, data)

#create empty lists for storage
inputData = []


for row in range(inputTable.getRowCount()):
	
	params = inputTable.getValueAt(row, 'Scanned In')
		
	########### NEED TO LOOP THROUGH THE DATA YOU GET OUT OF ION AND PUT IT INTO A LIST THEN LOOP THROUGH THAT LIST TO PUT IN INTO THE TABLE ON THE SCREEN ###########2
		
	inputData = system.db.runNamedQuery('ScanSearch', {'Component_ID': params})
	
	for rowAppend in range(inputData.getRowCount()):
		for colAppend in range(inputData.getColumnCount()):
			
			updatedTable = system.dataset.setValue(outputTable.data, rowAppend, headers[colAppend], inputData.getValueAt(rowAppend, colAppend))
			print updatedTable

This code will throw this error:

Traceback (most recent call last):
File “event:actionPerformed”, line 33, in
TypeError: setValue(): 1st arg can’t be coerced to com.inductiveautomation.ignition.common.Dataset

Ignition v8.1.7 (b2021060314)
Java: Azul Systems, Inc. 11.0.11

I greatly appreciate you Ignition Wizards. I am slowly learning every day thanks to you guys and these great forums. If you guys think I am doing this in a really dumb way, just let me know.

Tyler

Conversely, I have the dataset that I pulled from SQL and I tried to make a list from a single row of that dataset when there are multiple rows but I could not figure out how to do it. When I would iterate through it, it would add all rows to the list. For some reason I just can’t think of a way to stop it at the end of the row.

It looks like this is the error from your first post. outputTable should already be a dataset from line 3:

outputTable = event.source.parent.getComponent('outputTable').data

Remove the .data reference.

1 Like

I think if I were you I would create the updated table as a list of lists before creating the updatedTable, as this block of code:

for row in range(inputTable.getRowCount()):
	
	params = inputTable.getValueAt(row, 'Scanned In')
		
	########### NEED TO LOOP THROUGH THE DATA YOU GET OUT OF ION AND PUT IT INTO A LIST THEN LOOP THROUGH THAT LIST TO PUT IN INTO THE TABLE ON THE SCREEN ###########2
		
	inputData = system.db.runNamedQuery('ScanSearch', {'Component_ID': params})
	
	for rowAppend in range(inputData.getRowCount()):
		for colAppend in range(inputData.getColumnCount()):
			
			updatedTable = system.dataset.setValue(outputTable.data, rowAppend, headers[colAppend], inputData.getValueAt(rowAppend, colAppend))
			print updatedTable

is going to start at the top of updatedTable, overwriting data.

for row in range(inputTable.getRowCount()):
	
	params = inputTable.getValueAt(row, 'Scanned In')
		
	########### NEED TO LOOP THROUGH THE DATA YOU GET OUT OF ION AND PUT IT INTO A LIST THEN LOOP THROUGH THAT LIST TO PUT IN INTO THE TABLE ON THE SCREEN ###########2
		
	inputData = system.db.runNamedQuery('ScanSearch', {'Component_ID': params})

        for row in range(inputData.getRowCount():
            data.append([inputData.getValueAt(row, col) for col in range(getColumnCount())])

updatedTable = system.dataset.toDataSet(headers, data)

I didn’t test any of that, but hopefully it’ll give you a rough idea.

1 Like

Ahh yeah a list of lists would actually make a lot of sense. Brain didn’t really think about doing that. Maybe a pydataset would be better here instead. But I am just taking out that input table as it’s pretty much unnecessary as when we scan anything in, we can see if we double scanned because it will show the barcode in the SQL query. Don’t know why I didn’t think of that last night. Thank you for your help though friend!

1 Like