Anyone know what needs to be done to enable the use the LOV Option “Force users to filter values before use”? Option is grayed out in both the DF and BL.
BOE 4.2 SP8 P1 (saw the same behavior on SP7 P5), connecting to Oracle 11G, LOV is based on custom SQL.
We have LOVs that contain hundreds of thousands of values (think medical codes) and really need to have the users do a preliminary filter before trying to use the LOV.
Have you tried putting a prompt in the SQL for the filter? My thinking is you have to specify what they are to filter on in order to force them to do so.
I’ve tried that. It’s ugly - the “filter” prompt shows up as an entry in list of prompts. It also doesn’t allow for multiple selections. If I do the initial search for “fugue” then pick from my filtered LOV, if I go back to the filter to do another search, it clears out the values already selected in the LOV.
you can use cascading filter/LOV to guide the user
I need users to be able to search for a string that can be anywhere in the description field, there is no hierarchy to use to cascade the values. Unfortunately filtering values isn’t available in the lov hierarchies either. Plus, you can only display single fields, not the codes and descriptions and statuses I need to present to the users.
The only thing I’ve found that enables the force filter is to use the default LOV. But the list isn’t sorted and only includes that description field, I can’t add the code or status. It’s ugly and not a user friendly option.
I’m not privy to the IDT (Our Admin is obviously, but waiting on updates and additions can be 'out of scope)
As a report developer, I have come up with a way to use Input Control as a search using wildcard; therefore, we can search a given field that matches whatever the input control specifies. It has pros and cons, but in my case I am returning everything up front already, so there isn’t a performance gain to be had. They use the input control to create groups of like items together for there respective outputs. Several different groups may use one file to accomplish different task because the input control gives them the ability to search their needed information.
If this seems like a viable solution, reply and I’ll post a sample on how I came up with the wildcard search function.
E.G. Looking for Vendor Name that contains the word body. Type ‘body’ in the input control and the data filters to every record where the characters “body” (Upper or lower or camel) appear anywhere in the Vendor Name.
the cascading filters have not to be in any hierarchy-dependency.
create a dummy input-prompt/parameter for a string-value with e.g. the default %mysearchtext%
and use this parameter as a pattern-filter in your LOV
you can compare all of your desired LOV-fields with this pattern within a OR-condition
so every field will be checked if ‘mysearchtext‘ is found there before showing the LOV.
when starting a query the pattern-prompt will be shown at first
and after entering the text, the second prompt is only showing these values.
Sounds like you’re talking about adding a prompt in the SQL. I tried it, it’s ugly/not user friendly (the “filter” shows as a prompt) and, the bigger problem is that it doesn’t work for multiple searches for multiple selections.
Input controls are a possible work around for small data sets, but aren’t feasible working with large quantities of data. We’d be pulling in hundreds of thousands of rows into reports if the data isn’t filtered at the query level. We also schedule a lot of reports, so users aren’t interacting with them in WebI.
it’s not clear how your filtering should look like 
“…we really need to have the users do a preliminary filter before trying to use the LOV…”
–> " …I tried it, it’s ugly/not user friendly (the “filter” shows as a prompt) …"
“… We also schedule a lot of reports, so users aren’t interacting with them in WebI. …”
how/where do you want to do the filtering ?
I want to figure out how to get the force filter option to work with lovs built in the unx… The way it is supposed to work is that when you select a prompt, instead of loading a lov with hundreds of thousands of values, it asks the user to enter a search string, wildcards are supported — it actually gives users use instructions inside the lov list box when they click on the prompt. After the search string is entered, it then displays the lov filtered for the string entered. It’s all self contained within one prompt. Multiple searches can be done for “inlist” needs. And, when constructing the LOV, you can add additional columns and sorts to the lov display for usability. It’s supposed to be out of the box functionality, I just can’t figure out how to enable the option.