How do I retrieve selected items from a multi-select lov and compare them to a predetermined list as a dynamic action in oracle apex 18.2?

Question

My problem is with showing or hiding a Switch page item based on a users choices in an LOV. My goal is to only display this Switch if the user chooses a relevant item from the LOV. For example, if the user makes two selections "dog" and "cat", then I want the Yes/No switch to appear. If they choose "elephant" then I do not want it.

I have created a Dynamic Action with the client-side condition of "Item is in list" on the LOV item and a list of "23,24". Everything works fine as long as I only select one entry from the LOV. As soon as I select an additional entry the Switch disappears (because the False action of Hide kicked in).

I suspect this won't work because of the multi-select LOV. It is likely building a comparison behind-the-scenes like "two-values IN (23,24)". Could I use the "JavaScript expression" option instead of "Item is in list" to make this work? If so, an example would be greatly appreciated!

Basically, I want to code, "out of all the selections the user makes, if item 23 and/or 24 are part of them, then display the Switch".

Littlefoot Littlefoot · Accepted Answer · 2020-09-29T05:21:19

How about such a code? It would be a function that returns Boolean condition type. See comments within.

declare
  l_max number(1);
begin
  with lovs as
    -- split multi-selected values into rows
    (select regexp_substr(:P1_LOV_ITEM', '[^:]+', 1, level) val
     from dual
     connect by level <= regexp_count(:P1_LOV_ITEM, ':') + 1
    )
  -- check whether any of extracted LOV values exists in list you're interested in (23, 24)
  select max(1)
    into l_max
    from lovs l
    where l.val in (23, 24);

  -- if some of values exists, return TRUE and - thus - display the Switch item
  return l_max = 1;
end;

How do I retrieve selected items from a multi-select lov and compare them to a predetermined list as a dynamic action in oracle apex 18.2?

2 Answers