In this Oracle Apex tutorial, I will teach you how to add an Interactive Grid into a Form manually to make it behave like master-detail form.
I have an existing form on the Department table which is having two fields, DEPTNO and DNAME. The requirement is to add a detail section below the form to show all employees under the current department, and the user can add/edit employees under that department.
So, in this case, we should add an Interactive Grid into the form. Follow these steps:
Add an Interactive Grid into a Form Manually in Oracle Apex
- Open the form in Oracle Apex designer in which you want to add an Interactive grid. To demonstrate this, I am using the Department form, which is being called by the Department Report.
- Then right-click on the Content Body and click on the Create Region and set the following properties highlighted in the image below:
- I added the following query into the above SQL Query section:
Select ROWID, empno as employee_no, ename as employee_name, hiredate, deptno as dept, sal as salary from emp where deptno = :p5_deptno
You can see above that I added the P5_DEPTNO in the Page Items to Submit because I am using this item in the query. So you should add all the items you are using in the query to Page Items to Submit.
- It is better to use
ROWIDas Primary Key instead of actual Primary Keys for the Forms and Reports in Oracle Apex because it is faster to fetch and update records using theROWIDfor faster performance, also helps to assign default values to the columns we are linking to the master table. In this example, we are linkingDEPTNOfrom theEMPtable to theDEPTtable. So now click on theDEPTfield in the Employees region and set the following properties:
The above properties I have set because when the user clicks on the Add Row button in the Interactive GRID, it will take the default DEPTNO from the Master table DEPT.
- Now click on the Attribute section of this Interactive Grid and set the properties shown below:
Now when you open the Department form from the Department Report for a specific department number, you will have the following output:
how to check before submit if IG has no record?
Create a dynamic action on before submit event to execute JavaScript code. Then follow these steps:
Specify a static ID to your IG, for example: igemp.
Create a hidden page item, for example, px_youritem.
Then add the following JavaScript code into the DA you created to set the page item value to 1 if records exists and to 0 if not exists.
var model = apex.region("igemp").widget().interactiveGrid("getViews", "grid").model; var n_count; col_empno = model.getFieldKey("EMPNO"); model.forEach(function(igrow) { if (igrow[col_empno].isEmpty()) { n_count = 0; } else { n_count = 1; } break; }); $s('Px_YOURITEM', n_count);Now in a process, you will be able to read the Px_YOURITEM item to know whether your IG is having the records or not.
i am trying, but it doesn't work
var model = apex.region("IGITEMDATA").widget().interactiveGrid("getViews", "grid").model;
var n_count;
col_IG_ITEM = model.getFieldKey("ITEM");
model.forEach(function(igrow) {
if (igrow[col_IG_ITEM].isEmpty()) {
n_count = 0;
} else {
n_count = 1;
}
break;
});
$s('Px_IG_ITEM', n_count);
Create a process to check if px_ig_item is equals to 1 or 0, then take the action accordingly.
can you explain about it? where to put PX_IG_ITEM = 0?
Please check the following tutorial:
Creating a PL/SQL process in Oracle Apex
may i request a demo on this case?
Hi Vinish,
Do you have an example on how to create a Form with Next/Preview button to move through the records.
Thank you,
Varduhi
Hi,
Can we save the IG data on click of Apply Changes and remove save button in IG?
Also I just need separate Save for IG but also want the toolbar. How can I get this implemented.