In this tutorial, you will learn how to create a master detail form in Oracle APEX based on the 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 the wizard
In Oracle Apex, click on the button Create Page then choose Master Detail then choose Stacked option, and then click on the Next button.
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 the Primary Key for the 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:
When you click on the Pencil icon at the left, the modal dialog form will open to display the department details.
Our work is not yet finished.
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 the below steps:
Step 6: Create a Region for Detail Table
Right-click on the Dept region node and select the option Create Region from the shortcut menu.
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 on the right side.
Step 8: Remove the Interactive Grid Save Button
We will use a single save button to save both master and detail information.
One save button has 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 the 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 the Detail Table
For the detail table employee set the P19_DEPTNO as a default value for the interactive grid column DEPTNO. So that whenever a new record is created in the EMP table, the current DEPTNO can be saved.
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.
Step 12: Create a Dynamic Action for Before Submit
Create a dynamic action for the 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.
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.
Now, if you open the ACCOUNTING department, it will show all the employees under the ACCOUNTING department.
You can make the changes in both the master-detail section and with the click of the Apply Changes button, it will save all the changes and will close the dialog.
But if you want the window should not be closed until the user presses the Cancel or Close button from the title bar, follow the 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 right-click on the after-processing node, create a branch process, and set the link property to call the same page (19), as shown in the below image:
