Handling a N:M relation in APEX using a Shuttle

A N:M relation

There are situations where you have to handle a N:M relation between tables in APEX.

Normal way would be to have a parent & child Interactive Grid table, showing per parent record all the child records. To add a child record you will have to add a record, and then select the specific child record. You will have to do this one by one. Then do the same to the next parent records.
It is also harder to determine which child records already belong to the parent record.

An other way would be to introduce a shuttle box, to replace the whole IG child table.
The advantages of a shuttle:
- quick adding / removing of child records to the parent
- overview of which childs records have / have not been added to the parent

In this blog I will show you how to make a page where you can add, remove and save records using a Shuttle.

This will be the end result:

Table structure

For example this table relation structure:
 A party member can have multiple spells, and a spell can belong to mulitple party members.

Regions

"Region A"
Create an Interactive Grid on the mv_party table. Include a Row Selector, but disable "Multi Select".
Set the attribute "Static ID" to IG_PARTY.
Add a hidden item to the same region named: P2350_CURRENT_ID. Make sure that attribute "Value Protected" is disabled.

"Regio B"
Create a second "Static Content" region.
Add a region button named "Save" with as action Submit.
Add a shuttle item: P2350_PARTY_MEMBER_SPELLS.

For the attribute "Sql Query" use this:

SELECT name d
     , id r
  FROM mv_spells
order by name

This will display all the spells in the left side of the shuttle, in which the party member doesn't have access yet.

Dynamic Actions

Create the following Dynamic Actions

"On page load"
This DA makes sure it selects the previous selected record again after you save the data.

Page Load:
- Execute Javascript code

var l_current_value = $v(P2350_CURRENT_ID);
var widget      = apex.region('IG_PARTY').widget();
var grid        = widget.interactiveGrid('getViews','grid');  
var model       = grid.model; 
try {
  if ( l_current_value != -1 ) {
    model.forEach(function(r) {
      var record = r;
      var value1 = model.getValue(record,'ID');
      if(value1 == l_current_value) {
        apex.region("IG_PARTY").widget().interactiveGrid("getViews", "grid").setSelectedRecords([record], true);
    }  
  })
  }    
} catch (err )  {
    null;
}

"On Selection Change"
This DA sets the current ID of the party member in de item: P2350_CURRENT_ID
It will display the records in the right side of the shuttle to which the party member has access to.

Event: Selection Change [Interactive Grid]
Selection Type: Region
Region: "Region A"
- Execute Javascript code

var i_id;
var gridID = "IG_PARTY";
var ig$ = apex.region(gridID).widget();
var grid = ig$.interactiveGrid("getViews","grid");
var model   = ig$.interactiveGrid("getViews","grid").model;
var selectedRecords = grid.getSelectedRecords();
try {
  i_id = model.getValue( selectedRecords[0], "ID"); 
} catch(err) {
  i_id = -1;    
}    
apex.item( "P2350_CURRENT_ID" ).setValue (i_id);

- Set Value, SQL statement:

select listagg(psl.SPL_ID, ':') within group ( order by spl.name ) d
  from MV_PARTY_SPELLS psl
     , MV_SPELLS spl
 where psl.PRY_ID = :P2350_CURRENT_ID
  and spl.id = psl.spl_id

  Items to Submit: P2350_CURRENT_ID

"On Row initialisation"
This DA sets the current ID of the party member in de item: P2350_CURRENT_ID.
It will display the records in the right side of the shuttle to which the party member has access to.

Event: Row Initialisation [Interactive Grid]
Selection Type: Region
Region: "Region A"
- Execute Server-side Code

:P2350_CURRENT_ID := :ID;

  Items to Submit: ID
  Items to Return: P2350_CURRENT_ID

- Set Value, SQL statement

select listagg(psl.SPL_ID, ':') within group ( order by spl.name ) d
  from MV_PARTY_SPELLS psl
     , MV_SPELLS spl
 where psl.PRY_ID = :P2350_CURRENT_ID
  and spl.id = psl.spl_id

  Items to Submit: P2350_CURRENT_ID

Process

Now all we need is to save changes to the Shuttle box. Create an Execute Code process on clicking of the save button.

DECLARE
  l_current_id NUMBER     := to_number ( :P2350_CURRENT_ID );
  l_spells varchar2(4000) := ':'||:P2350_PARTY_MEMBER_SPELLS||':';
BEGIN  
    -- adding spells to the list
    insert into mv_party_spells ( pry_id, spl_id )
       ( select l_current_id, spl.id
           from mv_spells spl
          where l_spells like '%:' || spl.id || ':%'
          minus
         select l_current_id, psl.spl_id
           from mv_party_spells psl
          where psl.pry_id = l_current_id
       );         

   -- now delete spells not belonging to the current party member
   delete from mv_party_spells psl
   where psl.pry_id = l_current_id
     and l_spells not like '%:' || psl.spl_id || ':%';                           
     --
END;

And at last add a branch process to get back to the current page.