How to Turn SQL Database Columns into Dropdown Menus in Ignition Perspective?

Ignition
1

I'm working with Ignition Platform 8.1.37 (b2024013011) and seeking guidance on implementing recipe management through SQL Database integration. While I've successfully implemented transactional groups for Loading and Saving recipes, I need advice on configuring recipe selection via dropdown menus.

Specifically, I'd like to populate dropdown menus with recipe information in the format: JobNumber.RecipeName (e.g., 1.Recipe1). Recipe deletion functionality would be a nice-to-have feature, but it's not critical at this stage.

Could someone share best practices or examples for setting up these SQL-based recipe selection dropdowns?

image
image1070×311 30.8 KB

Like this (I manually added them but need automatically pulled from SQL):
image
image396×183 5.29 KB

2

Your recipe names aren't unique.

  • What's supposed to be displayed for rows 2 and 4? (Oops, I missed that the display was a concatenation, JobNumber.RecipeName.)
  • Why is row 1 full of nulls?
3

@Transistor is right, it is very strange that the recipe names are not unique. However, the query would look like this, where ndx is the name of the column that has your primary key.

SELECT
    ndx as value,
    concat(Job_number, '. ', Recipe_Name) as label
FROM
    your_table_name

You can add a where clause to filter the returned data as needed.

4

To follow @dkhayes117 recommendation, create a query binding on the dropdown's PROPS.options and configure that to use the named query in the query binding path.

The PROPS.value will contain the ndx column number when a selection is made. (dkhayes is guessing that's the name of that column as it's not visible in the screengrab). You then use that value in a query WHERE ndx = :index to return the recipe by passing the dropdown's value property as the index parameter into the named query.

5

I am not sure either but I was testing Transactional Group for the first time and I didn't create a database manually. Transactional Group made a database for me.

6

Thank you for the help. @Transistor @dkhayes117
Now I am stuck here. Any guidance to achieve this will be very helpful. Thanks in advance!

image
image1504×1312 81.3 KB

7

Go back to your Named Query editor and remove the two parameters. You're not using them.

Later, the dropdown selected value will provide the recipes_ndx value as a parameter to another query (to retrieve the recipe) which will have the line,
WHERE recipes_ndx = :recipes_ndx

I recommend that you read Named Queries | Ignition User Manual and the following pages in that section of the documentation as well as watch the Inductive University video on the topic.

1 Like