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

Question

When dealing with many-to-many relationships, how do you implement circular cascade logic for select-lists?

As an example, I created a simple test app that tracks books and authors.
(This is way simpler than my actual business scenario, and it shows the issue more clearly.)

The home page contains:

• a button that submits the page
• a select-list for books
• a select-list for authors

I'm using a submit button because my real-world business scenario includes a very lengthy report, which takes 20-60 seconds to refresh - and there are a dozen select-lists that the user needs to choose from, BEFORE submitting the page and pulling the report.

Here's a complete script containing all of the test data I'm working with:

CREATE table "BOOK" (
    "ID"         INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL ENABLE,
    "TITLE"      VARCHAR2(100) NOT NULL ENABLE,
    constraint  "BOOK_CK" check ("TITLE"<>''),
    constraint  "BOOK_PK" primary key ("ID"),
    constraint  "BOOK_UK1" unique ("TITLE")
)
/

CREATE table "AUTHOR" (
    "ID"         INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL ENABLE,
    "NAME"       VARCHAR2(100) NOT NULL ENABLE,
    constraint  "AUTHOR_CK" check ("NAME"<>''),
    constraint  "AUTHOR_PK" primary key ("ID"),
    constraint  "AUTHOR_UK1" unique ("NAME")
)
/

INSERT INTO BOOK (TITLE) VALUES ('BOOK01');
INSERT INTO BOOK (TITLE) VALUES ('BOOK02');
INSERT INTO BOOK (TITLE) VALUES ('BOOK03');
INSERT INTO BOOK (TITLE) VALUES ('BOOK04');
INSERT INTO BOOK (TITLE) VALUES ('BOOK05');
INSERT INTO BOOK (TITLE) VALUES ('BOOK06');
INSERT INTO BOOK (TITLE) VALUES ('BOOK07');
INSERT INTO BOOK (TITLE) VALUES ('BOOK08');
INSERT INTO BOOK (TITLE) VALUES ('BOOK09');
INSERT INTO BOOK (TITLE) VALUES ('BOOK10');
INSERT INTO BOOK (TITLE) VALUES ('BOOK11');
INSERT INTO BOOK (TITLE) VALUES ('BOOK12');
INSERT INTO BOOK (TITLE) VALUES ('BOOK13');
INSERT INTO BOOK (TITLE) VALUES ('BOOK14');
INSERT INTO BOOK (TITLE) VALUES ('BOOK15');
INSERT INTO BOOK (TITLE) VALUES ('BOOK16');
INSERT INTO BOOK (TITLE) VALUES ('BOOK17');
INSERT INTO BOOK (TITLE) VALUES ('BOOK18');
INSERT INTO BOOK (TITLE) VALUES ('BOOK19');
INSERT INTO BOOK (TITLE) VALUES ('BOOK20');

INSERT INTO AUTHOR (NAME) VALUES ('AUTHOR01');
INSERT INTO AUTHOR (NAME) VALUES ('AUTHOR02');
INSERT INTO AUTHOR (NAME) VALUES ('AUTHOR03');
INSERT INTO AUTHOR (NAME) VALUES ('AUTHOR04');
INSERT INTO AUTHOR (NAME) VALUES ('AUTHOR05');
INSERT INTO AUTHOR (NAME) VALUES ('AUTHOR06');
INSERT INTO AUTHOR (NAME) VALUES ('AUTHOR07');
INSERT INTO AUTHOR (NAME) VALUES ('AUTHOR08');
INSERT INTO AUTHOR (NAME) VALUES ('AUTHOR09');
INSERT INTO AUTHOR (NAME) VALUES ('AUTHOR10');
INSERT INTO AUTHOR (NAME) VALUES ('AUTHOR11');
INSERT INTO AUTHOR (NAME) VALUES ('AUTHOR12');
INSERT INTO AUTHOR (NAME) VALUES ('AUTHOR13');
INSERT INTO AUTHOR (NAME) VALUES ('AUTHOR14');
INSERT INTO AUTHOR (NAME) VALUES ('AUTHOR15');
INSERT INTO AUTHOR (NAME) VALUES ('AUTHOR16');
INSERT INTO AUTHOR (NAME) VALUES ('AUTHOR17');
INSERT INTO AUTHOR (NAME) VALUES ('AUTHOR18');
INSERT INTO AUTHOR (NAME) VALUES ('AUTHOR19');
INSERT INTO AUTHOR (NAME) VALUES ('AUTHOR20');

