Dropdown Question

Ignition
1

I’m working with a dropdown menu and it is getting it’s data from a SQL query. When i start the program, it automatically selects one of the values. How do I make it so that ‘no selection’ is an option? And this option have a respective value? Any thoughts other than just creating a new entry in my Database that will pull it? Thanks so much!

2

Hi Alex!

Just ran into the same thing recently-- shooting my own foot there! :laughing:

In the designer, set the selected index to -1, then save the project.

3

So now I feel silly, because I can’t even find that option under my property editor :/ Does my selection mode need to be on a certain setting? It’s on lenient, and the no selection value is -1. Still only displaying the 3 results returned from the query and not a 4th, ‘

4

Sorry, Alex, I misspoke-- or mis-typed…

Selected Value is the one you want. It’s right underneath Data

5

Ok, I see that and I see what it is doing on startup, but is there a way to make it a permanent option? After I select an option, the a * , I would be able to essentially return all rows of a table.

Maybe there is an easier way to do this? :scratch:

6

I’m not sure if this will work in your project, but in my dropdown scripting I added the line:

event.source.selectedValue = -1

and it seemed to work for me. Sets the index back to -1 after it goes through the rest of the script.

7

Ah! I see what you mean, Alex.

I use something like this for the Data property:

SELECT 'All' UNION ALL SELECT DISTINCT column_name FROM table
This will put the option ‘All’ at the top. You can then modify your where clause to fit what you want to do.

Hope this helps!

8

This is the SQL Query for the table I have on my page

SELECT * FROM workorders WHERE clientname = '{Root Container.Dropdown.selectedStringValue}' AND problem LIKE '%{Root Container.Text Field.text}%'

My WHERE statement is looking at the dropdown menu for the value in client name. Since I do not have any clients with the name ‘ALL’ it won’t return all of the values. I’m sure there is a better way to do this. Honestly, it’s not a huge problem if i don’t get it working, it would just allow me to search across all entries based on the search parameter instead of limiting it to just the one client.

9

You can use a custom property to hold the WHERE clause.

if({Root Container.Dropdown.selectedStringValue}="All", "", CONCAT("WHERE column_name = ", {Root Container.Dropdown.selectedStringValue}) )

Then your query would be something like:

SELECT *
FROM table
{Root Container.whereClause}
10

Another solution would be to form your query like this.

SELECT * FROM workorders WHERE (clientname = '{Root Container.Dropdown.selectedStringValue}' OR '{Root Container.Dropdown.selectedStringValue}' = 'All') AND problem LIKE '%{Root Container.Text Field.text}%'