Create a list from power table

Ignition
1

I have Power Table component that has a checkbox column name ‘Request’, this is tied to sql query.
I also have a button name ‘Request Materials’

i Have Many colums in this table but we will focus on the one i need to use which is Runid,

what i want to do is the following.

‘Request Materials’ Action performed.

select from Power Table all the Runid’s number which have the column ‘Request’ Check in the client Dataset of the Power Table and from then run a query using the list of ‘Runid’ that are selected.
Using MSSQL, query will be looking for a list.

SELECTSUM(r.qty) as cases,r.itemid as productid
FROM productrecitems r
INNER JOIN rawmaterials rm on rm.id = r.itemid
INNER JOIN productrec pr on pr.id = r.productrecid
INNER JOIN prodschedule ps on ps.productid=pr.productid
WHERE ps.runid  in (RUN ID FROM TABLE)
GROUP BY r.itemid, ps.runid

So i can make work with one run selected as the list it’s only one, if i have multiple checks, then i need to add a comma before the next runid. like example:

1 RUN

  WHERE ps.runid  in (1424)

MULTIPLE RUNS

  WHERE ps.runid  in (1424,1423,1428,1400,1550)

So how can i make that list as the option from row in range would generate a rows and not a list, and how can i add a comma to only before the runid, whenever i have more than one.

2

Code below extracts selected run ID’s in comma separate string and list forms–not sure which you were looking for:

# Create a sample dataset.
headers = ['Request', 'Runid']
data = [[True, 1]]
data.append([True, 2])
data.append([False, 3])
data.append([True, 4])
data.append([False, 5])
dataset = system.dataset.toDataSet(headers, data)
# Get selected run IDs from dataset.
runIdFromTableCommaSeparatedStr = ''
runIdFromTableList = []
for row in range(dataset.rowCount):
	if dataset.getValueAt(row,'Request'):
		# Make list as comma separated string.
		if len(runIdFromTableCommaSeparatedStr) != 0:
			runIdFromTableCommaSeparatedStr += ','
		runIdFromTableCommaSeparatedStr += str(dataset.getValueAt(row, 'Runid'))
		# Make list.
		runIdFromTableList.append(dataset.getValueAt(row, 'Runid'))
print runIdFromTableCommaSeparatedStr
print runIdFromTableList

Output:

1,2,4
[1, 2, 4]
1 Like
3

thanks, it does provided what i need, now i just add that list as a parameter to my query and it should work.

also do you know how to do that with getselectedrows? this is for another operation, but if user has selected 5 rows, i want to make a list of the Runid’s selected.

4

so I spoke too soon i guess, i’m having the following problem now. trying to use that list in my query and it gives me this error. which it should not because it should take it as a list.

image
image842×239 3.47 KB

this is the Script that i’m using

image

Anyone any thought?

5

i went ahead and had to do the following in order to be able to use the list. as it ignition would not allow to put it thru if not.

image

thanks for the help @witman

1 Like
6

I'd guess something like:

# Get selected rows.
rows = table.getSelectedRows()
# Get run IDs from selected rows.
runIds = ''
for row in rows:
	if len(runIds) != 0:
		runIds += ','
	runIds += str(dataset.getValueAt(row, 'Runid'))
7

For a bit of completeness, sometimes using a PyDataSet makes things easier to iterate.

# Create a sample dataset.
headers = ['Request', 'Runid']
data = [[True, 1]]
data.append([True, 2])
data.append([False, 3])
data.append([True, 4])
data.append([False, 5])
dataset = system.dataset.toDataSet(headers, data)

#Convert to PyDataSet
dataIn = system.dataset.toPyDataSet(dataset)

# Get selected run IDs from dataset.
runIdFromTableList = [row['Runid'] for row in dataIn if row['Request'] == True]
runIdFromTableCommaSeparatedStr = ','.join(str(x) for x in runIdFromTableList)

print runIdFromTableCommaSeparatedStr
print runIdFromTableList
8

You also need a question mark for each value in the list. Then you should be able to pass the list to runPrepQuery()

query = """
		SELECT SUM(r.qty) as cases, r.itemid as productid
		FROM productrecitems r
		INNER JOIN rawmaterials rm on rm.id = r.itemid
		INNER JOIN productrec pr on pr.id = r.productrecid
		INNER JOIN prodschedule ps on ps.prodid = pr.productid
		WHERE ps.runid in (""" + ','.join(['?'] * len(runIdFromTableList)) + """)
		GROUP BY r.itemid, ps.runid
		"""

data = system.db.runPrepQuery(query, runIdFromTableList)

2019-03-11_7-26-23

3 Likes