How to display selected items first when editing an Oracle Apex "Select List" page item

Question

I'm using Apex 19.1 on an 18.c database. I have a page item with a type of Select List (P136_ROLES). The page item has Allow Multi Selection = YES. The data in the P136_ROLES item needs to be updated on occasion. When the page item is displayed in Edit mode, I'd like to show the selected items first, then show the rest of the (unselected) LOV alphabetically. Two tables are involved with this page item:

AFF_ROLE

• PRIM_KEY (unique key for rows in this table)
• ROLE (The name of various roles in the organizaiton

AFF_CONTACT_ROLE

• PRIM_KEY (unique key for rows in this table)
• CONTACT_FKEY (A foreign key matching the PRIM_KEY in the AFF_CONTACT table)
• ROLE (A role from the AFF_ROLE table assigned to the given contact

The AFF_CONTACT_ROLE table is updated through a PL/SQL page process:

DECLARE
   l_selected         apex_t_varchar2;
BEGIN
-- Convert the colon separated string of values into a PL/SQL array
   l_selected := apex_string.split(p_str => :P136_ROLES, p_sep => ':');
-- Delete all prior role assignments for this contact.
   Delete From AFF_CONTACT_ROLE
       Where contact_fkey = :P136_PRIM_KEY;
-- Loop over array to insert row(s) containing contact_fkey and role
   FOR i IN 1..l_selected.count 
   LOOP
   Insert into AFF_CONTACT_ROLE (contact_fkey, role)
           Values (:P136_PRIM_KEY, l_selected(i));
   END LOOP;
END;

Here is the LOV for P136_ROLES:

Select role display, role return From AFF_ROLE;

Here is the source for P136_ROLES: (SQL Query return colon separated values):

Select role From AFF_CONTACT_ROLE Where contact_fkey = :P136_prim_key;

This source displays the roles that have been assigned to the contact, but the selected roles are hard to determine on first glance. I've also tried the following for a Source, but it gives similar results as the Select statement listed above:

select ar.role 
 from     aff_role ar
 right join aff_contact_role acr on acr.role = ar.role
                                and acr.contact_fkey = :p136_prim_key
 order by
    ar.role, 
    acr.role nulls last;

In a page item with the type of Select List, how do I display selected values first, then unselected alphabetically-sorted values next? Thanks for looking at this.

Answer 1

This works - I just crafted this together based on one of the answers here. It uses jquery to re-arranged the values in the select list, ordering the selected ones before the others. In page attribute "Function and Global Variable Declaration", put:

function sortSelectOptions(selector, skip_first) {
    var options = (skip_first) ? $(selector + ' option:not(:first)') : $(selector + ' option');
    var arr = options.map(function(_, o) { return { t: $(o).text(), v: o.value, s: $(o).prop('selected') }; }).get();
    arr.sort(function(o1, o2) {
      var t1 = o1.s ? o1.t.toLowerCase() : 'zz'+ o1.t.toLowerCase()
        , t2 = o2.s ? o2.t.toLowerCase() : 'zz'+ o2.t.toLowerCase();
      return t1 > t2 ? 1 : t1 < t2 ? -1 : 0;
    }); 
    options.each(function(i, o) {
        o.value = arr[i].v;
        $(o).text(arr[i].t);
        if (arr[i].s) {
            $(o).attr('selected', 'selected').prop('selected', true);
        } else {
            $(o).removeAttr('selected');
            $(o).prop('selected', false);
        }
    }); 
}

In "Execute when Page Loads", put:

jQuery(document).ready(function($) { 
    sortSelectOptions('#P136_ROLES', true); 
});

The 2nd parameter parameter needs to be true if you have a NULL value in your select list so that doesn't get sorted with the other options.

I'm a very lousy javascript programmer so not claiming this is a foolproof solution, but it should put you on the right track.