How to use Multi-Select Dropdown and filter the Power Table

Ignition
1

Hi, I am trying to use the new function Multi-Select dropdown to display different values in my power table based on the selections in the dropdown. The problem is I cannot directly access a dataset object (selectedLabels) in my Power table SQL query. How can i do this?

2

I probably need more details, but my guess is that you should pull in the unfiltered SQL data you want to the data property of the multiselect dropdown, and then directly bind your power table's data property to multiselect dropdown's selectedData property.

This way, the user selections will automatically populate the power table with the selected data, and there will be no need for subsequent queries.

3

Hi, Basically what i am trying to do is filter the power table here using the multi dropdown option. I am hoping i can access the selectedLabels datasety object into the SQL query for my table. So something like this.
-- First try to create a list from my multiselect function
with t as (SELECT * FROM '{Root Container.Container.MultiSelectDropdown.selectedLabels}["selectedLabels"]')
-- Then filter my table based on the multiselect function
SELECT*
FROM KoroitSamples
WHERE KoroitSamples.[Instrument] in t
But this does not work, thus i am trying to find an option where i can access the dataset object in SQL.

image
image1185×521 59.9 KB

4

Sounds like you want something like the Pseudo-SQL offered by my view() expression function (part of my free Integration Toolkit module).

Or, for more focused tasks, perhaps directly use the where() expression function.

5

Hi, thank you for replying, Can you clarify? Is view() and where(), used in expression only? Can i use them in SQL query? How would i use where() for the above scenario?

6

I'm off tomorrow, so if you haven't figured this out by then, I'll set something up in my test environment to demonstrate a few ways to do this.

I imagine that you will need to get the column as a list to make it useful for querying, which means you would have to script the query on the selectedLabels propertyChange event.

The script would look something like this:

'''
Written for the script editor of the multiselect dropdown's propertyChange event handler
'''

# If and only if the selectedLabels property of the multiselect dropdown changes
if event.propertyName == 'selectedLabels':
	
	# Get the labels as a list
	labels = event.source.selectedLabels.getColumnAsList(0)
	
	# Create a parameterized query query and match the number of parameters to the number of labels
	query = "SELECT * FROM KoroitSamples WHERE KoroitSamples.[Instrument] IN (%s)" % ', '.join('?' for item in labels)
		
	database = #The database name will need to be defined
	
	# Run the query and assign its result to the data property of the power table
	# Change this path to the relative path of the power table
	event.source.parent.getComponent('Power Table').data = system.db.runPrepQuery(query, labels, database)

That script looks proper by my eyes, but full disclosure: I'm not where I can test code at the moment.

2 Likes
7

thank you so much it worked.

1 Like