# Dropdown menu that dictates next drop down **URL:** \ **Category:** Ignition\ **Tags:** ignition79, vision\ **Created:** [December 2, 2024, 11:17pm UTC](https://forum.inductiveautomation.com/t/dropdown-menu-that-dictates-next-drop-down/99340 "2024-12-02T23:17:51Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![karmspabs](https://avatars.discourse-cdn.com/v4/letter/k/d6d6ee/32.png) [@karmspabs](https://forum.inductiveautomation.com/u/karmspabs)\ **Post date:** [December 2, 2024, 11:17pm UTC](https://forum.inductiveautomation.com/t/dropdown-menu-that-dictates-next-drop-down/99340/1 "2024-12-02T23:17:51Z")
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. ---
**Author:** ![ryan.white](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan.white/32/73193_2.png) [@ryan.white](https://forum.inductiveautomation.com/u/ryan.white)\ **Post date:** [December 2, 2024, 11:49pm UTC](https://forum.inductiveautomation.com/t/dropdown-menu-that-dictates-next-drop-down/99340/2 "2024-12-02T23:49:31Z")
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. ---
**Author:** ![karmspabs](https://avatars.discourse-cdn.com/v4/letter/k/d6d6ee/32.png) [@karmspabs](https://forum.inductiveautomation.com/u/karmspabs)\ **Post date:** [December 3, 2024, 12:03am UTC](https://forum.inductiveautomation.com/t/dropdown-menu-that-dictates-next-drop-down/99340/3 "2024-12-03T00:03:09Z")
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: ```python 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. ---
**Author:** ![ryan.white](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan.white/32/73193_2.png) [@ryan.white](https://forum.inductiveautomation.com/u/ryan.white)\ **Post date:** [December 3, 2024, 12:50am UTC](https://forum.inductiveautomation.com/t/dropdown-menu-that-dictates-next-drop-down/99340/4 "2024-12-03T00:50:24Z")
Am I correct in assuming that both `Bank` and `Machine` are integers in the DB table? ```sql 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 ```sql 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 ```sql 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 ```python lookup({path.to.first.dropdown.options}, {first.dropdown.select.value}, Null, 'value', 'Bank') ``` and ```python 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` ---
**Author:** ![karmspabs](https://avatars.discourse-cdn.com/v4/letter/k/d6d6ee/32.png) [@karmspabs](https://forum.inductiveautomation.com/u/karmspabs)\ **Post date:** [December 3, 2024, 4:54pm UTC](https://forum.inductiveautomation.com/t/dropdown-menu-that-dictates-next-drop-down/99340/5 "2024-12-03T16:54:47Z")
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. ---
**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:** [December 3, 2024, 6:01pm UTC](https://forum.inductiveautomation.com/t/dropdown-menu-that-dictates-next-drop-down/99340/6 "2024-12-03T18:01:13Z")
Named queries are just queries that are defined in a special section of ignition, so they can be called from different places: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/b/7b9167d7864590a5a4bade716a7c8d1ebb04f4fd.png) 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. ---
**Author:** ![ryan.white](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan.white/32/73193_2.png) [@ryan.white](https://forum.inductiveautomation.com/u/ryan.white)\ **Post date:** [December 3, 2024, 6:06pm UTC](https://forum.inductiveautomation.com/t/dropdown-menu-that-dictates-next-drop-down/99340/7 "2024-12-03T18:06:34Z")
> [@pascal.fragnoud](#): > > 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. This is tagged vision and 7.9, so they would need to utilize a property change script instead. > [@karmspabs](#): > > Am I correct in understanding that you just provided me with 3 different methods of achieving similar results? 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. > **[Named Queries | Ignition User Manual](https://www.docs.inductiveautomation.com/docs/7.9/sql-in-ignition/named-queries)** > > What are Named Queries? > **[Named Query Binding | Ignition User Manual](https://www.docs.inductiveautomation.com/docs/7.9/visualization-and-dashboards/property-binding-types/named-query-binding)** > > Binding Properties to a Named Query Honestly, I would take the first comprehensive query and make it a named query as well.