Dropdown menu that dictates next drop down

Ignition
,
1

I'm trying to give my operators a better option to review reports, as currently they just have a single drop down and have to scroll through Serial Numbers to find exactly what they're looking for. I have 4 banks of machines and all banks have 5 machines for a total of 20 unique machines. I'm trying to have my operators be able to filter reports based on specific machines through a series of dropdowns.

  1. I'm trying to figure out how to combine "Bank#" and "Machine#" into distinct rows of data in a dropdown menu. I haven't be able to achieve this with my rudimentary DB Browse skills in Property Binding. Looking to have a dropdown that would be: Bank1-Machine1, Bank1-Machine2, etc.
  2. Next is having dropdown#2 that then filters to associated "Serial#" based on the selection in dropdown#1.
2

Why not do three dropdowns? One for bank, machine and serial? Then you can pass the bank and machine Ids as parameters for a named query that populates the serial number dropdown.

How are your db tables set up for storing serial number information? Are the bank and machine Ids separate columns?

If you want to stick with 1 dropdown for combined bank/machine, you'll need to use a script to build the dropdown list dataset. You would then need to relate each value to the proper parameters for a named query to populate the serial dropdown options dataset.

3

My logic for combining bank and machine is to just keep things a bit more simple UI wise. I worry my operators are going to complain about having to click two more dropdowns rather than one.

Serial# is just another column in my DB along with Bank# and Machine#.

I was able to get my first drop down to look how I like using the following:

SELECT DISTINCT Bank#+'-'+Machine#
FROM Cycle_Records_IS

Am I on the right track here? I'm taking over this project (with no support) with zero ignition or SQL experience so I'm slowly piecing things together.

4

Am I correct in assuming that both Bank and Machine are integers in the DB table?

SELECT
	ROW_NUMBER() OVER(ORDER BY Bank ASC) - 1 AS 'value', 
	[label], 
	Bank, 
	Machine 
FROM(
	SELECT 
		Bank, 
		Machine, 
		MAX(c.[label]) AS 'label'
	FROM 
		Cycle_Records_IS p
	CROSS APPLY	(SELECT 'Bank' + convert(nvarchar, p.Bank) + '-Machine' + convert(nvarchar, p.Machine) as 'label') AS c
	GROUP BY 
		Bank, 
		Machine
)t1

Should give you what is expected for the first dropdown. There is probably better/cleaner SQL to achieve this; personally I'd only run

SELECT
	Bank,
	Machine
FROM
	Cycle_Records_IS
GROUP BY
	Bank, Machine

and place the result somewhere and use a script to loop through it and create the dataset for the dropdown.

Have a named query of

SELECT 
	Serial AS 'value', 
	Serial AS 'label' 
FROM
	Cycle_Records_IS
WHERE 
	Bank = :bank 
	AND Machine = :machine
ORDER BY Serial DESC

as the binding for the options dataset of the serial number dropdown. The parameters of the named query binding would be

lookup({path.to.first.dropdown.options}, {first.dropdown.select.value}, Null, 'value', 'Bank')

and

lookup({path.to.first.dropdown.options}, {first.dropdown.select.value}, Null, 'value', 'Machine')

for bank and machine respectively.

To make everything easier on yourself, create 4 custom properties on the root container of the window called machineByBank, selectedBankAndMachineIdx, availableSerials, and selectedSerial.

Have the first query as the binding for machineByBank, and bind the first dropdown's options dataset to this.

Bidirectionally bind the first dropdown's selectedValue property to selectedBankAndMachineIdx.

Bind the named query to availableSerials, and bind the second dropdown's options dataset to this.

Bidirectionally bind the second dropdown's selectedValue to selectedSerial

5

Very much appreciate the response ryan and yes, Bank and Machine are integers.

Going to spend the day working through your response as I've gotta teach myself exactly what is going on here, but a few clarifying questions to help me on my way:

  • Am I correct in understanding that you just provided me with 3 different methods of achieving similar results? The first being a SQL method inside the dropdown, then a named query method, and last a SQL method using custom properties?

  • Or is the named query the script you refer to for looping through and creating a dataset? I'm trying to follow along and am slightly confused by the named query portion.

Apologies for not following you clearly, I'm not qualified yet to be doing this but I'm learning everyday.

6

Named queries are just queries that are defined in a special section of ignition, so they can be called from different places:
image

The script he mentioned is most likely a transform script: You'd bind a custom property to your named query, then bind a dropdown to that custom property and add a script transform to process the dataset to build the options.
Then you'd bind the second dropdown to another named query, passing as parameters the values from the first dropdown.

tip: Dropdowns values can be more than just a single value, they can actually be dictionaries.

7

This is tagged vision and 7.9, so they would need to utilize a property change script instead.

I have provided 1 method of achieving your overall end goal, but have mentioned 2 methods of populating the first dropdown, a single comprehensive query and single simple query with property change script.

The usage of a named query for the second dropdown is common between either method used to populate the first dropdown.

Honestly, I would take the first comprehensive query and make it a named query as well.