Multi-select dropdown to MSSQL table

Ignition
, ,
1

I'm trying to figure out how to break down the multiple selections in a dropdown and insert them into a table.

The project is for users to report observations. I have 3 tables, first is for the report details, second is for the list of items they can pick. (they can pick none, one, or many items from the list) and the third will store those items with the two keys from table 1 and 2.

So if observation_ID 1 has 2 items (A&C) then table 3 would have 2 rows. 1 | A, 1 | C.

I can't find any documentation or posts on how to break apart the selections from the dropdown an insert them into the table.

Thanks for the help!

2

When multi-select is enabled, the value prop of the dropdown will be a list.
You just need to grab everything in that list.

something like that:

from itertools import chain

obs_id = obs_dropdown.value
item_values = item_dropdown.value

marks = ','.join("(?, ?)" for _ in item_values)
query = """
insert into your_table (obs_id, item_id)
values {}
""".format(marks)

values = list(chain.from_iterable((obs_id, item_id) for item_id in item_values))

system.db.runPrepUpdate(query, values)
1 Like
3

Thank you!

I'm assuming obs_id is the record value from the first table.

One question. Where are you getting the item_id from?

4

obs_id is found in the value parameter of the first dropdown,
item_id comes from the value parameter of the second dropdown.
It's extracted in a comprehension:

... for item_id in item_values

which is equivalent to

for item_id in item_values:
    ...

Here, I used a generator expression to generate tuples containing the obs_id and each item_id:

(obs_id, item_id) for item_id in item_values

Then used chain.from_iterable to "flatten" the list of tuples into a simple 1d list, which can be passed as values to runPrepQuery

5

Thank you once again, you're awesome!