# Populate table dropdown list using dynamic results

**URL:** <https://forum.inductiveautomation.com/t/populate-table-dropdown-list-using-dynamic-results/12536>\
**Category:** General Discussion\
**Created:** [January 10, 2017, 5:11pm UTC](https://forum.inductiveautomation.com/t/populate-table-dropdown-list-using-dynamic-results/12536 "2017-01-10T17:11:29Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![KRoberts](https://avatars.discourse-cdn.com/v4/letter/k/e19adc/32.png) [@KRoberts](https://forum.inductiveautomation.com/u/KRoberts)\
**Post date:** [January 10, 2017, 5:11pm UTC](https://forum.inductiveautomation.com/t/populate-table-dropdown-list-using-dynamic-results/12536/1 "2017-01-10T17:11:29Z")

</div>

I’m trying to populate a table dropdown list using the results from a sql query. I have tried a couple of different ways, still not right. Here are 2 ways that I have tried it:

table = system.db.runQuery(“SELECT COLUMN\_NAME, IS\_NULLABLE, DATA\_TYPE FROM INFORMATION\_SCHEMA.COLUMNS WHERE TABLE\_NAME = ‘Ignition\_ItemParameters’”, “Stage”)

myList = [“No Mapping”]

if colName == “mapping”:  
for row in table:  
myList.append(row[0])

```
return {'options': myList}

```

And also:

table = system.db.runQuery(“SELECT COLUMN\_NAME, IS\_NULLABLE, DATA\_TYPE FROM INFORMATION\_SCHEMA.COLUMNS WHERE TABLE\_NAME = ‘Ignition\_ItemParameters’”, “Stage”)

```
if colName == "mapping":
	curString = '("", "No Mapping")'

	for row in table:
		curString += ', ("' + row[0] + '", "' + row[0] + '")'

	return {'options': [curString]}

```

I’m close on both methods, but haven’t gotten it yet.

---

<div class="post-metadata">

**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\
**Post date:** [January 10, 2017, 6:13pm UTC](https://forum.inductiveautomation.com/t/populate-table-dropdown-list-using-dynamic-results/12536/2 "2017-01-10T18:13:12Z")

</div>

This is the structure I use for this

```auto
	if colName == 'btnIcon':
		query = "SELECT DISTINCT [btnIcon] FROM [SuperApp].[dbo].[SYS_Lookup_Images] ORDER BY btnIcon"
		res=system.db.runQuery(query,'SuperApp')
	  	header=1
	  	dlist=[]
	  	for row in res:
	  		dlist.append((str(row[0]),str(row[0])))
	  		header = header + 1
	  	return {'options': dlist}
```

---

<div class="post-metadata">

**Author:** ![KRoberts](https://avatars.discourse-cdn.com/v4/letter/k/e19adc/32.png) [@KRoberts](https://forum.inductiveautomation.com/u/KRoberts)\
**Post date:** [January 10, 2017, 8:24pm UTC](https://forum.inductiveautomation.com/t/populate-table-dropdown-list-using-dynamic-results/12536/3 "2017-01-10T20:24:20Z")

</div>

That doesn’t work. I thought the same thing, here’s the updated code:

query = “SELECT COLUMN\_NAME FROM INFORMATION\_SCHEMA.COLUMNS WHERE TABLE\_NAME = ‘Ignition\_ItemParameters’”

res = system.db.runQuery(query, “Stage”)

header = 1  
dlist = []

if colName == “mapping”:  
for row in res:  
dlist.append((str(row[0]),str(row[0])))  
header = header + 1

```
	return {'options': dlist}

```

elif colName == “datatype”:  
return {‘options’: [("", “No Mapping”), (“Date”, “Date”), (“Boolean”, “Boolean”), (“Integer”, “Integer”), (“Double”, “Double”), (“String”, “String”)]}

If I run it in Script Console, I get the following:

[(‘ID’, ‘ID’), (‘ItemNumber’, ‘ItemNumber’), … (‘PPM\_Target’, ‘PPM\_Target’)]

But when I click in the mapping column in the table, I only get back the first line in the list (ID)

---

<div class="post-metadata">

**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\
**Post date:** [January 11, 2017, 3:02pm UTC](https://forum.inductiveautomation.com/t/populate-table-dropdown-list-using-dynamic-results/12536/4 "2017-01-11T15:02:47Z")

</div>

Can you wrap your code in the code tags here please?  
It will show indentation for us.

You are putting this code in the configureEditor method of the power table correct?  
And you have the column set as editable under custom properties?

---

<div class="post-metadata">

**Author:** ![KRoberts](https://avatars.discourse-cdn.com/v4/letter/k/e19adc/32.png) [@KRoberts](https://forum.inductiveautomation.com/u/KRoberts)\
**Post date:** [January 11, 2017, 5:18pm UTC](https://forum.inductiveautomation.com/t/populate-table-dropdown-list-using-dynamic-results/12536/5 "2017-01-11T17:18:54Z")

</div>

Yes, the column is set to Editable, and inside of the configureEditor function, here is my code:

```auto
        query = "SELECT COLUMN_NAME, IS_NULLABLE, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Ignition_ItemParameters'"
	
        res = system.db.runQuery(query, "Stage")

	header = 1
	dlist = []
	
	if colName == "mapping":
		for row in res:
			dlist.append((str(row[0]),str(row[0])))
			header = header + 1

			return {'options': dlist}
```

I’m missing something but I’m stuck. If I run this through the Script Console and do a print(dlist) I get back what I’m expecting. But if I run it in the table, all I get when I click is the first row.

---

<div class="post-metadata">

**Author:** ![KRoberts](https://avatars.discourse-cdn.com/v4/letter/k/e19adc/32.png) [@KRoberts](https://forum.inductiveautomation.com/u/KRoberts)\
**Post date:** [January 11, 2017, 5:20pm UTC](https://forum.inductiveautomation.com/t/populate-table-dropdown-list-using-dynamic-results/12536/6 "2017-01-11T17:20:12Z")

</div>

The variable res is on the same indention as the query and res, it doesn’t look that way in the post, but it is in code.

---

<div class="post-metadata">

**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\
**Post date:** [January 12, 2017, 1:09pm UTC](https://forum.inductiveautomation.com/t/populate-table-dropdown-list-using-dynamic-results/12536/7 "2017-01-12T13:09:36Z")

</div>

It looks like your return {} is inside the for loop?