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:
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
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?
ReplyDeleteHi,
DeleteI'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
Thank you very much Marko. Meanwhile I've solved the problem with this JavaScript Expression:
ReplyDeletethis.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.
Marko, just to add new improvement based on your logic using regex:
Deletethis.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")
That's better. Because t1000 is only for the first row added...
DeleteSomehow 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!
DeleteThis comment has been removed by the author.
ReplyDeleteWHERE TO WRITE THIS PL/SQL CODE
ReplyDeleteBEGIN
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;
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.
ReplyDeleteI 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:
ReplyDeleteinteractiveGrid.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)
THANKS for yout help!
ReplyDelete