Skip to content

Oracle Apex: Master-Detail Form Example on EMP and DEPT Tables

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:

Create a report with form.

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.

Create regions.

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.

Specify navigation menu entry.

Step 4: Specify the Table name

Select the table name DEPT from the table LOV, then click on the Next button.

Specify table name.

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.

Define the primary key for the table.

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:

Dept table report page.

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:

Dept table dialog page.

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:

Create a detail region for the EMP table.

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:

Make the IG editable.

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:

Remove the save button from IG.

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.

Set PK for detail table.

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:

Set default value for DEPTNO column.

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:

Set static ID for IG.

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:

DA for before page submit event.

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.

Oracle Apex process tab.

Now you are all set.

Save the changes and run the main department report page.

You will have output as shown below:

Master-detail form in Oracle Apex.

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:

Server-side condition.

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:

Oracle Apex: branch process.