Multi dropdown into SQL query. How to display array?

Hi there,

I'm trying to input a multi-dropdown (string) into SQL query, and display it in a table in Ignition.

The result looks like this:

And in igntion on the dropdown it looks like this:

Is there a smart way for how to display this nicely in the table? Do I need to concat the whole array, or is there a more elegant way?

The dropdown should look like this:
Ole and Melhus dropdown

And the options should look like this:

    "isDisabled": false,
    "label": "Ole",
    "value": 35
    "isDisabled": false,
    "label": "Melhus",
    "value": 72

(I've given the employee number as 'value'. You could just repeat the names there.)

  • You pass the dropdown's value property to the SQL query.
  • You bind the table's data property to the SQL query.

Is there a smart way for how to display this nicely in the table?

You don't display the dropdown in the table unless you are trying to use a dropdown to edit a table. Please clarify.

I am using multiple choise dropdown, and if i select multiple names, the output is an array of names. I then want to write the values to sql, then display the values in a table.

I can concat all vales of array like this «Ole, Melhus, Robert», but I was hoping for a more elegant solution :grin:

Vanilla JDBC doesn't understand database columns that hold arrays. The JDBC drivers that do are brand-specific extensions. Ignition uses vanilla JDBC.

1 Like

Allright, so I guess I need to concat the string?

Not sure how to do this efficiently though... This is not the best way :stuck_out_tongue:
concat(concat(concat({this.props.value}[0],", "),{this.props.value}[1]),", ")

Jython doesn't have a concat() function. Just use + to concatenate strings. Or, perhaps, use ','.join([some list]) to make a comma-delimited string of keywords.

Thanks, I didn't know i could concat using only +. This is the best I can do, which works in order to concat the values.

    0,	"Alle",
    1,	{this.props.value}[0],
    2, 	{this.props.value}[0] + ", " + {this.props.value}[1],
    3, 	{this.props.value}[0] + ", " + {this.props.value}[1] + ", " + {this.props.value}[2],
  	4, 	{this.props.value}[0] + ", " + {this.props.value}[1] + ", " + {this.props.value}[2] + ", " + {this.props.value}[3],
	5, 	{this.props.value}[0] + ", " + {this.props.value}[1] + ", " + {this.props.value}[2] + ", " + {this.props.value}[3] + ", " + {this.props.value}[4],
 	6, 	{this.props.value}[0] + ", " + {this.props.value}[1] + ", " + {this.props.value}[2] + ", " + {this.props.value}[3] + ", " + {this.props.value}[4] + ", " + {this.props.value}[5],
  	7, 	{this.props.value}[0] + ", " + {this.props.value}[1] + ", " + {this.props.value}[2] + ", " + {this.props.value}[3] + ", " + {this.props.value}[4] + ", " + {this.props.value}[5] + ", " + {this.props.value}[6],
 	8, 	{this.props.value}[0] + ", " + {this.props.value}[1] + ", " + {this.props.value}[2] + ", " + {this.props.value}[3] + ", " + {this.props.value}[4] + ", " + {this.props.value}[5] + ", " + {this.props.value}[6] + ", " + {this.props.value}[7],

Problem is that even if i can write a string of names seperated by commas, the problem returns when i want to edit the table. When I click on a row, all editable cell values gets filled back into the dropdown, which don't support a string.... Hoping anyone can help me make this possible :slight_smile:

Maybe this must be done on the MySQL side? Maybe write all names into different columns, then concating all into 1 column when displaying the table? This was a tricky one :confused:


The canonical way to do this sort of many-to-many relationship is additional DB tables, for the choice options and for the connections between subjects and choices. Assembled in results via LEFT JOIN operations.

Typically not editable within a table of subjects, but displayable there using your SQL DB's group_concat() function while grouping by subject.

To edit, typically a side view or popup would present the multi-select input and perform the appropriate inserts/deletes upon the "connections" table.

You will certainly want to get comfortable using jython scripts--expressions are not good tools to provide parameters to insert/update queries.