Oracle Apex - Derive pne column in IG based on another

Question

I have 2 columns in IG.

EMP_CODE and EMP_ACCOUNT

IG Query:

SELECT EMP_NAME, EMP_CODE, EMP_ACCOUNT, DEPT FROM EMPLOYEE_DETAILS;

EMP_CODE is display only.

EMP_ACCOUNT is pop-up lov with list of values query currently as :

select emp_account from employee_accounts;

EMP_CODE is to be displayed from employee_codes table.

However the validation to be put here is:

Both employee_account and employee_code tables are joined by common emp_acc_id column.

select a.emp_account, b.emp_code from employee_accounts a , employee_codes b where a.emp_acc_id = b.emp_acc_id;

This is the relation between the 2 columns in 2 tables.

Now coming back to IG, EMP_CODE is display only and needs to be populated when EMP_ACCOUNT VALUE is selected. So if EMP_ACCOUNT is 1002, then it's corresponding EMP_CODE shall be populated.

What changes would be needed in the columsn to populate EMP_CODE on basis of EMP_ACCOUNT?

Answer 1

I'd remove emp_code from employee_details (drop that column); you know which code it is by joining employee_details to employee_accounts and further to employee_codes. Normalize!

Create a function which returns code:

create or replace function f_emp_code 
  (par_emp_account in employee_accounts.emp_account%type)
  return employee_codes.emp_code%type
as
  retval employee_codes.emp_code%type;
begin
  select c.emp_code
    into retval
    from employee_codes c join employee_accounts a on a.emp_acc_id = c.emp_acc_id
    where a.emp_account = par_emp_account;

  return retval;
end;

Interactive grid's query would then be

select d.emp_name, 
       f_emp_code (d.emp_account) emp_code,
       d.emp_account,
       d.dept
from employee_details d

Turn emp_code column's Query only property ON.

That's it, I guess.

---

[EDIT]

If you want to refresh emp_code as soon as you modify emp_account, create a dynamic action on emp_account which fires when it changes. Action should be "Execute server side code":

:emp_code := f_emp_code (:emp_account);