This demo shows 2 filter items based on a List-of-values with multi-select lists based on checkboxes.
Check it out to see how it works.
For example: Click both "All"-selectors and press button "Apply Filter" to see the Employee-Report. Than click "All"-selector on "Job Filter" and check only 1 or 2 values, e.g. MANAGER and PRESIDENT. Than click "Apply Filter" to see the result set.
Both filter items are based on my "Multi-Select-Checkbox-List" APEX PlugIn. This PlugIn can easily imported into your APEX Application and than used as a normal APEX-Item.
Enabling cascading LOV Feature
To enable cascading LOV Feature go to "List of Values" Section, extend your LOV-Query by adding the Where-Clause condition column and add all items to submit to "Cascading LOV Parent Item(s)" and "Page Items to Submit" properties.
Enabling Pre-Search Option
You can add a Pre-Search Item for this checkbox list by changing the property "Enable Pre-Search Option" in "Settings" Section to "Yes".
Check out this feature by typing any text into the text item above the Job-Filter checkbox list. E.g. type in "ana" and you should see a pre-filtered checkbox list that shows only "ANALYST" and "MANAGER". Change the value once more to see how it works.
If the List of return-value is greater than 4000 characters, an error occurs. Reason is the 4000 character limitation of Oracle VARCHAR2 Objects.
This PlugIn renders a fieldset with an "All"-Selector, a Hidden-Item to store the return result and a div-Region with all LOVs as Checkboxes.
The return-value is a concatinated string delimited by a special seperator (in this case a ":"). The seperator can be changed at the item-settings region. So the result-string is something like this: ANALYST:CLERK:MANAGER
- Download and import the "Multi-Select-Checkbox-List" APEX PlugIn
- Create an Object-Type like this:
CREATE TYPE value_list_to AS OBJECT (retval VARCHAR2(4000));
- Create an Collection type which uses the object-type:
CREATE TYPE value_list_tt AS TABLE OF value_list_to;
- Create a function to convert the return-values into a PL/SQL-Table:
CREATE OR REPLACE FUNCTION parse_ms_valuelist_to_sql(p_delimited_str_list IN VARCHAR2
,p_value_delimiter IN VARCHAR2 DEFAULT ':')
RETURN value_list_tt pipelined
l_ret_tab := apex_util.string_to_table(p_delimited_str_list, p_value_delimiter);
for i in 1..l_ret_tab.COUNT loop
- Create the SQL-Statement for the report:
select * from emp e
where retval = e.deptno)
where retval = e.job)