# Using Drop-Downs to specify a SQL query

**URL:** <https://forum.inductiveautomation.com/t/using-drop-downs-to-specify-a-sql-query/75522>\
**Category:** Ignition\
**Tags:** scripting, database\
**Created:** [June 26, 2023, 12:50pm UTC](https://forum.inductiveautomation.com/t/using-drop-downs-to-specify-a-sql-query/75522 "2023-06-26T12:50:04Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Mason\_Stevens](https://avatars.discourse-cdn.com/v4/letter/m/edb3f5/32.png) [@Mason\_Stevens](https://forum.inductiveautomation.com/u/Mason_Stevens)\
**Post date:** [June 26, 2023, 12:50pm UTC](https://forum.inductiveautomation.com/t/using-drop-downs-to-specify-a-sql-query/75522/1 "2023-06-26T12:50:04Z")

</div>

I am looking for the best way to accomplish the following with drop-down boxes:

1. Let the selection from one drop-down dictate what set of tags will be displayed (i.e., choose a reactor from the drop-down, and temp/pressure/level values will propagate to the HMI).

2. Let the selection from two drop-downs be linked to the row+column of a SQL query (i.e. choose a reactor to set the row, choose a cleaning solvent to set the column, and the window will return the resulting charge weight to a label... choosing reactor 1 and solvent 1 will return the weight found in [1,1] in the database).

Any advice appreciated!

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [June 26, 2023, 1:28pm UTC](https://forum.inductiveautomation.com/t/using-drop-downs-to-specify-a-sql-query/75522/2 "2023-06-26T13:28:16Z")

</div>

What flavor of DB? (MSSQL, MySQL, Postresql, etc...)

Off the top of my head without knowing anything about your setup I would suggest something like this:

Create a Named Query with the required parameters in the `WHERE` clause and then use a property change script to call `system.db.runNamedQuery()` supplying the values of the dropdowns as the parameter values.

---

<div class="post-metadata">

**Author:** ![Mason\_Stevens](https://avatars.discourse-cdn.com/v4/letter/m/edb3f5/32.png) [@Mason\_Stevens](https://forum.inductiveautomation.com/u/Mason_Stevens)\
**Post date:** [June 26, 2023, 1:43pm UTC](https://forum.inductiveautomation.com/t/using-drop-downs-to-specify-a-sql-query/75522/3 "2023-06-26T13:43:10Z")

</div>

Our DB is MSSQL.

I'll give runNamedQuery a try and see if i can get something worked out, thanks!

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [June 26, 2023, 2:27pm UTC](https://forum.inductiveautomation.com/t/using-drop-downs-to-specify-a-sql-query/75522/4 "2023-06-26T14:27:51Z")

</div>

Why a change script ?  
A simple query binding should be enough.

1. Let's say you have, in the tag provider `[default]`, a folder named `reactors`, that contains the following UDTS: `reactor1`, `reactor2`, `reactorA`, `reactorB`, `reactorFoo`.  
Configure your dropdown options to look like this:

```json
[
  {
    'label': "reactor 1",
    'value': "reactor1"
  },
  {
    'label': "reactor 2",
    'value': "reactor2"
  },
  {
    'label': "reactor A",
    'value': "reactorA"
  },
  {
    'label': "reactor B",
    'value': "reactorB"
  },
  {
    'label': "reactor foo",
    'value': "reactorFoo"
  }
]

```

Now, use an indirect tag binding on the labels where you want to disaply your reactors properties, so that it looks something like this:  
`[default]reactors/{reactor}/temperature`  
And use the dropdowns `value` property as the `{reactor}` parameter.

1. Create a named query, let's call it `get_charge_weight`. Make it a SCALAR property, as you want to get only one value out of this. The query should look like this, assuming the reactor and solvent columns are foreign keys to actual reactors and solvents:

```auto
select charge_weight
from charges
where reactor_id = :reactor and solvent_id = :solvent

```

Obviiously you'll need to configure those 2 parameters to the appropriate names.  
Now create your 2 dropdowns with the requires options. most likely you can just generate them with queries:

```auto
select id
from reactors

select id
from solvents

```

You can then use the dropdown values as parameter for the `get_charge_weight` named query, that you can use in a query binding on whatever you want to use to display the result.