Auto-Populating Columns in Oracle APEX Interactive Grids Based on LOV .

Have you ever found yourself manually typing data into an Interactive Grid that already exists elsewhere in your database? It’s tedious, prone to error, and — frankly — unnecessary.

In the world of Oracle APEX, the Popup LOV is a powerhouse. One of its best “hidden” features is the ability to fetch multiple pieces of data simultaneously. Today, we’ll look at the most efficient way to fill extra columns in a grid using the Additional Outputs attribute.

The Use Case: The Employee Picker

Imagine you are building a “Projects” grid. When a user selects an Employee ID, you want the grid to automatically pull in that employee’s Job Title, Department, and Email.

Instead of writing complex JavaScript or multiple Dynamic Actions, we can do this declaratively.

Step 1: Prepare the Shared Component LOV

Your List of Values (LOV) needs to “carry” the extra data.

1. Go to Shared Components > List of Values.
2. Create a new LOV (e.g., EMPLOYEE_PICKER) based on this SQL:
3. SQL

SELECT      employee_id,  
    first_name || ' ' || last_name AS employee_name,
     job_id,     email,     department_id 
FROM employees

1. Crucial Step: In the LOV settings, ensure JOB_ID, EMAIL, and DEPARTMENT_ID are listed in the Columns section. You don't have to show them to the user, but they must be part of the LOV data source.

Step 2: Configure the Interactive Grid

Now, let’s head over to your Page Designer and the Interactive Grid where the magic happens.

The Trigger Column

Select the column that will hold the EMPLOYEE_ID.

• Type: Popup LOV.
• List of Values: EMPLOYEE_PICKER.
• Settings > Additional Outputs: This is where the mapping happens. Use the syntax LOV_COLUMN:GRID_COLUMN.
• Enter this:
• JOB_ID:JOB_ID, EMAIL:EMAIL, DEPARTMENT_ID:DEPT_ID

The Target Columns

For the mapping to work, your grid must have columns with Static IDs that match your mapping.

1. Select the JOB_ID column in your grid.
2. In the property editor, find Advanced > Static ID and set it to JOB_ID.
3. Repeat this for EMAIL and DEPT_ID.

Step 3: Handling Read-Only Fields

A common “gotcha” in APEX is trying to auto-fill a column that is set to the Display Only type. APEX security often prevents the browser from “pushing” values into display-only fields.

The Fix:

• Set the target columns (Job, Email, etc.) to Text Field.
• Under Appearance, add is-readonly to the CSS Classes.
• Under Settings, set Read Only to Always.

This makes the fields look and act like display-only labels while still allowing the Popup LOV to update them.

Bonus: Keeping Your Data Consistent

When auto-filling columns like Email or Job Title that already exist in a master table (like EMPLOYEES), you face a design choice: do you want to save a copy of that data in your project table, or just show it?

If you want to show the information for reference without creating “data redundancy” (duplicate data), follow these two critical steps:

1. The “Inner Select” Trick

In your Interactive Grid’s main SQL source, you need to bring these columns in so the grid has a place to put them. Use a subquery (inner select) to fetch the current values based on the foreign key.

SQL

SELECT 
    p.project_id,
    p.employee_id,
    -- Use inner selects to fetch reference data
    (SELECT e.email FROM employees e WHERE e.employee_id = p.employee_id) AS email,
    (SELECT e.job_id FROM employees e WHERE e.employee_id = p.employee_id) AS job_id,
    p.task_name
FROM projects p

2. Set Columns to “Query Only”

Since the Email and Job ID belong to the EMPLOYEES table and not your PROJECTS table, you must tell APEX not to try and "save" them when a user hits the Save button.

• Select the target columns (EMAIL, JOB_ID) in the Page Designer.
• In the Source property group on the right, find Query Only.
• Switch it to On.

Why do this? If you don’t set them to “Query Only,” APEX will try to insert those values into your PROJECTS table. If those columns don't exist there, your save operation will crash with an "Invalid Column" error!