Oracle APEX Part 14 - Child Master Relationship in Oracle APEX page

🔹 Scenario Overview (Best Practice)

• Master: Department

• Detail: Employees

• UI Type: Interactive Grid (Editable)

• Dropdown: Department Select List

• Conditional Visibility: Employee grid shown only when a department is selected

---

🔹 Database Objects (Assumed)

Departments Table

CREATE TABLE departments (
  dept_id   NUMBER PRIMARY KEY,
  dept_name VARCHAR2(100)
);

Employees Table

CREATE TABLE employees (
  emp_id   NUMBER PRIMARY KEY,
  emp_name VARCHAR2(100),
  dept_id  NUMBER REFERENCES departments(dept_id),
  salary   NUMBER
);

---

🔹 Step 1: Create a New Page (Master-Detail Wizard)

1. Open Oracle APEX

2. Navigate to:

   App Builder → Your Application → Create Page
   

3. Select Master Detail

4. Click Next

---

🔹 Step 2: Configure the Master (Departments)

Setting	Value
Master Table	DEPARTMENTS
Primary Key	DEPT_ID
Display Column	DEPT_NAME
Region Type	Interactive Grid
Editable	Yes

Click Next

---

🔹 Step 3: Configure the Detail (Employees)

Setting	Value
Detail Table	EMPLOYEES
Foreign Key	DEPT_ID
Primary Key	EMP_ID
Region Type	Interactive Grid
Editable	Yes

Click Next → Create Page

---

🔹 Step 4: Add Department Select List (Dropdown)

1. In Page Designer

2. Right-click Items → Create Item

3. Set:

Property	Value
Name	P10_DEPT_ID
Type	Select List
Label	Department

List of Values (LOV)

SELECT dept_name d, dept_id r
FROM departments
ORDER BY dept_name

---

🔹 Step 5: Synchronize Master Grid with Dropdown

Create a Dynamic Action

1. Select P10_DEPT_ID

2. Create Dynamic Action

   • Event: Change

3. True Action:

   • Action: Refresh

   • Selection Type: Region

   • Region: Employees Grid

---

🔹 Step 6: Apply Conditional Visibility (Professional UX)

Hide Employees Grid Until Department Selected

1. Select Employees Interactive Grid

2. Set Server-Side Condition

Property	Value
Type	Item is NOT NULL
Item	P10_DEPT_ID

---

🔹 Step 7: Filter Employees by Selected Department

In Employees Interactive Grid → Source → SQL Query:

SELECT emp_id,
       emp_name,
       dept_id,
       salary
FROM employees
WHERE dept_id = :P10_DEPT_ID

---

🔹 Step 8: Final Result (User Experience)

✔ User selects a Department
✔ Employee grid appears dynamically
✔ Only employees of selected department are shown
✔ Fully editable master-detail grid