Home > APEX, Oracle, PL/SQL, SQL > Enhanced APEX Shuttle Item

Enhanced APEX Shuttle Item

Recently I have been really, really busy building some internal applications for my company. All of them using Oracle and APEX of course. To satisfy some of application’s requirements, and just for usability sake, I had to tweek the default behavior of the APEX shuttle item. The reason was the initial LOV that populated the shuttle contained over 500 values. About 490+ too many as far as I was concerned. The users felt the same way. They complained that it was too hard to find what they were looking for, even though the list was in alphabetical order and I agreed. So, I decided to add the functionality to allow the user to constrain the values in left-hand side of the shuttle. Similar to the Popup LOVs with a filter option, but I wanted to use AJAX to accomplish it to avoid a page submit. After a bunch of playing around and optimization, here is what I came up with:

http://apex.oracle.com/pls/otn/f?p=19903:10
Username/Password: demo/demo

I was surprised that it was not that hard to accomplish and there are very few moving parts. All you need is a javascript function to make the AJAX call, an On Demand process to fetch only those values that meet the filter’s constraint and a way to trigger it.

How It Works.

As the user types in the textbox the shuttle automagically filters itself. I use an onKeyUp event on the textbox P10_SEARCH_BY to trigger the AJAX call.

onKeyUp="populateShuttle( this,'P10_SHUTTLE');"

So everytime a KeyUp event happens in that textbox, a call to the populateShuttle function happens. That function is responsible for making the AJAX call to the On-Demand Process, which fetches the appropriate values, and then parsing the results and repopulating the left hand side of the shuttle.

function populateShuttle(filter,shuttleName)
{
  var right = $x(shuttleName+"_RIGHT");

  for ( var i=0;i<right.length; i++ )
    right.options[i].selected = true;

  var req = new htmldb_Get( null, 19903, 
                          'APPLICATION_PROCESS=populateShuttle', 0 );

  req.addParam( 'x01', $v(right) );
  req.addParam( 'x02', $v(filter) );

  var resp = eval(req.get());

  var left = $x(shuttleName+"_LEFT");
  left.length=0;

  if (resp)
    for ( var i=0; i<resp.length; i++ )
      left.options[i] = new Option(resp[i].data, resp[i].id);	

  req = null;
}

Although APEX treats the shuttle as a single element, it is in fact made up of many HTML elements, 10 images and 2 multiselect list boxes. With a little poking around the HTML of a generated APEX page with a shuttle item on it, I was able to determine that the actual DOM names of the multiselect list boxes of a shuttle were [SHUTTLE_NAME]_LEFT and [SHUTTLE_NAME]_RIGHT. How convenient. Accessing the actual elements was then trivial. I just used the APEX supplied function $x() function to get the elements.

The first thing I do is get the right textbox and loop over its values, selecting each one. This is so when I fetch its value, I get all the elements in the list. I need this information so when I fetch the values for the left hand side of the shuttle, based on the user’s filter, I do NOT bring back any values that have already been moved to the right hand side of the shuttle. In my example say you had already selected KING and moved him to the right. Then you type a ‘K’ in the filter_by textbox, you will only get back CLARK and BLAKE even though KING also meets the constraint of containing a ‘K’.
Next, I get the value of the right hand side of the shuttle and the value of the filter_by textbox, using the $v() function and send the values to the On Demand process via the APEX global variables g_x01 and g_x02.

The On Demand process is just a simple PL/SQL block that is a query loop.

declare
  l_selected long  := wwv_flow.g_x01;
  l_search long := wwv_flow.g_x02;
begin
  htp.prn('[');
  for c in (
    select ename, empno, rownum r
      from emp
     where regexp_like( ename, nvl(l_search,ename), 'i' )
       and nvl(instr(':'||l_selected||':',':'||empno||':'),0)=0 )
  loop
    htp.prn( case when c.r&gt;1 then',' else null end ||
             '{ id: ' || c.empno ||
             ', data: "' || c.ename || '"}' );
  end loop;
  htp.prn(']');
end;

It fetches all the values that match the filter

where regexp_like( ename, nvl(l_search,ename), 'i' )

but not those already be selected by the user.

and nvl(instr(':'||l_selected||':',':'||empno||':'),0)=0)

