APEX: How to implement circular cascade select-lists (many-to-many)

First of all, I just want to say, way to ask a question! :)

As you've found, the "cascading" part is meant to flow from parent to child, not circularly (which can lead to a Stack Overflow! Sorry, couldn't resist).

I'll give you a solution, but I'll admit up front that it's sub-optimal in that each change of a select list will require two Ajax calls rather than just one. I'll raise the issue with the APEX team in hopes that they can address it in the future.

I'll start the steps assuming that folks have run your script and have a blank page with an HTML region. My page was 53, so folks will need to make changes accordingly.

Part 1: The Basics

1. Add a page item to the region. Set Name to P53_BOOK, Type to Select List, and the List of Values Type to SQL Query. Enter the following code in the SQL Query field:

   select title d,  id rfrom bookwhere (  :P53_AUTHOR is null    or id in (      select book_id      from book_author      where author_id = :P53_AUTHOR    ))order by title

2. Add another item to the region. Set Name to P53_AUTHOR, Type to Select List, and the List of Values Type to SQL Query. Enter the following code in the SQL Query field:

   select name d,  id rfrom authorwhere (  :P53_BOOK is null    or id in (      select author_id      from book_author      where book_id = :P53_BOOK    ))order by name

3. In the Rendering pane of the Page Designer, right-click P53_BOOK, select Create Dynamic Action, and then set its Name to P53_BOOK changed. Set the Client-side Condition Type to JavaScript Expression and enter the following code in the JavaScript Expression field:

   this.browserEvent.originalEvent !== undefined

   While it looks odd, this will prevent the Dynamic Action from firing when the other item is refreshed (prevents a different circular logic issue; see this for details).

4. At the action level, change the Action from Show to Execute PL/SQL Code. Set PL/SQL Code to null; and Items to Submit to P53_BOOK. This action is only being used to update session state for P53_BOOK prior to the refresh action you'll create next.

5. Add a new action that fires after the Execute PL/SQL Code action. Set Action to Refresh, Selection Type to Item(s), and Item(s) to P53_AUTHOR.

6. Right-click P53_AUTHOR, select Create Dynamic Action, and then set its Name to P53_AUTHOR changed. Set the Client-side Condition Type to JavaScript Expression and enter the following code in the JavaScript Expression field:

   this.browserEvent.originalEvent !== undefined

7. At the action level, change the Action to Execute PL/SQL Code. Set PL/SQL Code to null; and Items to Submit to P53_AUTHOR.

8. Add a new action that fires after the Execute PL/SQL Code action. Set Action to Refresh, Selection Type to Item(s), and Item(s) to P53_BOOK.

If you run that, it should work as expected (for the most part). The biggest problem is that if you make a change to one item and it refreshes the other item, the selection in the other item will always be lost - even if the pre-refresh value exists in the item after the refresh.

The following steps can be used to automatically reselect the value if it's available.

Part 2: Restore previously selected values (optional)

1. Go to the P53_BOOK changed Dynamic Action. Add a new true action that fires before the previous two actions. Set Action to Execute JavaScript Code and enter the following code in the Code field:

   $('#P53_BOOK').data('last-val', $v('P53_BOOK'));

   That code uses jQuery's data method to store the value of the item pre-refresh. You'll use it later after the refresh.

2. Go to the P53_AUTHOR changed Dynamic Action. Add a new true action that fires before the previous two actions. Set Action to Execute JavaScript Code and enter the following code in the Code field:

   $('#P53_AUTHOR').data('last-val', $v('P53_AUTHOR'));

3. Right-click P53_BOOK and select Create Dynamic Action. Set Name to P53_BOOK refreshed and set Event to After Refresh.

4. At the action level, set Action to Execute JavaScript Code and enter the following in the Code field:

   $s('P53_BOOK', $('#P53_BOOK').data('last-val'), null, true);

   That code sets the value of P53_BOOK using the data method (this time as a getter). The last parameter to $s (true) is passed to suppres the change event, which would otherwise create more circular logic.

5. Right-click P53_AUTHOR and select Create Dynamic Action. Set Name to P53_AUTHOR refreshed and set Event to After Refresh.

6. At the action level, set Action to Execute JavaScript Code and enter the following in the Code field:

   $s('P53_AUTHOR', $('#P53_AUTHOR').data('last-val'), null, true);

That should do it. But there's one more annoying thing left... If you select a null value in either field, you might expect it to refresh both fields (showing all the options for both), but it doesn't work like that. If this is desired, I'd recommend just adding a separate reset button instead. Otherwise, you'd probably be best off writing all of this in raw JavaScript code.