Populate table dropdown list using dynamic results

General Discussion
1

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.

2

This is the structure I use for this

	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}
3

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)

4

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?

5

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

        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.

6

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.

7

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