I am having an issue with a simple Dropdown component on perspective. I have it's options property binded to a column in an database table. The search function does not seem to work properly, when typing in the box to select an option, multiple options that do not contain the searchParam string will appear. I have tried to use a query parameter and bound it to the searchParam using
"SELECT columnName
FROM tableName
WHERE columnName LIKE '%' + :name + '%' "
However I still am getting a similar issue, it will work well the first time after startup, then if you deselect and try to search again, the options will be full of non-matches. The column is very big, so searching is an important feature for this dropdown box to work properly. If anyone has had similar issues or has an idea for a fix that would be greatly appreciated.
Q1. What flavour of SQL?
Q2. It looks like you are using a Named Query. Correct?
You could try,
SELECT columnName
FROM tableName
WHERE columnName LIKE CONCAT('%', :name, '%')
I suspect, however, that you are misusing the dropdown's search feature. The idea is that you generate the options list with a Query Binding which uses your Named Query. The dropdown's search feature just filters which dropdown options are displayed without the need to re-query the database. This should work for your requirements.
Please see Wiki - how to post code on this forum. Welcome to the forum!
It is Microsoft SQL Server 2019, and yes I am using a named query. You are correct about me misusing the search feature, however I am only trying this way of using a named query with a parameter because the normal dropdown filter search wasn't working either, so I decided to re-query to try to "force" the search to work. I tried using CONCAT as well but that didn't change anything.
The issue seems strange, as when I search for a specific option, some options will not get filtered out and stay in the option list - even though they are not similar to the searchParam string, but some options will filter out as soon as they deviate from the searchParam. One thing I noticed is when you mouseOver each of the options, the ones that don't seem to filter out will not change color, while the options that do filter out - aka the actual option you are searching for, will turn blue.
Sorry for the bad code format, and thanks for the welcome, I am new to ignition and have been spending my last few weeks figuring out ignition to the best of my ability.
Can you make a copy of the dropdown, remove the options binding, copy it from the Project Browser and paste it here (formatting as code)? That way we can try to reproduce your problem - if it still exists with a fixed set of options. If it does still exist can you whittle down the list of options to keep your post to a reasonable size while still demonstrating the fault?
If there's sensitive information in it then paste it into a text editor first and do a Find and Replace to obfuscate it.
Welp, I figured it out, duplicate entries. Having over 800 names we had lots of duplicates as the table was made through some sort of scripting and It was less than perfect. I just removed the binding and physically went through a text editor and removed all duplicates. Works like a charm. Not sure why the duplicates were causing that much of a problem though, it seems like they should still filter out of options when being searched for.
Thanks for the help, I removed all the name entry's to post it here and realized I had fixed the problem.
Good work and thanks for taking the time to post.
Hit the Solution link on your last post to mark it solved.