CREATE table "BOOK_AUTHOR" (
    "ID"         INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL ENABLE,
    "BOOK_ID"    INTEGER NOT NULL ENABLE,
    "AUTHOR_ID"  INTEGER NOT NULL ENABLE,
    constraint  "BOOK_AUTHOR_PK" primary key ("ID"),
    constraint  "BOOK_AUTHOR_UK1" unique ("BOOK_ID","AUTHOR_ID")
)
/

ALTER TABLE BOOK_AUTHOR ADD FOREIGN KEY (BOOK_ID)
      REFERENCES BOOK (ID) ENABLE
/

ALTER TABLE BOOK_AUTHOR ADD FOREIGN KEY (AUTHOR_ID)
      REFERENCES AUTHOR (ID) ENABLE
/

CREATE OR REPLACE VIEW "VW_BOOK_AUTHOR" AS
SELECT ba.ID,
       ba.BOOK_ID,
       b.TITLE BOOK_TITLE,
       ba.AUTHOR_ID,
       a.NAME AUTHOR_NAME
FROM BOOK_AUTHOR ba
LEFT JOIN BOOK b on b.ID = ba.BOOK_ID
LEFT JOIN AUTHOR a on a.ID = ba.AUTHOR_ID
/

INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK01' AND a.NAME='AUTHOR01';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK02' AND a.NAME='AUTHOR02';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK03' AND a.NAME='AUTHOR03';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK04' AND a.NAME='AUTHOR04';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK05' AND a.NAME='AUTHOR05';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK06' AND a.NAME='AUTHOR06';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK07' AND a.NAME='AUTHOR07';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK08' AND a.NAME='AUTHOR08';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK09' AND a.NAME='AUTHOR09';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK10' AND a.NAME='AUTHOR10';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK11' AND a.NAME='AUTHOR11';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK12' AND a.NAME='AUTHOR12';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK13' AND a.NAME='AUTHOR13';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK14' AND a.NAME='AUTHOR14';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK15' AND a.NAME='AUTHOR15';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK16' AND a.NAME='AUTHOR16';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK17' AND a.NAME='AUTHOR17';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK18' AND a.NAME='AUTHOR18';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK19' AND a.NAME='AUTHOR19';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK20' AND a.NAME='AUTHOR20';

INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK02' AND a.NAME='AUTHOR01';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK02' AND a.NAME='AUTHOR03';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK04' AND a.NAME='AUTHOR03';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK04' AND a.NAME='AUTHOR05';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK06' AND a.NAME='AUTHOR05';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK06' AND a.NAME='AUTHOR07';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK08' AND a.NAME='AUTHOR07';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK08' AND a.NAME='AUTHOR09';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK10' AND a.NAME='AUTHOR09';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK10' AND a.NAME='AUTHOR11';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK12' AND a.NAME='AUTHOR11';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK12' AND a.NAME='AUTHOR13';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK14' AND a.NAME='AUTHOR13';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK14' AND a.NAME='AUTHOR15';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK16' AND a.NAME='AUTHOR15';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK16' AND a.NAME='AUTHOR17';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK18' AND a.NAME='AUTHOR17';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK18' AND a.NAME='AUTHOR19';
INSERT INTO BOOK_AUTHOR (BOOK_ID,AUTHOR_ID) SELECT b.ID, a.ID FROM BOOK b, AUTHOR a WHERE b.TITLE='BOOK20' AND a.NAME='AUTHOR19';

At this point, if you select a book, and then click the button to submit the page, the authors list is narrowed down to only authors of the selected book. (And vice-versa if you select an author.)

For the next step, I need to refresh the select lists WITHOUT submitting and refreshing the entire page.

Let's start by adding cascade settings to just ONE of the select-lists (P1_AUTHOR):

So far so good.
Now the author select-list automatically refreshes, when the user selects a book.

However, we run into problems when we also configure cascade settings for the other select-list:

Now, when we run the page, and try to select a book OR author, the page crashes with a Javascript exception:

The error is Maximum call stack size exceeded, which indicates an infinite loop execution (with recursion).

This seems like it COULD be a bug/oversight in the APEX code, since the technical situation is easily resolvable with correct/robust logic.

That said, how can I work around this?
Is there a different way of configuring the cascade options to make this work?
Or can I write custom dynamic actions and Javascript to manually submit individual items, and manually refresh the select-lists?

Answer 1

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 r
   from book
   where (
     :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 r
   from author
   where (
     :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.