Cascading Shuttle Boxes

From the first time I started working with Oracle Application Express (several years back) I liked it. Only few small things bothered me at time, but with every version it always got better. For example I hated tabular forms, but love to use interactive grids now. The only thing I don’t recall ever changing are cascading list of values. I still use them, but some native behaviour bothers me every time I implement them. Mainly I’m speaking about the fact, if you change parent list, all cascading child lists are refreshed and all selected values are cleared. In select lists it can be overlooked, but where it infuriate me are shuttle boxes, where – in cascade you might have several shuttle boxes, selected dozens items, just to be cleared if you remove one item from the parent box. This problem lead to article you’re about to read.

Let’s pretend we have three tables – list of consoles, list of games and reference table between them (as each game can be released on multiple consoles and in the same time we can have multiple games for each console).

General gamer usually owns one or two consoles, so the best way how to look at this is to first select consoles you own and then see all the games which were published on either of the consoles like in the image below.

As you can see – we selected Nintendo Switch and PC and from the second box, we can see that League of Legends, The Legend of Zelda: Breath of the Wild and Witcher 3: Wild Hunt are the available games for at least one of the console. Now, it’s where the problem starts. Let’s pretend you select all the games, do some other things on the page, but then you return back and decide, you want the games only for PC. With this in mind you remove Nintendo Switch, but all your games deselect which might result in some other changes on the page and you can start filling it from the beginning. I want to prevent or at least minimize the effect of these changes with few lines of javascript on the page – so all games you selected before changing the parent will be still selected after parent change (if they can be selected).

First let’s have a look on the console shuttle box – named P2_CONSOLE. The settings are:

• Identification – Name: P2_CONSOLE
• Label – Label: Console
• Settings – Show Controls: Moving Only
• Advanced – CSS Classes: selected-values
• List of Values – Type: SQL Query (see below)

SELECT console, id
  FROM csb_console

Now on the games shuttle box – named P2_GAME. The settings are:

• Identification – Name: P2_GAME
• Label – Label: Game
• Settings – Show Controls: Moving Only
• Advanced – CSS Classes: selected-values
• List of Values
  • Type: SQL Query (see below)
  • Cascading LOV Parent Item(s): P2_CONSOLE
  • Items to Submit: P2_CONSOLE
  • Optimize Refresh: No

SELECT DISTINCT g.game
              , g.id
  FROM csb_game g
  JOIN csb_console_game_ref c ON (g.id = c.game_id)
 WHERE c.console_id IN (SELECT TRIM(REGEXP_SUBSTR(:P2_CONSOLE,'[^:]+', 1, LEVEL)) AS console
                          FROM DUAL
                       CONNECT BY REGEXP_SUBSTR(:P2_CONSOLE, '[^:]+', 1, LEVEL) IS NOT NULL)

Everything is normal so far, except for added css class “selected-values“, which we will utilize in dynamic action. Let’s name the dynamic action Shuttle Boxes and have it execute on Page Load, where the True action will be Execute JavaScript Code with execution option Fire on Initialization set to Yes.

// Alphabetical sort
function optionsSort($id) {

  var options_left  = $('select#' + $id + '_LEFT option'),
      options_right = $('select#' + $id + '_RIGHT option'),
      arr_left      = options_left.map(function(_, o) {
                        return {
                          t: $(o).text(),
                          v: o.value
                        };
                      }).get(),
      arr_right     = options_right.map(function(_, o) {
                        return {
                          t: $(o).text(),
                          v: o.value
                        };
                      }).get();

  arr_left.sort(function(o1, o2) {
    var t1 = o1.t.toLowerCase(),
        t2 = o2.t.toLowerCase();
    return o1.t > o2.t ? 1 : o1.t < o2.t ? -1 : 0; });

  arr_right.sort(function(o1, o2) {
    var t1 = o1.t.toLowerCase(),
        t2 = o2.t.toLowerCase();
    return o1.t > o2.t ? 1 : o1.t < o2.t ? -1 : 0; });

  options_left.each(function(i, o) {
    o.value = arr_left[i].v;
    $(o).text(arr_left[i].t);
  });

  options_right.each(function(i, o) {
    o.value = arr_right[i].v;
    $(o).text(arr_right[i].t);
  });

}

// Set list of shuttles based on cascade
var shuttleBoxes = ['P2_CONSOLE', 'P2_GAME'];

