Efficiently Handling Interactive Grid to Collection: Filtered, Unfiltered and Updated Records

This article explores a solution for managing all records in an Interactive Grid, whether filtered or not, and inserting/updating them into APEX collections for subsequent processing. In contrast to a previous blog that focused on handling only selected rows, this article extends the approach to include all record states, providing a more comprehensive data management solution.

Use Cases

  1. Processing All Records After Filtering
    • Scenario: Users apply filters to the Interactive Grid and want to process all records that match the filter criteria.
    • Solution: The provided JavaScript function captures all records currently visible in the grid (after applying filters) and sends them to a server-side process. This ensures that any data visible in the grid, regardless of whether it’s filtered or not, is included in the processing.
  2. Bulk Data Processing
    • Scenario: Users need to push all records from the grid to another system or table, regardless of any applied filters.
    • Solution: The JavaScript function collects all records from the grid and formats them into JSON. This JSON is then sent to a PL/SQL process, which handles inserting the records into an APEX collection. This approach is useful for bulk operations where all records need to be processed or transferred.
  3. Data Export
    • Scenario: Users want to export all records from the Interactive Grid to an external system or file format for reporting or integration purposes.
    • Solution: By sending all records as JSON to a server-side process, you can facilitate data export in various formats. This data can then be converted or used for external reporting or integration needs.
  4. Data Synchronization
    • Scenario: The application requires synchronization of all visible data with a central database or external system.
    • Solution: The JavaScript function gathers all records from the grid and sends them to the server. The server-side PL/SQL process can then handle synchronization tasks, ensuring that all visible data is up-to-date across systems.

JavaScript Function: Collecting and Sending All Records

The following JavaScript function collects all records from the Interactive Grid, regardless of any filters applied, and sends them to a server-side PL/SQL process.

function selectRecords() {
    let str = '{ "data" : [',
        // Retrieve the grid and model from the interactive grid region
        grid = apex.region("ig_emp_id").widget().interactiveGrid("getViews", "grid"),
        model = grid.model;

    // Iterate through each selected record
    model.forEach(function(records, index, id) {
        var vEmpID       = model.getValue(records, "EMPNO"),       // Updated field name to EMPID
            vEName       = model.getValue(records, "ENAME"),
            vDeptNo      = model.getValue(records, "DEPTNO"),
            vHireDate    = model.getValue(records, "HIREDATE"),
            meta = model.getRecordMetadata(id);

        // Construct the JSON string for the selected records
        
        //if (meta.updated || meta.deleted) {
        str = str + '{ "EMPNO" :"'+vEmpID+'","ENAME":"'+vEName+'","DEPTNO":"'+vDeptNo+'","HIREDATE":"'+vHireDate+'" },';
   // }
    });

    // Finalize the JSON string
    str = str + ']}';
    str = str.replace(',]', ']'); // Remove trailing comma

    console.log("str " + str);

    // Send the JSON data to the server process
    apex.server.process(
        'SELECT_RECORDS', {
            x01: str
        }, {
            dataType: 'JSON',
            success: function(data) {
                let vErrorCode = data.return_status,
                    vErrorMessage = data.return_message;

                if (vErrorCode == "S") {
                    // Handle error case
                    console.log(data.return_message);
                   apex.message.showPageSuccess(data.return_message);
                } else {
                    // Handle success case
                    alert("ERROR");
                    // Uncomment the following line if you want to submit a page or process
                    // apex.submit("SUBMIT");
                }
            },
        }
    );
}

PL/SQL Process: Handling and Inserting Records

The PL/SQL block processes the JSON data sent from the JavaScript function, inserting records into an APEX collection.

DECLARE
    l_emp_coll  apex_collections.collection_name%TYPE := 'EMP_COLL'; -- Collection name to match JavaScript
BEGIN
    -- Create or truncate the APEX collection
    APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(p_collection_name => l_emp_coll  );
    
    -- Iterate over the JSON data and add members to the collection
    FOR rec IN (
        SELECT empid,  -- Primary Key
               ename,
               deptno,
               hiredate
        FROM JSON_TABLE(
            apex_application.g_x01, -- JSON data received from JavaScript
            '$.data[*]' -- Path to the array of records in the JSON
            COLUMNS (
                empid     NUMBER PATH '$.EMPNO',   -- Column names to match JSON structure
                ename     VARCHAR2(250) PATH '$.ENAME',
                deptno    NUMBER PATH '$.DEPTNO',
                hiredate  VARCHAR2(250) PATH '$.HIREDATE'
            )
        )
    )
    LOOP
    
        -- Add each record to the APEX collection
        APEX_COLLECTION.ADD_MEMBER(
            p_collection_name => l_emp_coll  ,
            p_n001 => rec.empid,   -- Use primary key as the unique identifier
            p_c001 => rec.ename,
            p_c002 => rec.deptno,
            p_c003 => rec.hiredate
        );
    END LOOP;

    -- Return a JSON response indicating success
    apex_json.open_object;
    apex_json.write('return_status', 'S'); -- 'S' for Success
    apex_json.write('return_message', 'Records successfully processed.');
    apex_json.close_object;
EXCEPTION
    WHEN OTHERS THEN
        -- Handle exceptions and return error details
        apex_json.open_object;
        apex_json.write('return_status', 'E'); -- 'E' for Error
        apex_json.write('return_message', 'Error occurred: ' || SQLERRM);
        apex_json.close_object;
END;

Triggering the JavaScript Function

To execute the selectRecords() function, create a button in your APEX application and configure it to run the JavaScript code:

  1. Add a Button: Place a new button on the page containing the Interactive Grid.
  2. Configure Dynamic Action: Set the button’s action to execute JavaScript code.
  3. Add JavaScript Code: In the button’s properties, enter the following code to call the selectRecords() function:
selectRecords();

or pass as url
javascript:selectRecords();

By following these steps, you ensure that the button triggers the JavaScript function, collects all records from the grid, and sends them to the server for processing.

Conclusion

This approach allows Oracle APEX developers to efficiently manage and process all records in an Interactive Grid. Whether handling filtered data, performing bulk operations, or integrating with external systems, this method provides a robust solution for comprehensive data processing and management in APEX applications.