Multiple selection drop down

Ignition
1

Using mutiple selection drop downs to try and build a query where statement.

What is a good way to loop through the selected options and pull those selections out to build the query?

Say I have 5 options in the drop down and the user selects 2 of them.

2

Hello!

I used something like this for a onClick event on a button to filter a query based on multiselect dropdown.

# Get reference to 'businessUnit' multiple dropdown filter
tab_container = self.getSibling("TabContainer")
business_units = [bu[3:] for bu in tab_container.custom.tableFilter] if tab_container.custom.tableFilter else ['DEFAULT_SITE']

# Construct SQL  with placeholders
query = """
    SELECT *
    FROM WorkOrder WO
    WHERE WO.businessUnit IN ({businessUnits})
""".format(businessUnits=", ".join(["?" for _ in business_units]))

# Execute query
try:
    results = system.db.runPrepQuery(query, business_units, "DATABASE_NAME")
    if tab_container:
        tab_container.getChild("TablaOT").props.data = system.dataset.toDataSet(results)
    
  
    self.getSibling("textArea-1").props.text = "Query executed successfully and table data updated."
except Exception as e:

    self.getSibling("textArea-1").props.text = f"Error executing query: {e}"

hope it helps.

4

Further information now that I'm awake more. Im lookint to have 3 or 4 mutilple selection drop down objects. The user will select possibly multiple from each then press a "Start" button.

It would then build a query based off the outputs selected

5

You'll need to give more information.

  1. How are the dropdown values related to the database columns?
  2. Are you trying to create a query of the form,
SELECT col1, col2, col3
FROM myTable
WHERE
    col1 IN (dd1[2], dd0[5], dd0[6])
    AND
    col2 IN (dd2[7])
    AND
    col3 IN (dd3[0], dd0[2], dd0[11])

where those ddx[n] values are determined by the dropdowns?

  1. What is to happen if a dropdown has no selection?
  2. Why do you need a "Start" button? You can let it update on every selection.
6

The overall result is to pull production data from multiple plant areas and combine.

First drop down would be forge where it is 4 lines. Id like to be all to select all or 1 or any number in between. 2nd drop down is Turning, it has 5 lines. Same concept being able to select any combination of lines to create the query for the database of part output.

If a selection is blank it should be ignored by the query builder.

The start button was just a way for me to focus my process thoughts currently. Its not a requirement.

7

You didn't adequately answer questions 1 and 2.

8

Just a quick tip:
", ".join(["?" for _ in business_units])

You don't need the square brackets here, join can work with a generator.

1 Like