# Create a list from power table

**URL:** <https://forum.inductiveautomation.com/t/create-a-list-from-power-table/23195>\
**Category:** Ignition\
**Created:** [March 9, 2019, 6:30pm UTC](https://forum.inductiveautomation.com/t/create-a-list-from-power-table/23195 "2019-03-09T18:30:53Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![juang](https://avatars.discourse-cdn.com/v4/letter/j/9dc877/32.png) [@juang](https://forum.inductiveautomation.com/u/juang)\
**Post date:** [March 9, 2019, 6:30pm UTC](https://forum.inductiveautomation.com/t/create-a-list-from-power-table/23195/1 "2019-03-09T18:30:53Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![witman](https://avatars.discourse-cdn.com/v4/letter/w/d2c977/32.png) [@witman](https://forum.inductiveautomation.com/u/witman)\
**Post date:** [March 9, 2019, 6:48pm UTC](https://forum.inductiveautomation.com/t/create-a-list-from-power-table/23195/2 "2019-03-09T18:48:25Z")

</div>

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

```python
# 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:

```nohighlight
1,2,4
[1, 2, 4]

```

---

<div class="post-metadata">

**Author:** ![juang](https://avatars.discourse-cdn.com/v4/letter/j/9dc877/32.png) [@juang](https://forum.inductiveautomation.com/u/juang)\
**Post date:** [March 9, 2019, 8:04pm UTC](https://forum.inductiveautomation.com/t/create-a-list-from-power-table/23195/3 "2019-03-09T20:04:52Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![juang](https://avatars.discourse-cdn.com/v4/letter/j/9dc877/32.png) [@juang](https://forum.inductiveautomation.com/u/juang)\
**Post date:** [March 9, 2019, 10:07pm UTC](https://forum.inductiveautomation.com/t/create-a-list-from-power-table/23195/4 "2019-03-09T22:07:19Z")

</div>

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](https://us1.discourse-cdn.com/inductiveautomation/original/2X/b/b0e0b29d50d4bbac3fedde72e835777074fbc7b2.png)

this is the Script that i’m using

![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/f/f0432b86974485aa41bc7ab722b8a2c1f27d8aa0.png)

Anyone any thought?

---

<div class="post-metadata">

**Author:** ![juang](https://avatars.discourse-cdn.com/v4/letter/j/9dc877/32.png) [@juang](https://forum.inductiveautomation.com/u/juang)\
**Post date:** [March 9, 2019, 10:31pm UTC](https://forum.inductiveautomation.com/t/create-a-list-from-power-table/23195/5 "2019-03-09T22:31:25Z")

</div>

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](https://us1.discourse-cdn.com/inductiveautomation/original/2X/0/02a7160f663712657b318c7dafcdbed068599dc5.png)

thanks for the help @witman

---

<div class="post-metadata">

**Author:** ![witman](https://avatars.discourse-cdn.com/v4/letter/w/d2c977/32.png) [@witman](https://forum.inductiveautomation.com/u/witman)\
**Post date:** [March 10, 2019, 12:12am UTC](https://forum.inductiveautomation.com/t/create-a-list-from-power-table/23195/6 "2019-03-10T00:12:38Z")

</div>

> [@juang](#):
>
> 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.

I'd guess something like:

```python
# 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'))

```

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [March 11, 2019, 11:08am UTC](https://forum.inductiveautomation.com/t/create-a-list-from-power-table/23195/7 "2019-03-11T11:08:06Z")

</div>

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

```python
# 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

```

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [March 11, 2019, 11:24am UTC](https://forum.inductiveautomation.com/t/create-a-list-from-power-table/23195/8 "2019-03-11T11:24:57Z")

</div>

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

```auto
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](https://us1.discourse-cdn.com/inductiveautomation/original/2X/6/6e1118f381712fe3e71b0214401d869f46621454.jpeg)