Now it’s just a simple process of looping over the rows, packaging them up and sending them back. I have become a big fan of JSON ( JavaScript Object Notation http://www.json.org/ ) , so that how I am packaging up the payload to pass back. Basically, I’m making the results into a javascript array of objects. Each object has an id and data element. The payload look something like this

[{id: 7839, data: "KING"}{id: 7698, data: "BLAKE"} ... ]

This makes it super simple easy to work with once it gets back to the calling javascript function. All you need to do is apply an eval() on it and its transformed into a true javascript array of objects that can be easily looped over and referenced.

In one line of code, I call the On Demand process, getting back the JSON packed result and eval() it.

var resp = eval(req.get());

Now the variable resp is an Array of objects with two elements in each object, id and data. Looping over the values and populating the left hand side of the shuttle is trivial now.

  if (resp)
    for ( var i=0; i<resp.length; i++ )
      left.options[i] = new Option(resp[i].data, resp[i].id);

And there you have it. I hope I have shown that with some minor tweeks, you can extend the functionality of the base APEX items to be much more functional and user friendly. As always, comments ( good and bad ) are always welcome.

About these ads
Categories: APEX, Oracle, PL/SQL, SQL Tags: , , , ,
  1. Stew Ashton
    April 28, 2009 at 6:14 am

    Hi Christopher,

    I tried out the Enhanced Shuttle on Firefox 3.0.9 (Windows Vista) and the left-hand side of P10_SHUTTLE goes empty as soon as I type something in P10_FILTER_BY. It stays empty thereafter.

    • Christopher Beck
      April 28, 2009 at 10:10 am

      Give it a whirl again. You will have to login. demo/demo is the user/password.

  2. Stew Ashton
    April 28, 2009 at 12:23 pm

    Works fine now. Nice post, thanks !

    • mansi
      June 24, 2010 at 8:44 am

      Hi,

      For me too the shuttle goes empty as soon as I type anything in the filter. It stays empty thereafter. Could anyone pls help.

      • Christopher Beck
        June 24, 2010 at 12:16 pm

        Kinda hard to debug without looking at what you implemented. Verify that you have the proper SQL in the On-Demand process. If it appears so, then see if you can supply any other info to help me help you debug it.

      • tasos
        June 24, 2010 at 12:57 pm

        I had the same problem. The error is probably in your sql. Try this to test.
        create a table

        CREATE TABLE “USERS”
        ( “NAME” VARCHAR2(20),
        “ID” VARCHAR2(30),
        “DEPARTMENT” VARCHAR2(150),
        “STATUS” VARCHAR2(30)
        )

        and add records
        insert into users (name,id,department,status)values(‘bruce’,’22342′,’computing’,’staff’)
        insert into users (name,id,department,status)values(‘steve’,’11342′,’computing’,’staff’) etc.

        the process then is

        declare
        l_selected long := wwv_flow.g_x01;
        l_search long := wwv_flow.g_x02;
        begin
        htp.prn(‘[');
        for c in (
        select name, id, rownum r
        from users
        where regexp_like(name, nvl(l_search,name), 'i' )
        and nvl(instr(':'||l_selected||':',':'||id||':'),0)=0 and department='computing' and status='staff'
        )

        loop
        htp.prn( case when c.r>1 then',' else null end ||
        '{ id: ' || c.id||
        ', data: "' || c.name || '"}' );
        end loop;
        htp.prn(']‘);
        end;

        Your javascript

        function pop(filter)
        {

        var req = new htmldb_Get(null,&APP_ID.,’APPLICATION_PROCESS=populate’,0);

        var left = $x(‘P6_SELECT_PANEL1_LEFT’);
        var right = $x(‘P6_SELECT_PANEL1_RIGHT’);

        for ( var i=0;i<right.length; i++ )
        {right.options[i].selected = true;}

        req.addParam( 'x01', $v(right) );
        req.addParam( 'x02', $v(filter) );

        var resp = (eval(req.get()));

        left.length=0;

        if (resp)
        for ( var i=0; i<resp.length; i++ )
        left.options[i] = new Option(resp[i].data, resp[i].id);

        req = null;
        }

        and finally on HTML Form Element Attributes of your item

        onKeyUp=”pop( this);”

      • mansi
        June 24, 2010 at 2:14 pm

        It worked. I had added a condition for my process.

      • mansi
        June 24, 2010 at 2:16 pm

        It worked. I had added a condition for my process that should not have been there.

        I have another query now. Is there a way I can capture which item is selected on the left shuttle? I can then fetch more details for the record and display them in another region on the same page.

  3. Stew Stryker
    April 28, 2009 at 12:29 pm

    Christopher,

    Very nice! I’m especially impressed that there isn’t *that much* to implementing it, though I’m sure it took you a few hours to analyze, code and implement.

    I’d love to see the popup LOV (key + description) update dynamically the way this does.

    Thanks for sharing,

    Stew

  4. tasos
    June 15, 2010 at 2:29 pm

    Great job. After selecting dates can you store them in a DB table? For example, if you want to make appointments on the selected days.

    • Christopher Beck
      June 15, 2010 at 2:41 pm

      Sure. I did not change the shuttle at all, just what/how it displays its selections. You will still need to do the same processing you would always have had to to store the results of a shuttle.

      Briefly…

      In a page process, make a PL/SQL process – On Submit and add the following code:

      declare
      l_selected APEX_APPLICATION_GLOBAL.VC_ARR2;
      begin
      l_selected := apex_util.string)to_table( :MY_SHUTTLE );
      for i in 1 .. l_selected.count loop
      insert into my_table ( my_column )
      values ( l_selected(i) );
      end loop;
      end;

      You’ll have to enhance this to suit your needs.

  5. Jon
    September 15, 2010 at 1:37 pm

    Spent a bit of time getting your solution to work which looked pretty good, then realised in Apex 4.0 it is possible to do with cascading LOVs and no coding.

    • Christopher Beck
      September 15, 2010 at 2:33 pm

      Glad it was helpful. But you are correct about APEX 4.0. It has made many things that used to be hard much, much easier.

  6. KP
    October 21, 2010 at 2:39 pm

    Is there a way to include a Horizontal scrollbar on the shuttle so that I can set the width of the shuttle? Thanks.

    • KP
      October 21, 2010 at 3:24 pm

      Specifically, when the width of the shuttle is set and the values in the shuttle are relatively long, they simply get cut off. I am hoping there is a way to show a horizontal scrollbar at the bottom of each list in an APEX shuttle.

    • Christopher Beck
      October 21, 2010 at 3:38 pm

      Yes, but I’m not a CSS expert. In theory you need to set a STYLE on each selectbox to enable horizontal scrolling. I’m sure with a little google’ing you can find the right style settings.

      If the shuttle is the only select box on your page, then you can use
      select {
      attr: val;
      }
      But if you need to identify just the shuttle’s select lists then the IDs are item_name_LEFT and item_name_RIGHT.
      #item_name_LEFT{
      attr: val;
      }
      #item_name_RIGHT{
      attr: val;
      }

      Sorry I can’t be of more help on this one.

  7. purplover
    February 1, 2012 at 3:29 pm

    I’m sorry – I am obviously missing something obvious here. Exactly where am I creating the on demand process, and does it have to have a certain name?

    • Christopher Beck
      February 1, 2012 at 6:26 pm

      The On Demand process is a created in the application’s shared components section. Go to Shared Components and in the top left box (Logic) there is an option for Application Processes. Click that and create a process and for the Point option select On Demand. And yes the name of the process needs to be the same as the name in put here

      var req = new htmldb_Get( null, 19903,’APPLICATION_PROCESS=populateShuttle’, 0 );
      ^^^^^^^^^^^^^^^

      Hope that helps.

  8. purplover
    February 1, 2012 at 9:39 pm

    Thank you! That is what I thought, but I was still getting an error – I had the function declaration in the HTML Header field instead of the javascript function field (duh!); once I moved it to the function field it worked beautifully!

    Thank you so much for posting this to help everyone!!

    • Christopher Beck
      February 1, 2012 at 11:38 pm

      Thanks. Glad you found it useful. Now I just need to find some time to convert this into an Apex plug-in.

  9. dopple
    April 3, 2012 at 3:13 pm

    Hi Christopher. I am using your example and can’t deny it is a really nice piece of code, but do you have any ideas as to how it can be sped up? Specifically the problem seems to be that if you type quickly, not all keypresses are captured.

    What I’m currently trying to do is make the onkeyup call an intermediate function which will wait a second (using setTimeout) and then call the main function.

    Have you any thoughts on this?

    • Christopher Beck
      April 3, 2012 at 4:35 pm

      Glad you like the code. I think I need to make this into an apex plug-in to just make it easier for people to use. Now only if I can find the time.

      As for your question, I have not experienced the issue you are seeing. But then again, I’m on a fast network and my table I am querying is indexed properly so the response seems instantaneous to me. I guess you could tweek the javascript to try and buffer the key strokes and make sure each is then sent via the ajax call in order. That will ensure you capture and display each one. Not sure how to go about that without more research. If you get it working, let me know and maybe I can include it in this example.

      • dopple
        April 4, 2012 at 9:00 am

        I managed to get this sorted by changing the populateShuttle function to do the dollowing. Although the ajax retrun is really fast, if you type faster than the data is returned, there is a slight lag. I’ll maybe try to post a screen cap of it happening tonight.

        function populateShuttle(filter,shuttleName) {

        var left = $x(shuttleName+”_LEFT”);
        var right = $x(shuttleName+”_RIGHT”);

        for ( var i=0;i<right.length; i++ ) {
        right.options[i].selected = true;
        }
        setTimeout(function() {
        var req = new htmldb_Get( null, &APP_ID., 'APPLICATION_PROCESS=populateShuttle', 0 );
        req.addParam( 'x01', $v(right) );
        req.addParam( 'x02', $v(filter) );

        var resp = eval(req.get());

        left.length=0;

        if (resp) {
        for ( var i=0; i<resp.length; i++ ) {
        left.options[i] = new Option(resp[i].data, resp[i].id);
        }
        }
        req = null;
        },500);
        }

  10. July 31, 2013 at 8:10 pm

    It’s a shame you don’t have a donate button! I’d definitely donate to this fantastic blog! I suppose for now i’ll settle for bookmarking and adding your RSS feed to
    my Google account. I look forward to brand new updates and will talk about this blog with my Facebook group.
    Talk soon!

  11. March 14, 2014 at 5:56 pm

    Pretty! This has been a really wonderful article.
    Thank you for providing these details.

  12. March 14, 2014 at 5:56 pm

    Excellent post. I absolutely love this site. Continue the good work!

  13. Tim
    April 1, 2014 at 10:55 pm

    Been at this for quite some time with no luck, I am new to apex so troubleshooting is often very cumbersome. Any help is appreciated.

    Function in HTML header:
    function populateShuttle(filter,shuttleName)
    {
    var left = $x(shuttleName+”_LEFT”);
    var right = $x(shuttleName+”_RIGHT”);

    for ( var i=0;i<right.length; i++ )
    right.options[i].selected = true;

    var req = new htmldb_Get( null, &APP_ID.,
    'APPLICATION_PROCESS=populateShuttle', 0 );

    req.addParam( 'x01', $v(right) );
    req.addParam( 'x02', $v(filter) );

    var resp = eval(req.get());

    left.length=0;

    if (resp)
    for ( var i=0; i1 then’,’ else null end ||
    ‘{ id: ‘ || c.first_name||
    ‘, data: “‘ || c.user_name|| ‘”}’ );
    end loop;
    htp.prn(‘]’);
    end;

    Shuttle = P99_SHUTTLE:
    select user_name d, user_name r
    from apex_workspace_apex_users

    Text Box = P99_FILTER w/ HTML form element attributes:
    onKeyUp=”populateShuttle( this,’P99_SHUTTLE’);”

    • Christopher Beck
      April 2, 2014 at 2:55 pm

      What issue are you having?

  14. Tim
    April 2, 2014 at 3:20 pm

    When I type in the filter, nothing happens in the shuttle. For example I have three names: IKATAJS, TSREED and BCHARMAN in the left hand side, but when I type in “t” or “i” into the filter box all three names remain.

    • Christopher Beck
      April 2, 2014 at 4:17 pm

      Do you have the onKeyUp event associated with the search text box?

      You can either hard code it in the page item itself or now with dynamic actions, you can just create a dynamic on the onKeyUp event, associate it with the search text box and have the event execute the javascript function populateShuttle.

      • Tim
        April 2, 2014 at 4:29 pm

        Yes, I have entered:
        onKeyUp=”populateShuttle( this,’P99_SHUTTLE’);” into the HTML Form Element Attributes on the search text box item, where P99_SHUTTLE is my shuttle item

  15. Tim
    April 2, 2014 at 3:25 pm

    my dynamic action looks like this:

    declare
    l_selected long := wwv_flow.g_x01;
    l_search long := wwv_flow.g_x02;
    begin
    htp.prn(‘[');
    for c in (
    select user_name, last_name, rownum r
    from apex_workspace_apex_users
    where regexp_like( user_name, nvl(l_search,user_name), 'i' )
    and nvl(instr(':'||l_selected||':',':'||last_name||':'),0)=0 )
    loop
    htp.prn( case when c.r>1 then',' else null end ||
    '{ id: ' || c.last_name||
    ', data: "' || c.user_name|| '"}' );
    end loop;
    htp.prn(']‘);
    end;

    perhaps I am having a problem using last_name instead of a numeric ID?

    • Koloo
      April 2, 2014 at 11:56 pm

      Hi,
      but in your process why are you using c.last_name for id: ? use pk from the table or something unique, also check your code in firebug it will show you the error because any error in on-demand process cant be captured by debugger…

      • Christopher Beck
        April 3, 2014 at 12:15 am

        Koloo,

        I took the debugging of Tim’s issue offline and emailed him directly and pointed out the same thing. The problem is, in that table, there is no ID. USER_NAME is the unique key. And when I debugged his code in the Chrome Developer Tool ( I don’t use firefox ), his issue was that since he was using a character string as the ID in the JSON object that he was passing back, it needed to be doulbe-quoted, just like the DATA element. So the simple solution to his problem was changing the Application Process and use this

        htp.prn( case when c.r>1 then’,’ else null end ||
        ‘{ id: “‘ || c.last_name|| ‘”‘ ||
        ‘, data: “‘ || c.user_name|| ‘”}’ );

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: