Oracle Apex Interactive Grid Master Multiple selection to Detail

1
votes

I have a master detail Interactive Grid, all is working fine. As soon as I select a row on the master it populates the details interactive grid without a problem.

The thing I'm trying to achieve is that I want to select two or more rows on the master and populate all the data on the details grid.

Is it possible?

1
oracleplsqlgridoracle-apexinteractive

1 Answers

0
votes

I found your question very challenging and it can be useful to other users.

I'm not sure if the proposed solution works under every condition since it is not tested enough. I resolve your problem with APEX_COLLECTIONS, some JS and PL/SQL.

But lets start in the example below I will use the standard HR data set, more precisely the DEPARTMENTS and EMPLOYEEStables.

  • In the Before Header section add one process defined as:

BEGIN
  APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(
              p_collection_name => 'SELECTED_MASTER');
END;
  • Also create one Ajax Callback process, name it SELECTOR:
BEGIN

  APEX_COLLECTION.TRUNCATE_COLLECTION(p_collection_name => 'SELECTED_MASTER');
  FOR i IN 1..apex_application.g_f01.count LOOP
    APEX_COLLECTION.ADD_MEMBER ( p_collection_name => 'SELECTED_MASTER',
                                 p_n001 => apex_application.g_f01(i));
  END LOOP;

  htp.p('Done.');
END;

  • Create the first IG: Load Dept

    • under Source section set the following values:

      • Location : Local Database
      • Type : Table/View
      • Table Owner : Parsing Schema
      • Table Name : Department

    • Columns:

      • APEX$ROW_SELECTOR (!important)
      • APEX$ROW_ACTION
      • DEPTNO
      • DNAME
      • LOC

    • Static ID : loadDept

    • on the IG create a DA:

      • Name : SELECT_ROWS
      • Event : Selection Change [Interactive Grid]
      • Selection Type : Region
      • Region : Load Dept

    • now add one True action which executes the following JS:

var array = []
var i;
for (i = 0; i < this.data.selectedRecords.length; i++) {
  array[i] = this.data.selectedRecords[i][0];
}
apex.server.process( "SELECTOR", {
    f01: array,
},
{
    dataType : 'text',
    success: function( data )  {
        apex.region( "loadEmp" ).refresh();
    },
    error: function( jqXHR, textStatus, errorThrown ) {
        apex.message.clearErrors();
        apex.message.showErrors([
                {
                    type:       "error",
                    location:   "page",
                    message:    errorThrown,
                    unsafe:     false
                }
        ]);
    }
} );
  • The second IG: Load Emp
    • Static ID : loadEmp
    • under Source section set the following values:
      • Location : Local Database
      • Type : SQL Query
      • SQL Query
SELECT EMPNO,
       ENAME,
       JOB,
       MGR,
       HIREDATE,
       SAL,
       COMM,
       DEPTNO,
       CREATED,
       LAST_UPDATED
  FROM EMPLOYEES
WHERE
    APEX_COLLECTION.COLLECTION_MEMBER_COUNT ('SELECTED_MASTER') = 0
    OR
    
    (
        APEX_COLLECTION.COLLECTION_MEMBER_COUNT ('SELECTED_MASTER') > 0
            AND    
        EXISTS (SELECT 'x' FROM apex_collections
                 WHERE collection_name = 'SELECTED_MASTER' AND n001 = DEPTNO)
    )

Although it works the first record in the master table is always selected, and that is the only known bug or feature. :)