In this tutorial, you will learn how to create a master-detail form in Oracle Apex based on SCOTT schema's EMP and DEPT tables.
If you don't have the SCOTT schema installed, or do you need only the EMP and DEPT tables, then you can download it from the following link: Download Scott Schema.
Creating Master-Detail form in Oracle Apex
Follow these steps in Oracle Apex to create a master-detail form:
Step 1: Create a form using wizard
In Oracle Apex, click on the button Create Page then choose Form and then choose Report with Form option and then click on the Next button, as shown in the below image:
Step 2: Specify Page Titles and Page Types
In the next step, specify the report page name and the form page name. Also, specify the form page mode to Modal Dialog.
You can also select the Normal, it depends on your need.
Step 3: Specify Navigation Menu Entry
In the third step, it will ask you, whether to show in the navigation menu or not. Select the option as per your need, then click on the Next button.
Step 4: Specify the Table name
Select the table name DEPT from the table LOV, then click on the Next button.
Step 5: Define Primary Key for Master Table
In the fifth step, it will ask you to define the primary key column. Select the option Managed by Database (ROWID), then click on the Next button.
After completing the above 5 steps, you will end-up creating two pages, one is the report page for the DEPT table, from which you can navigate to the second modal dialog form. As shown in the below image:
And when you click on the Pencil icon at the left, the modal dialog form will open to display the department details. Below is the screenshot:
Our work not yet finish.
Now we need to display below the employee grid for the selected department.
To do this, open the above dialog page in page designer and follow below steps:
Step 6: Create a Region for Detail Table
Do the right-click on the Dept region node and select the option Create Region from the shortcut menu. Below is the screenshot:
Then set the following properties for the new region:
- Title: Employees
- Type: Interactive Grid
- Type: Table
- Table Name: EMP
- Where Clause: deptno = :P19_DEPTNO
- Page Items to Submit: P19_DEPTNO
Change the item name P19_DEPTNO according to your page item.
Step 7: Make Interactive Grid Editable
Make the interactive grid for employees editable.
To do this, click on the Attribute node and turn on the Edit > Enabled button at the right side, as shown in the below image:
Step 8: Remove the Interactive Grid Save Button
We will use single save button to save both master and detail information.
And one save button have been already created for the DEPT form.
So remove the Save button from the interactive grid.
To do this, in the attributes, scroll down to the Toolbar section and uncheck the Save button option, as shown in the below image:
Step 9: Define Primary Key for the Detail Section
The primary key should be defined for the detail table. So select the EMPNO column and right side turn on the Primary Key button.
Suppose, you have included the ROWID for the detail table, then no need to select the primary key. Because column ROWID is by default set as the primary key.
Step 10: Set the Default DEPTNO for Detail Table
For the detail table employee set the P19_DEPTNO as a default value for the interactive grid column DEPTNO. So that every time when a new record created in the EMP table, the current DEPTNO can be saved. Below is the screenshot:
Step 11: Assign A Static ID to Interactive Grid
Click on the interactive grid region, scroll down the right side to the Advanced section and specify the static id igemp in the field, as shown in the below image:
Step 12: Create a Dynamic Action for Before Submit
Create a dynamic action for event Before Submit to execute the JavaScript code and add the following JavaScript code:
var n_deptno;
n_deptno = $v("P19_DEPTNO");
var model = apex.region("igemp").widget().interactiveGrid("getViews", "grid").model;
model.forEach(function(igrow) {
model.setValue(igrow, 'DEPTNO', n_deptno);
});The above JS code will update the interactive grid column DEPTNO to the current P19_DEPTNO item value for the records. Change the item name according to your page. Below is the screenshot:
Step 13: Set Process order
Now click on the Process tab, and make sure the Close Dialog process is at the bottom, if not, drag it to the bottom last.
Now you are all set.
Save the changes and run the main department report page.
You will have output as shown below:
Now, if you will open the ACCOUNTING department, it will show all the employees under the ACCOUNTING department.
And you can make the changes in both the master-detail section and on the click of the Apply Changes button, it will save all the changes and will close the dialog.
But if you want that the window should not be closed until the user presses the Cancel or close button from the title bar, follow below steps:
Click on the Process tab.
Click on the close dialog process and set the server-side condition for the Delete button only, as shown in the below image:
Now do the right-click on the after processing node, and create a branch process, and set the link property to call the same page (19), as shown in the below image:
