Friday, March 17, 2017

APEX 5.1 Master detail (with Interactive/Classic Detail Report)

You probably know that in APEX 5.1 you can easily make a master-detail report regions declaratively by using an Interactive Grids (IG).

But what if you don't want all those fancy IG stuff (I know, I know...you can hide/disable all of it) but plain old Classic (or Interactive) Report for a detail report. No problem, it's only few clicks away!

Let's take a well known example with the dept and emp tables.

First of all you have to create an Interactive Grid over the dept table (master):














Don't forget to put the DEPTNO into SQL Query. It can be hidden, but you have to put it in.

Next step is to create a hidden item (with Value Protected property set to No) where you will temporary store the DEPTNO of the selected grid row (in my case it's P28_DEPTNO):



Then you can create your detail report (Classic or Interactive) over the emp table with where condition that references your hidden item (don't forget to put it in Page Items to Submit):



Next step is to create a dynamic action with event Selection Change [Interactive Grid] over the dept region:


For first true action use Set Value with the Set Type Javascript Expression and define it as:

this.data.selectedRecords.length != 1 ? '': this.data.model.getValue( this.data.selectedRecords[0], "DEPTNO")
and for an Affected element choose your hidden item:



The second true action should be a refresh of your detail region...and thats all.

Live demo is available here.

You can use similar approach if you want this to work with a multiple selected rows (for example in editable grid) but then you should change Set Value DA and where condition of your emp query to support this.

Enjoy!

Tested on APEX 5.1.0.00.45

11 comments:

  1. hi marko. I liked your article very much. I tried it, but for instance when you have "add row" button in master region, and click it, you will get "Ajax call returned server error ORA-01722: invalid number for", because hidden item is based on number type, and the value populated by the action "add row" is string until you save it. how to solve this issue?

    ReplyDelete
    Replies
    1. Hi,

      I'm not sure how your PK value looks like but you can use a regular expressions. Change JavaScript Expression of the Set Value DA to:
      fSetItem(this)

      and add this JS function to the page Function and Global Variable Declaration property (or somewhere globally):

      function fSetItem(pThis){
      var vReturnId = '';

      if(pThis.data.selectedRecords&&pThis.data.selectedRecords.length == 1){
      var vReturnId = pThis.data.model.getValue(pThis.data.selectedRecords[0], "DEPTNO");
      if (vReturnId.match('^t([0-9]{4})')){
      vReturnId = '';
      }
      }

      return vReturnId;
      }

      In this example function regular expression checks if PK value is like t0000-t9999

      Br,
      Marko

      Delete
  2. Thank you very much Marko. Meanwhile I've solved the problem with this JavaScript Expression:

    this.data.selectedRecords.length != 1 || this.data.model.getValue( this.data.selectedRecords[0], "ID") == 't1000'
    ?
    '' : this.data.model.getValue( this.data.selectedRecords[0], "ID")

    because I've found that ID genereted by the "Add Row" is always t1000.

    ReplyDelete
    Replies
    1. Marko, just to add new improvement based on your logic using regex:

      this.data.selectedRecords.length != 1 || this.data.model.getValue(this.data.selectedRecords[0], "ID").match('^t([0-9]{4})')
      ?
      '' : this.data.model.getValue( this.data.selectedRecords[0], "ID")

      Delete
    2. That's better. Because t1000 is only for the first row added...

      Delete
    3. Somehow when I tested it seemed to me it will always be t1000, but as you said it is changing. Anyway, thank you for your help and nice article!

      Delete
  3. This comment has been removed by the author.

    ReplyDelete
  4. WHERE TO WRITE THIS PL/SQL CODE
    BEGIN
    FOR i IN (SELECT workspace_id
    FROM apex_workspaces
    WHERE workspace = :WORKSPACE_NAME)
    LOOP
    apex_util.set_security_group_id(i.workspace_id);
    END LOOP;
    FOR i IN (SELECT *
    FROM apex_application_translations
    WHERE application_id = :APP_ID
    AND translatable_message = 'APEX.IG.SUMMARY')
    LOOP
    apex_lang.update_message(
    p_id => i.translation_entry_id,
    p_message_text => 'Interactive Grid. Report: %0, View: %1');
    COMMIT;
    EXIT;
    END LOOP;
    END;

    ReplyDelete
  5. Hi Marko. I was able to refresh the table dynamically from my interactive grid but I was not able to uncheck the selected row in the grid(In your case Departments table). Can you help me to resolve this issue.

    ReplyDelete
  6. I tried this solution. It works great for the add row. But after I save the record and the grid is refreshed, if I click on a row to edit I get this error:

    interactiveGrid.min.js?v=19.2.0.00.18:10 Uncaught TypeError:

    Cannot read property 'attr' of null
    at a..._identityChanged (VM669 interactiveGrid.min.js:10)
    at a..._identityChanged (desktop_all.min.js?v=19.2.0.00.18:27)
    at a..._setModelValue (VM669 interactiveGrid.min.js:7)
    at a..._setModelValue (desktop_all.min.js?v=19.2.0.00.18:27)
    at a...setActiveRecordValue (VM669 interactiveGrid.min.js:7)
    at a...setActiveRecordValue (desktop_all.min.js?v=19.2.0.00.18:27)
    at HTMLDivElement. (VM669 interactiveGrid.min.js:9)
    at HTMLDivElement.dispatch (desktop_all.min.js?v=19.2.0.00.18:2)
    at HTMLDivElement.v.handle (desktop_all.min.js?v=19.2.0.00.18:2)
    at Object.trigger (desktop_all.min.js?v=19.2.0.00.18:2)

    ReplyDelete