This article describes a method on how to implement dynamic quickpicks for form elements in Oracles Application Express.

Use case: A specific use-case involves a calendar-application containing events of different types. For every event-type there were several but few recurring events with the same name. To improve the usability the use of quickpicks was considered. The requirement there however was to dynamically show only these quickpicks that regard to the selected event-type.

Example: In our example here, I replaced the Department of the DEPT-table will be the substitute for the event-type. After selecting a department, the employees regarding that department should be dynamically displayed as quickpicks below a simple text-field. The result then looks like:

Before presenting my solution to the problem, it should be mentioned that there are already plugins available that do exactly that - like the Dynamic Quick Picks plugin.

Solution: Only few things are neccesary as you see in the following tree-view.

We need a select-item for the department (P31_DEPARTMENT), the text-field (P31_DEMO) and a hidden item that temporarily stores the quickpicks (P31_QUICKPICKS). A dynamic action that triggers onchange of the Department-select-box is required. The first action then is to set the value of P31_QUICKPICKS with all quickpicks.

Beware that Escape Special Characters is set to No. Setting the value is done by calling a background-function stored in the database called GET_QUICKPICKS.

create or replace function GET_QUICKPICKS (p_deptno in NUMBER)
return VARCHAR2
as
  l_quickpick VARCHAR2(100);
  l_return CLOB;
  cursor c_quickpicks is 
    select ENAME from ( select e.ENAME from EMP e where e.DEPTNO = p_deptno )
    order by ENAME asc;

begin
  open c_quickpicks;
  loop
    fetch c_quickpicks into l_quickpick;
    exit when c_quickpicks%NOTFOUND;
    if l_return is not null then l_return := l_return || ', '; end if;
    l_return := l_return || '<a href="javascript:$s(''P31_DEMO'', '''
                         || l_quickpick || ''');">[' || l_quickpick || ']</a>';
  end loop;
  close c_quickpicks;
  return l_return;

exception when OTHERS then
  return null;
end;

This function does nothing more that getting the names of all employees for a given department. These names are then encapsuled in

<a href="javascript:$s('P31_DEMO', name);">[name]</a>

With that the text-field P31_DEMO will be set to name after clicking on one of the quickpicks. The $s function is part of Apex's Javascript API.

Now that all quickpicks are stored in P31_QUICKPICKS - that HTML-part need to be appended to the item P1_DEMO to be visible to the user. To accomplish that, some lines of Javascript-code are neccessary that will be written in the second action of our dynamic action.

// get the quickpicks that were previously stored in P31_QUICKPICKS
var quickpicks = apex.item("P31_QUICKPICKS").getValue(); 
if (quickpicks == "") quickpicks = " ";

// remove any quickpicks that were visible before
$(".quickpicks").remove();  

// append the new quickpicks after the text-field-item P31_DEMO and encapsule it in a div-tag (class="quickpicks" is used to find that div later to remove it again)
$("#P31_DEMO").parent().append('<div class="quickpicks">' + quickpicks + '</div>'); 

That is all the magic. No submit-action or refresh is needed with this approach. You may try it out yourself going to the Live-Example.