Fri. Apr 23rd, 2021

Requirement: Let us assume we have two tables in the Database: DEPARTMENTS table, and the EMPLOYEES table.

Here, DEPARTMENTS is the Master table, and EMPLOYEES is the Child or Details table.

Now, I have an Oracle APEX page in which I want to have two Interactive Grids: one Interactive Grid is for DEPARTMENTS data, and the other Interactive Grid is EMPLOYEES data.

The moment we select any Department in the DEPARTMENTS Interactive Grid it should show us all the Employees that belongs to that selected Department in the DEPARTMENTS Interactive Grid.

Sample output screen is shown below:

Environment Setup: First of all let us create two tables: DEPARTMENTS table and EMPLOYEES table by using the below SQL Statement.

SQL Statement to Create DEPARTMENTS table:

CREATE TABLE departments (
    department_id    NUMBER,
    department_name  VARCHAR2(20) NOT NULL,
    location_name    VARCHAR2(40),
    CONSTRAINT pk_departments PRIMARY KEY ( department_id )
);

SQL Statement to Create EMPLOYEES table:

CREATE TABLE employees (
    employee_id    NUMBER,
    first_name     VARCHAR2(20) NOT NULL,
    last_name      VARCHAR2(20),
    email          VARCHAR2(30) NOT NULL,
    department_id  NUMBER,
    CONSTRAINT pk_employees PRIMARY KEY ( employee_id ),
    CONSTRAINT fk_dept_emp FOREIGN KEY ( department_id )
        REFERENCES departments ( department_id )
);

Add the below data in the DEPARTMENTS table:

Add the below data in the EMPLOYEES table:

Solution: For solution of the above requirement follow the steps as shown below:

Step 1: Create an Oracle APEX application with the name as “Sample Application“. Create a blank page with the name as “Sample Page

Step 2: Create a Static Region and set the Title as Master Details Example

Step 3: Create a Sub Region and set the below properties

  • Title: Departments
  • Type: Interactive Grid
  • Table Name: DEPARTMENTS
  • Order By: DEPARTMENT_ID
  • Parent Region: Master Details Example

Now, go to the Attributes tab and Turn On Edit Enabled field value

Expand Departments > Expand Columns > Click DEPARTMENT_ID > Turn On the Primary Key field.

Step 4: Create another Sub Region and set the below properties

  • Title: Employees
  • Type: Interactive Grid
  • Table Name: EMPLOYEES
  • Order By: EMPLOYEE_ID
  • Parent Region: Master Details Example

Also, set the Master Region property as Departments

Now, go to the Attributes tab and Turn On Edit Enabled field value

Expand Employees > Expand Columns > Click EMPLOYEE_ID >Turn On the Primary Key field.

Click on DEPARTMENT_ID column in the Employees Sub Region > Set Master Column property as DEPARTMENT_ID

Step 5: Save and run the application. Thus, the ran application is as below.

Click on the second row of the Departments Interactive Grid with Department Id as 2, and we can see only those Employees whose Department Id is 2 in the Employees Interactive Grid as shown below.

Hence, the solution to our requirement.

If you like the post please comment, share, and do join me on Facebook. Please subscribe to my YouTube Channel for video tutorials.

Thanks & Regards,
Susanto Paul

 521 total views,  2 views today

By Susanto Paul

Susanto is an Oracle ACE, a qualified MCA, MBA, and a highly-skilled Senior Oracle Specialist. He is an enthusiastic Blogger and YouTuber who helps learners to solve their complex problems more efficiently. He has 9+ years of experience in multiple technologies like AWS, Oracle ADF, Oracle APEX, Oracle JET, Oracle VBCS, Oracle IDCS, Oracle PL/SQL, Oracle Integration Cloud, Java, JDBC, Servlets, JSP, Spring, Hibernate, HTML5, CSS3, JavaScript, TypeScript, NodesJS, Angular, MySQL, Oracle WebLogic Server, JUnit, JMeter, Selenium Web Driver, etc. He is a certified: Oracle Certified Professional Java SE 6 Programmer, Oracle ADF 11g Certified Implementation Specialist, Oracle Cloud Platform Application Integration 2020 Certified Specialist, Oracle Cloud Infrastructure Foundations 2020 Certified Associate, and Oracle Cloud Infrastructure Developer 2020 Certified Associate

Leave a Reply

Your email address will not be published. Required fields are marked *