Tuesday, 18 April 2017

Oracle Apex Plug-in for restricting shuttle choices

The Oracle Apex shuttle item is a neat way to select a number of values. It is however not so user friendly when the list of choices is very long. In this case it is useful to be able to limit the list of choices.
When you implement the limitation in the LOV-query of the item, the limitation will also be applied to the selected values. Usually this is not the desired behavior.
This behavior is avoided when the restricting of the left shuttle pane using JavaScript. I have created a Dynamic Action plug-in to do this. The choices in the left shuttle pane are evaluated case insensitive against the content of the search item. If the search item contains more than one string ( separated by spaces) all the strings should occur in the shuttle value. The search string 'INVOICE 2013' returns only values that contain 'INVOICE' (independent of case) and '2013'.



The plugin attributes are the shuttle item and the select item. The plugin needs to know the select item to disable submit when enter is pressed.
Furthermore the select all button is disabled once a selection is applied because this button copies everything from left to right irrespective of visibility. Once no selection is applied the button is activated again.

You can see the plug-in in action on:

http://www.speech2form.com/ords/f?p=OPFG:RESTRICT_SHUTTLE

As usually you can find the plugin on apex.world

Update 22-04-2017: A new version has been released with support for Safari and IE. Also the triggering item is not anymore restricted to the search item. 

 Update 22-06-2017: In a new blogpost I describe a way to have direct filtering after each keystroke: https://dickdral.blogspot.nl/2017/07/restricting-shuttle-choices-even-faster.html

 Update 22-07-2017: A new version 1.2 has been released with a bug fix.This bug prevented the plug-in form functioning. 

Happy apexing
at

8 comments:

  1. Anonymous18 July 2017 at 11:04

    Hello
    I've tried your plug in but so far I have not managed to get it to work. I've created a static content page with two items, a text item named :P50_ZOEK and a shuttle item named :P50_SHUTTLE.
    I've created a dynamic action on :P50_ZOEK with event 'Change' and event scope 'Static'. For the true action I chose 'Restriced Shuttle Values (Plugin)', search item :P50_ZOEK and affected item :P50_SHUTTLE.
    But when I enter text in the :P50_ZOEK item and press enter nothing happens.
    What am I missing?
    Thank you,
    Jolande

    ReplyDelete
  2. Dick Dral18 July 2017 at 16:55

    Hi Jolande,

    would you be able to reproduce this on apex.oracle com.
    Then I can have a look at.

    Thanks,
    Dick

    ReplyDelete
  3. Dick Dral19 July 2017 at 13:08

    Hi Jolande,

    You could try two more things:
    - I think I may have disabled the Enter key, because sometimes it causes the page to be submitted. So if you could using the Tab key?
    - In a more recent post I described a way to have the shuttle react on individual key strokes so the user gets immediate response. You might try that.

    Thanks,
    Dick

    ReplyDelete
  4. Anonymous19 July 2017 at 17:05

    Hi,

    I am having the same issue. I have set up an example using your key release example. However, nothing happens when I type in the search item. I feel like there is one step I'm missing. Have you posted a complete setup anywhere that I can look at?

    I will try to put up an example on apex.oracle.com

    Thank you

    ReplyDelete
  5. Anonymous19 July 2017 at 17:20

    Hi,

    I have uploaded an example to apex.oracle.com (page 5).

    workspace TESTBRETT
    user: demo
    pw: demo

    Thank you,
    Brett

    ReplyDelete
  6. Dick Dral22 July 2017 at 10:51

    Hi Jolande and Brett,

    I found an error in the JavaScript code of the plugin. This was the cause of the behavior you observed.
    You can find the new version on apex.world.

    Thanks,
    Dick

    PS Brett, I used your application to test the new version, so you can see it working there

    ReplyDelete
  7. Anonymous25 October 2019 at 10:03

    Hi,

    I tried importing you Plugin into my Apex app, but it always fails in line 141 of the sql code

    set verify on feedback on define on

    prompt ...done

    74656D293B0A202020202020202020202020636F6E736F6C652E6C6F67282752; -- Always fails execution in this line

    wwv_flow_api.g_varchar2_table(22) := '657374726963742053687574746C652056616C7565733A2073687574746C655F6974656D3D272C2073687574746C655F6974656D293B0A20202020202020207D0A2020202020202020617065785F72657374726963745F73687574746C655F76616C7565';

    wwv_flow_api.g_varchar2_table(23) := '732E72657374726963745F76616C756573287365617263685F6974656D2C73687574746C655F6974656D293B0A202020207D0A7D3B0A';

    null;

    end;


    I am new to Apex, I don't really get how the exportfiles are processed right now.

    Thanks,

    Timo

    ReplyDelete
  8. Anonymous9 April 2020 at 09:56

    @ Timo,

    You need to go to Export/Import -> Import -> Plug-in. Then select the sql file.

    For others,

    You need to remove the search component from the Cascaded LOV parent item for the Affected Shuttle.
    Also, the dynamic action should be defined on Key Release of the search component.
    Then select Action as "Restrict Shuttle Values"

    Thanks,
    Gurpreet

    ReplyDelete