Oracle APEX - Interactive Grid Set-Up

Question

I am using Oracle APEX 18.2.0.00.12 on Google Chrome and I have a question regarding the best/preferred way to set up an Interactive Grid using the following requirements:

Note: To simplify the question, I will use fake table names and table column names to represent the problem.

I have an EMP table that looks something like the following:

First_Name Last_Name Dept_Code Dept_Name

John                Smith

Jane                 Doe

In this table, there are several rows of data, but there is only data in the First_Name and Last_Name columns. There is no data in the Dept_Code and Dept_Name columns.

I also have a DEPT table that looks something like the following:

Dept_Code Dept_Name

1234 Finance

5678 Logistics

In this table, there are several rows of data, and the data exists in both columns.

I want to set up an Interactive Grid based on the EMP table that contains all of the columns in that table, but the Dept_Name column would be Display Only. So, the query for the IG would be something like

SELECT *  
FROM EMP;

The problem: I want the user to be able to enter the department code of the employee in the Dept_Code column of the IG. When the user clicks save, I want the Department Code to be saved in the Dept_Code column of the EMP table for that row and the Dept_Name column of the IG for that row to auto-populate with the corresponding Department Name from the DEPT table. Then I want that Department Name to be saved in the Dept_Name column of that row of the EMP table.

For example, the IG would look like:

First_Name Last_Name Dept_Code Dept_Name

John                Smith

Jane                 Doe

If the user inputs 5678 as the Dept_Code for John Smith in the IG and then clicks save, I want 5678 to be saved in the Dept_Code column for that that row in the EMP table, then I want the Dept_Name column of that row in the IG to auto-populate with Logistics (this data is pulled from the DEPT table) and also for the Dept_Name column of that row in the EMP table to contain Logistics.

So the final result would be:

EMP table:

First_Name Last_Name Dept_Code Dept_Name

John                Smith              5678              Logistics

Jane                 Doe

IG:

First_Name Last_Name Dept_Code Dept_Name

John                Smith              5678              Logistics

Jane                 Doe

Does anyone know the best way to set this up?

Thank you.

Littlefoot Littlefoot · Accepted Answer · 2019-07-25T21:04:56

One option is to create a function which will return department name (code would be the parameter), e.g.

create or replace function f_dept_name (par_dept_code in dept.dept_code%type)
  return dept.dept_name%type
is
  retval dept.dept_name%type;
begin
  select dept_name
    into retval
    from dept
    where dept_code = par_dept_code;

  return retval;
end;

Then create an interactive grid using such a query:

select first_name, 
       last_name,
       dept_code, 
       f_dept_name(dept_code) dept_name
from emp

Go to columns' properties and - for dept_name (which is returned by a function) - set query only property to yes (you'll find it within the "Source" group of properties).

That should do it.

Oracle APEX - Interactive Grid Set-Up

1 Answers