Column Actions

Multi-Select Checkbox-List
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.


Limitations
If the List of return-value is greater than 4000 characters, an error occurs. Reason is the 4000 character limitation of Oracle VARCHAR2 Objects.

Solution
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

Usage

• 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
  IS
  l_ret_tab apex_application_global.vc_arr2;
  BEGIN
  l_ret_tab := apex_util.string_to_table(p_delimited_str_list, p_value_delimiter);
  for i in 1..l_ret_tab.COUNT loop
  PIPE ROW(value_list_to(l_ret_tab(i)));
  end loop;
  return;
  END parse_ms_valuelist_to_sql;
  
• Create the SQL-Statement for the report:
  select * from emp e
  where EXISTS
    (select 1
     from table(parse_ms_valuelist_to_sql(:P8_DEPARTMENT_FILTER))
     where retval = e.deptno)
  and EXISTS
    (select 1
     from table(parse_ms_valuelist_to_sql(:P8_JOB_FILTER))
     where retval = e.job)