// Cycle through the list
for(var i = 0; i < shuttleBoxes.length; i++){

  // Delete previous values in session storage
  sessionStorage.removeItem(shuttleBoxes[i]);
  sessionStorage.removeItem(shuttleBoxes[i] + '_CALL');

  // Initial sort
  optionsSort(shuttleBoxes[i]);

  // Watching before refresh event
  $('#' + shuttleBoxes[i]).on('apexbeforerefresh', function(e){

    // Variables
    var $this     = $(this),
        $id       = $this.attr('id'),
        $call     = (Number(sessionStorage.getItem($id + '_CALL')) || 0) + 1,
        $order    = shuttleBoxes.indexOf($id),
        $values   = [],
        $storage  = [];

    // Get selected values
    $this.find('.selected-values option').each(function() {
      $values.push($(this).val());
    });

    // If there are values in session storage, get them, concat with current and remove duplicates
    $storage = (sessionStorage.getItem($id) ? sessionStorage.getItem($id).split(',') : []);
    $values  = $values.concat($storage);
    $values  = $values.reduce(function(a,b){if(a.indexOf(b)<0)a.push(b);return a;},[]);

    // Save values under the item name into session storrage
    sessionStorage.setItem($id, $values.join(',').trim());
    sessionStorage.setItem($id + '_CALL', $call);

  });

  // Watching after refresh event
  $('#' + shuttleBoxes[i]).on('apexafterrefresh', function(e) {

    // Variables
    var $this     = $(this),
        $id       = $this.attr('id'),
        $values   = (sessionStorage.getItem($id) ? sessionStorage.getItem($id).split(',') : []),
        $call     = (Number(sessionStorage.getItem($id + '_CALL')) || 0),
        $order    = shuttleBoxes.indexOf($id);

    // If the order and the call is equal, set again the values
    if ($call === $order || !sessionStorage.getItem(shuttleBoxes[($order - $call)] + '_CALL')) {

      apex.item($id).setValue($values.join(':'));
      optionsSort($id);
      sessionStorage.removeItem($id);
      sessionStorage.removeItem($id + '_CALL');

    }

  });

};

With this code you’ll be set for the success in what we want to achieve, so if in the example above you have Nintendo Switch and PC with all games selected, if you deselect Nintendo Switch, then based on the game-console relationship both Witcher 3 and League of Legends will remain and be still selected.

Surely you won’t be satisfied with the “it works, don’t look too deep into it” so let’s dissect the code if only just by little:

• Lines 1 – 39: Custom function called in the code to sort both left and right sides of shuttle box alphabetically. Only parameter needed to supply is the item ID (e.g. P2_CONSOLE).
• Line 42: Main setting of this script – here is the place for all shuttle boxes in the cascade – the parent first, then child, then child’s child and so on…
• Lines 45 – 103: Cycle through the list of shuttle boxes to attach events to them.
  • Lines 48-49: Remove any old stuff from browser session storage
  • Lines 52: Calling the function to sort the item
  • Lines 55 – 79: Registering to watch for apexbeforerefresh trigger. This is triggered before the item is refresh (by parent being changed) and during the refresh it clears all data, so here we still have access to all selected values. We save these values into session storage with number of how many the trigger has been triggered already.
  • Lines 82 – 99: Registering to watch for apexafterrefresh trigger. This event is triggered after the refresh is finished, which means we already refreshed the shuttle box and all available options are now in the left shuttle box ready to be selected. We do some checks and then we get all values from the session storage, set the item values equals this list of items and sort the result. Note: Yes – in the list of the selected values in session storage might be some which are no longer available (in the example it would be: The Legend of Zelda: Breath of the Wild), but if you’re calling it by apex.item(itemID).setValues(…) it’ll automatically discard all not available options.

With this being said, it’s by all means no perfect solution, and it might be working for few examples, but it might give you the idea how to implement such things for yourself. This case is overly complicated for simple 2 shuttle box cascade, but we had more than one level of the cascade, with level three depending both on level one and two, so that’s one of the reasons for the check on line 92 and for making it so complicated.

Did you encounter similar problem? Have you approached it in different, maybe more efficient way? Share your experience down below in the comment section, or if you have any question I’ll be glad to answer them to best of my knowledge. Also as I promised in my previous post, all my future post (where applicable) will have the link to example – so here you go: https://apex.oracle.com/pls/apex/f?p=70928:2:15012854560083::NO:::.