Pre-Filled Interactive Grids

When developing applications in Oracle APEX, you'll often need to present users with a pre-filled interactive grid containing both existing data and new potential entries. This article demonstrates a powerful technique to pre-populate an Interactive Grid with both existing order items and newly selected items for ordering.

The Challenge

Imagine this scenario: A user selects multiple items that they want to add to an order. These selections are stored as a colon-separated list of item IDs (a common pattern in APEX). You want to display both:

1. Any existing ordered items already in the order table

2. The newly selected items that aren't yet in the order table

This requires showing a combination of existing records and potential new records in a single Interactive Grid.

The Solution: RIGHT JOIN with Dynamic Lists

The key to solving this problem is using a RIGHT JOIN between your existing order items table and the dynamically generated list of selected items. Here's how it works:

SELECT 
    order_items_oid, 
    COALESCE(item_oid, column_value) item_oid, 
    quantitie  
FROM 
    order_table
RIGHT JOIN 
    apex_string.split(:P1_SELECTED_IDS,':') ON (column_value = item_oid);

This query:

• Takes your existing order items from the order_times table

• Splits a colon-separated list (e.g., '1:2:3') into individual rows using apex_string.split()

• Uses a RIGHT JOIN to ensure all selected items appear in the results

• Uses COALESCE to display either the existing item_oid or the newly selected item ID

The Primary Key Problem

After implementing the above solution, you'll notice an issue when trying to save changes: any pre-filled rows that didn't exist in the database have NULL values for the primary key (order_items_oid). This causes problems with the Interactive Grid's row handling operations.

The Complete Solution: Adding a Pseudo Primary Key

To solve the primary key problem, we need to generate a unique identifier for each row, including the new pre-filled rows. Here's the enhanced query:

SELECT 
    ROW_NUMBER() OVER () AS pseudo_oid, 
    order_items_oid, 
    COALESCE(item_oid, column_value) item_oid, 
    quantitie  
FROM 
    order_table
RIGHT JOIN 
    apex_string.split(:P1_SELECTED_IDS,':') ON (column_value = item_oid);

This improved version:

1. Creates a pseudo_oid column using ROW_NUMBER() to ensure each row has a unique identifier

2. Preserves the original primary key in the order_items_oid column for database operations

3. Maintains the COALESCE approach to handle both existing and new items

An alternative approach that combines existing IDs with generated ones:

SELECT 
    COALESCE(order_items_oid, ROW_NUMBER() OVER () * -1) pseudo_oid, 
    order_items_oid, 
    COALESCE(item_oid, column_value) item_oid, 
    quantitie  
FROM 
    order_table
RIGHT JOIN 
    apex_string.split(:P1_SELECTED_IDS,':') ON (column_value = item_oid);

This version:

1. Uses the existing primary key when available

2. For new rows, generates a negative number using ROW_NUMBER() * -1 to ensure unique identification

Implementation

When implementing this in your APEX application:

Set your Interactive Grid's primary key to the pseudo_oid column

Conclusion

This technique provides a seamless way to present users with a grid containing both existing data and potential new entries based on their selections. The pseudo primary key approach ensures proper handling of all rows during save operations, solving one of the common challenges when working with dynamic data in Oracle APEX Interactive Grids.

By leveraging the RIGHT JOIN combined with dynamic list generation and pseudo primary keys, you can create intuitive order entry interfaces that enhance the user experience in your APEX applications.