Editing Data in a Grid

Exploring a Simple Employees Editable Grid

Consider the page shown in the figure below with an Interactive Grid based on the familiar EMP table. If editing is enabled, the user can click or navigate using the keyboard into the first row's Name column and edit an employee's name from KING to KINGSTON.

If you configure the grid to let users move through the data by scrolling, as shown below, the headings can stay visible as the user inspects even very large data sets. You configure which columns and rows are editable. Assuming they are authorized, the user can click into any editable row and any editable column to edit required values. When they save the grid or submit the page, all their changes are saved automatically.

You also pick the type of input control for each editable column that best serves end users. Available item types include many of the same kinds you can use in a form page. For example, in the figure below the user is about to pop open the Date Picker mini calendar to change employee CLARK's date of hire.

Understanding the Grid's Core End-User Capabilities

Interactive Grid shares Interactive Report's focus on letting end users control how reports look and behave, but gives them a more direct way to navigate and select rows and cells. When editing is enabled, they can also paste ranges of data directly into the grid. Depending on what features you enable, users access these capabilities through the default toolbar, Actions menu, column headers, keyboard shortcuts, and direct grid interactions. If you allow it, users can:

• Search across rows, optionally constraining search to a selected column.
• Choose, reorder, resize, freeze, and stretch columns.
• Sort by one or more columns and create filters using row-level or column-level conditions.
• Add aggregates, highlights, control breaks, groupings, and charts to summarize or reshape the displayed data.
• Use flashback, when enabled and supported, to view data as it existed at an earlier point in time.
• Save, rename, switch, reset, or delete saved reports, including private or public reports depending on authorization.
• Download or email the report in the formats made available by the developer and current APEX configuration.
• Use Help and keyboard shortcuts to navigate, select, edit, and invoke grid commands.

The figure below shows the Interactive Grid's Actions menu with the features you let users access.

Examining Editable Grid Behavior

When you enable editing, the grid is a multi-row data entry component backed by a native page process that saves inserted, updated, and deleted rows automatically. When needed, you can customize processing to accommodate more advanced scenarios.

Editable grids have navigation mode and edit mode. In navigation mode, the user moves through cells, selects rows or ranges, opens row actions, and invokes toolbar or menu commands. In edit mode, the active cell hosts the appropriate form control. Double click a cell, press [Enter], or click the (Edit) toolbar button to go into edit mode. Press [Esc] to return to navigating. Only one cell editor is active at a time; this avoids rendering an input control for every visible cell, improving performance, keyboard navigation, and accessibility.

You define whether users may add rows, update rows, delete rows, and whether the grid adds an empty row when the query returns no data. You can also use an Allowed Row Operations column to compute permitted actions dynamically, for example allowing updates only while a row is in a draft status. To make a column read-only in certain cases, just configure a condition. It can also depend on a user's authorization.

Like Form regions, editable grids can protect users from concurrency problems. The Lost Update Type setting compares row values by checksum or using a row version column. This ensures one user's edits don't silently overwrite a colleague's recent changes to the same record.

Using Row, Cell, and Selection Actions

The grid exposes both row actions and selection actions. Row actions work on the current row and typically include add, duplicate, delete, refresh, and revert. Selection actions work on selected rows or cell ranges and include duplicate, delete, refresh, revert, copy down, fill, clear, and clipboard operations where allowed.

Interactive Grid supports row selection and cell selection. In row selection mode, users can select one or more rows. In cell selection mode, users can select a rectangular range of cells, including keyboard support for selecting columns, rows, or all cells. Using simple JavaScript, you can opt to maintain selection across page-by-page pagination, and specify a selection state item to store the selected primary keys in a hidden page item for use in SQL, PL/SQL, branching, or other regions. See Understanding and Customizing Grid Behavior for examples.

These selection features are especially useful for business workflows such as approving several rows, recalculating values for selected rows, refreshing a dependent chart, passing selected keys to another page, or running a custom action on a batch of records.

Working with Clipboard and Spreadsheet Paste

Interactive Grid supports full copy, cut, paste, paste insert, and drag-and-drop of tabular data. This lets a user copy a range of cells from a spreadsheet and paste it into an editable grid.

Clipboard operations apply in navigation mode. Copy can place selected rows or cell ranges on the clipboard as plain text and HTML table data, and APEX also uses a custom JSON format when possible to preserve underlying model values for grid-to-gI rid copy and paste. Cut deletes selected rows or clears selected cells after copying, when the affected records and cells allow editing. Paste overwrites values starting at the current cell; Paste Insert uses the Shift-modified shortcut or command to insert new rows as needed.

Pasted values are set as if the user typed them, so dynamic actions and client-side validations can run. Read-only rows, read-only columns, and cells that cannot be edited are skipped. LOV-backed columns require care: the pasted value should match the return value that APEX stores, not merely the display value the user sees. Because APEX uses browser clipboard APIs for these operations, paste behavior can also depend on the browser and deployment context. For example, a browser might require HTTPS, a keyboard shortcut, or explicit user permission before exposing clipboard data to the page.

Navigating Larger Data Sets with Pagination and Scrolling

Users can move through Interactive Grid rows page-by-page like in an Interactive Report, showing a bounded set of rows with navigation controls. The grid also offers scroll pagination that presents a viewport into the data. Both approaches fetch only the necessary rows as the user moves through the data. The grid also supports fixed headers, frozen columns, column resizing, and drag-and-drop column reordering. These features help users work with larger result sets while staying oriented in the data.

The EMP table is small enough to keep examples easy to follow. But to illustrate scroll pagination, the figure below shows an Interactive Grid scrolling the built-in Employees sample data instead. Its 250 employees make scrolling behavior and on-demand data fetching easier to witness. The figure shows the progress indicator that appears when the user tugs the scrollbar down and the grid fetches additional data on demand.

When Show Total Count is off, Interactive Grid loads more rows as users scroll farther down, and the scrollbar can only indicate whether more rows remain. When Show Total Count is on, APEX knows the total row count and can use virtual scrolling, so the scrollbar reflects the size of the result set. This can improve the browsing experience, but requires an additional query to calculate the count.

By default, when the Interactive Grid does scrolling pagination it uses the available vertical space on the page. Use the Heading > Fixed To setting on the Attributes tab to choose how the grid heading behaves:

• Page: heading stays pinned to the top of the page as rows scroll beneath it.
• Region: heading is above a scrolling grid sized by the Fixed Report Height setting.
• None: heading is not fixed, and scrolls normally with the page content.

The figure above shows a grid fixed to the Region with a report height of 300 pixels.

Editing Rows with Form Controls

An editable Interactive Grid row is conceptually like a horizontal form. Each editable column specifies a column type that determines its cell editing experience for users. A column's form control behaves much like a page item, but the value belongs to the column's cell in the current row. This lets users edit many rows in place while still getting familiar APEX item behavior.

Grid columns can use many common item types, including text fields, number fields, date pickers, select lists, popup LOVs, switches, and checkboxes. This makes editable grids useful for more than simple text entry. For example, a row of an Interactive Grid on the familiar EMP table can include a select list for DEPTNO, a popup LOV for MGR, a date picker for HIREDATE, and number fields for amounts or percentages. The figure below shows the user choosing a new manager for employee CLARK by searching the popup LOV for names containing a letter A.

Column form controls can also participate in familiar form behaviors. Cascading lists can depend on other column values in the same row. Dynamic actions can react when a user changes a column value, validate input, calculate another column, enable or disable related fields, or refresh a dependent LOV. These behaviors make the editable row feel less like a plain table and more like a small data-entry form available on-demand for each row.

Using a Template Component in a Grid Column

Template Components let any developer familiar with HTML markup create reusable components other team members can easily use in Page Designer. You'll see a simple example here, but for more information on creating them, see Reusable Components Using HTML Skills.

The Percent Dot on Bar template component displays a percent value as a dot on a bar using Scalable Vector Graphics. SVG is a W3C standard set of tags representing vector drawing instructions. All major modern browsers support it. This makes it a practical choice for lightweight, scalable graphics in web applications.

The template component uses the following fragment of SVG to draw the bar and the dot. Notice the markup references two Template Component custom attributes LABEL and PERCENT. When using this component on a page, developers provide appropriate values for these two attributes in the Property Editor that make sense for the context they use it in.

<svg xmlns="http://www.w3.org/2000/svg"
     viewBox="0 0 100 16" width="100%" height="16">
  <title>#LABEL#</title>
  <!-- Light gray rectangle with rounded ends -->
  <rect x="0" y="6.5"
        width="100" height="3"
        rx="1.5" fill="#e8e8e8"/>
  <!-- Blue rectangle with rounded ends -->
  <rect x="0" y="6.5"
        width="#PERCENT#" height="3"
        rx="1.5" fill="#378ADD"/>
  <!-- White circle with dark blue 2-wide border, radius 6 -->
  <circle cx="#PERCENT#" cy="8" r="6"
          fill="white" stroke="#185FA5" stroke-width="2"/>
</svg>

The viewBox attribute is for the author. It sets up a math-friendly coordinate space. The viewbox="0 0 100 16" specifies a rectangle 100 units wide and 16 units tall. In contrast, width and height are for the browser, controlling how that space maps to the page. The width="100%" asks it to use the full width of the container and height="16" makes the shape 16 pixels tall. The width and height of the rectangles (<rect>) and circle (<circle>) elements are expressed in logical units. The browser handles converting logical units into page units.

The figure below shows how the three SVG shapes get drawn into the viewBox area. The light grey rounded rectangle is 3 units tall and 100 units wide. The y-axis grows down from the origin (0,0) in the upper left corner. So drawing it at logical y coordinate 6.5 makes the 3-unit tall rectangle vertically centered. The blue rounded rectangle is similarly anchored, but only stretches #PERCENT# units across. The dark blue circle of radius 6 has a 2 unit border stroke, which SVG centers on the path of the circle, so the visual circle plus its border has an effective radius of 7 units. This means the circle's center needs to be shifted by 7 units if we don't want it to be clipped when showing percentage values near the left and right edges. So, the effective number range for drawing the circle's center needs to go from 7 to 93, which is a range of 86 units.

Adding SQL Expression Column to the Grid

To preserve the editability of the grid's data source, to support any requirement you can add additional computed columns that are read-only. This comes in handy to calculate the appropriate PERCENT value to pass to the template component to show where each employee's salary falls in the range of all salaries.

Choose Create Column on context menu of the Interactive Grid's nested Columns node in the component tree. Use the Property Editor to name it PERCENT. Set it's column type to Hidden. Its Source > Type defaults to SQL Expression. Lastly, set its Data Type to NUMBER and enter the following into the SQL Expression area:

ROUND(
  7 + (                      /* start 7 units from left edge  */
    (SAL - MIN(SAL) over ()) /* distance from the minimum     */
    /
    NULLIF(                  /* divided by total range        */
      MAX(SAL) over ()       /* guarding against              */
    - MIN(SAL) over (), 0)   /* divide-by-zero                */
    *
    86                       /* scale to 86-unit drawing area */
  )
)

This SQL formula converts a salary (SAL) into a position on a scale from 7 to 93. It works in three steps:

1. Find where this salary sits relative to the range: subtract the lowest salary from the current salary, then divide by the difference between the highest and lowest. This gives a number between 0 and 1, where 0 means "at the bottom" and 1 means "at the top".
2. Stretch that onto an 86-unit scale: multiply by 86, the width of the drawable area inside the graphic.
3. Shift it 7 units in from the left:add 7 to keep the circle indicator from being cut off at the edges.

MIN(SAL) over () and MAX(SAL) over () are SQL window functions. The over () part with empty parentheses means "look across all rows in the result set". So MIN(SAL) over () gives the single lowest salary across all employees, repeated on every row. Without the over () you would need a GROUP BY, which would collapse the rows and lose the per-employee detail.

The NULLIF(..., 0) function prevents a divide-by-zero error in the rare case where all values in the range are identical. The ROUND() removes any decimal places since SVG coordinates work best as whole numbers.

Displaying a Template Component in a Grid Column

Now add a column to represent the salary range indicator. Repeat the Create Column step above. Since this column doesn't need a SQL Expression and doesn't come from a Database Column, set its Source > Type to None. Configure the column type to be an HTML Expression, and enter the four lines below into the HTML Expression area. This uses the {with/}…{apply/} template directive to include the PERCENT_DOT_ON_BAR template component, passing the value of the current row's SAL column for the LABEL attribute and the value of its PERCENT column for the component's PERCENT attribute.

{with/}
    LABEL   := &SAL.
    PERCENT := &PERCENT.
{apply PERCENT_DOT_ON_BAR/}

This combination produces the result shown in the figure below, where each employee's salary renders as a percent dot on a bar, proportional to the range of all employees salaries. Hovering the mouse over an individual bar like the one for FORD, the tooltip shows the salary amount you passed as the template component's LABEL attribute. In FORD's case, that is 3000.

Changing a Grid Row's Primary Key is Not Allowed

An Interactive Grid manages an in-memory record set in the browser. This is called the model. An editable grid’s model also tracks rows the end user has added, modified, and deleted. When the user submits the page or clicks the grid toolbar’s (Save) button, the changes in the model are sent to the server where the Interactive Grid – Automatic DML page process saves any rows that were created, updated, or deleted.

The client-side model uniquely identifies rows using the values of the grid's Primary Key column and assumes those values won’t change. For rows the user creates, the model generates a temporary unique key in the browser. Then, when the page process saves a new row, the database-assigned primary key replaces the client-assigned temporary value. That’s the only key-change the grid allows.

Understanding Why Collection Sequence ID of a Member Row Can Change

Each collection member has a unique system-assigned sequence ID (SEQ_ID). It's an integer row index. When a member is added, if it's the first row its SEQ_ID is one (1), otherwise it's one more than the maximum sequence ID in the collection. As collection members are added, removed, moved, or sorted, using the APEX_COLLECTION API, a member's index can change. So, SEQ_ID is not the unchanging primary key the Interactive Grid requires.

Instead, generate a unique key value with SYS_GUID() and store it in the collection in an additional generic VARCHAR2 column. The shopping cart uses the following collection columns, with the ID storing the unique GUID value for each member in C002:

• C001 as ITEM_CODE
• N001 as QUANTITY, and
• D001 as NEED_BY_DATE
• C002 as ID

Simplifying Collection Access with a View and Package

Create a view like following SHOPPING_CART_V to use as your Interactive Grid's data source. This way its column names are self-documenting:

create or replace view shopping_cart_v as
select c001 as item_code,
       c002 as id,
       n001 as quantity,
       d001 as need_by_date
  from apex_collections
 where collection_name = 'SHOPPING_CART'

Then create a PL/SQL package like the one below to encapsulate the use of the APEX_COLLECTION API. This lets your page's grid work with function and procedure names related to the task at hand. The add_item returns the unique GUID id the grid uses as its unchanging primary key. The update_item and remove_item accept this item id value as the unique identifier as well.

create or replace package shopping_cart_api is
    ----------------------------------------------------------
    procedure clear_cart;
    ----------------------------------------------------------
    function add_item(
        p_item_code    in varchar2,
        p_quantity     in number,
        p_need_by_date in date)
        return            varchar2;
    ----------------------------------------------------------
    procedure remove_item (
        p_item_id in varchar2);
    ----------------------------------------------------------
    procedure update_item (
        p_item_id      in varchar2,
        p_item_code    in varchar2,
        p_quantity     in number,
        p_need_by_date in date);
end shopping_cart_api;

The corresponding package body appears below:

create or replace package body shopping_cart_api is
    c_collection_name constant varchar2(40) := 'SHOPPING_CART';
    ----------------------------------------------------------
    procedure ensure_collection
    is
    begin
        if not apex_collection.collection_exists(c_collection_name) then
            apex_collection.create_collection(c_collection_name);
        end if;
    end ensure_collection;
    ----------------------------------------------------------
    procedure clear_cart
    is
    begin
        ensure_collection;
        apex_collection.truncate_collection(c_collection_name);
    end clear_cart;
    ----------------------------------------------------------
    function get_seq_id(
        p_item_id in varchar2)
    return number
    is
        l_ret number;
    begin
        for j in (select seq_id as seq
                    from apex_collections
                   where collection_name = c_collection_name
                     and c002 = p_item_id)
        loop
            l_ret := j.seq;
        end loop;
        return l_ret;
    end;
    ----------------------------------------------------------
    function add_item (
        p_item_code    in varchar2,
        p_quantity     in number,
        p_need_by_date in date)
    return varchar2
    is
        l_ret varchar2(255) := sys_guid();
    begin
        ensure_collection;
        apex_collection.add_member (
            p_collection_name    => c_collection_name,
            p_c001               => p_item_code,
            p_c002               => l_ret,
            p_n001               => p_quantity,
            p_d001               => p_need_by_date
        );
        return l_ret;
    end add_item;
    ----------------------------------------------------------
    procedure remove_item (
        p_item_id in varchar2)
    is
        l_seq_id number;
    begin
        ensure_collection;
        l_seq_id := get_seq_id(p_item_id);
        if l_seq_id is not null then
            apex_collection.delete_member (
                p_collection_name => c_collection_name,
                p_seq             => l_seq_id);
        end if;
    end remove_item;
    ----------------------------------------------------------
    procedure update_item (
        p_item_id      in varchar2,
        p_item_code    in varchar2,
        p_quantity     in number,
        p_need_by_date in date)
    is
        l_seq_id number;
    begin
        ensure_collection;
        l_seq_id := get_seq_id(p_item_id);
        if l_seq_id is not null then
            apex_collection.update_member(
                p_collection_name => c_collection_name,
                p_seq             => l_seq_id,
                p_c001            => p_item_code,
                p_c002            => p_item_id,
                p_n001            => p_quantity,
                p_d001            => p_need_by_date);
        end if;
    end update_item;
end shopping_cart_api;

Configuring Interactive Grid to Save Using an API

With the view and package in place, set the Interactive Grid to use the view as its data source and configure its ID column as Primary Key. Page Designer adds the related page process to handle saving its rows when you enable editing on the Attributes tab in the Property Editor. You can rename the page process to Save Shopping Cart. The figure shows it selected in the processing tree. Its type is Interactive Grid - Automatic Row Processing (DML), and its Editable Region property is set to the name of the Interactive Grid whose data it saves. As shown, set its Target Type to PL/SQL Code. The code appears below and uses the SHOPPING_CART_API package. You can also disable row locking and lost update protection as highlighted. Since the collection data is private to the current user session, there’s no chance another user can lock or inadvertently change the same collection row.

The complete code for the PL/SQL Code to Insert/Update/Delete appears below. Since the page process' Editable Region property is set to an Interactive Grid, the code runs once for each row that the user created, updated, or deleted. Notice the CASE statement uses the built-in bind variable :APEX$ROW_STATUS to know if the current row being processed is for create ('C'), update ('U'), or delete ('D'). For the create, it assigns the unique item id GUID returned by the add_item function directly to the ID primary key grid column using the :ID bind variable.

case :APEX$ROW_STATUS
when 'C' /* Create */ then
    :ID := shopping_cart_api.add_item(
        p_item_code    => :ITEM_CODE,
        p_quantity     => to_number('QUANTITY'),
        p_need_by_date => to_date(:NEED_BY_DATE,'DD-MON-YYYY'));
when 'U' /* Update */ then
    shopping_cart_api.update_item(
        p_item_id      => :ID,
        p_item_code    => :ITEM_CODE,
        p_quantity     => to_number('QUANTITY'),
        p_need_by_date => to_date(:NEED_BY_DATE,'DD-MON-YYYY'));
when 'D' /* Delete */ then
    shopping_cart_api.remove_item(
        p_item_id => :ID);
end case;

Wiring Button to Grid Action Declaratively

The Shopping Cart page's Interactive Grid has its toolbar and footer turned off, so the normal (Add Item) toolbar button does not appear. The page saves the grid data using a normal (Save) button that submits the page. It clears the shopping cart with another (Clear) button that submits the page to trigger an Invoke API page process with When Button Pressed = CLEAR that calls shopping_cart_api.clear_cart. It adds its own (Add Item) button that triggers the grid's built-in selection-add-row action in the browser.

To setup this declarative button behavior:

1. Assign the grid an HTML DOM ID like shoppingcart
2. Set the Action of the button to Defined by Dynamic Action, and
3. Set Custom Attributes to have the following text:

data-action="[shoppingcart]selection-add-row" data-no-update="true"

The grid region’s HTML DOM ID shoppingcart appears in square brackets, and the selection-add-row action name follows. The same approach works for any of the built-in actions explained in the Actions section of the Interactive Grid's JavaScript API reference documentation. The data-no-update custom attribute asks the selection-add-row action to not automatically update your declaratively wired button at runtime to use the action's label and icon.

Customizing the No Rows Found Message

By default, a grid region with no rows will display the text No data found. To configure a more user-friendly message specific to what the grid is showing in your app, set its When No Data Found message on the Attributes tab of the Property Editor. For example, if you set it to "No items in your cart", then an empty shopping cart looks like the figure below.

Processing the Shopping Cart Contents

At checkout time, to process the shopping cart rows, your app can use code like the following using the view you create to give your collection members more meaningful names:

for j in (select item_code, quantity, need_by_date
            from shopping_cart_v)
loop
    -- Reference j.item_code, j.quantity, j.need_by_date
end loop;

Understanding Grid Behavior and Save Processing

The Interactive Grid region is a model-view component: a client-side model manages records, metadata, and change state; multiple views present and edit that model; and actions, events, and widget methods let developers invoke commands and respond to user interactions.

Toolbar buttons and menu items are wired to named Interactive Grid actions. When a user triggers one of these actions, the Interactive Grid widget coordinates the appropriate response, such as refreshing data, entering edit mode, adding a row, opening a dialog, or saving changes.

The Interactive Grid can be read-only, or enabled for editing. Each editable one's client-side model tracks inserted, updated, and deleted records. When a grid’s Save action is triggered, it saves its own pending changes without refreshing the page. When the user submits the page, pending grid changes can be processed along with form region items, validations, and other page processes. Each grid's associated Interactive Grid - Automatic Row Processing (DML) page process handles saving its changes. This lets a master form and one or more detail grids be validated and saved in a single transaction. When using page submit to save grid data, you can optionally hide the grid's own (Save) toolbar button by turning off that feature in the Property Editor.

Customizing Grid Behavior with JavaScript

The Oracle APEX JavaScript API Reference covers the grid's rich set of client-side APIs. Developers commonly use them to:

• Configure high-level grid features and default view options.
• Customize the toolbar by adding, removing, or rearranging its controls.
• Configure built-in actions or add custom actions.
• Invoke grid commands such as save, refresh, edit, or open a built-in dialog.
• Respond to grid events such as selection, save, report, view, or mode changes.
• Work with selected records and model data, including refreshing a single row.

Use the Interactive Grid's JavaScript Initialization Function for settings that must be in place before the grid is created. Examples include:

• Setting initial grid options for features, toolbar layout, report settings, selection, editability, pagination, and default view or model behavior.
• Using initActions to add, hide, disable, relabel, or configure actions before the toolbar and menus are wired up.

Use apex.util.getNestedObject when setting initialization options inside objects like defaultGridViewOptions or defaultModelOptions. This lets your code change the values it cares about without accidentally discarding other settings that might already be in place.

After the grid exists, you can use region, grid, actions, model, and event APIs. Examples include:

• Invoking named actions such as save, edit, or show-filter-dialog.
• Getting or setting selected records.
• Reacting to events such as selection change, mode change, save, report change, view change, or view model create.
• Using the current view and model APIs to read or update record values.
• Calling region, grid view, or model methods to refresh data, navigate to a cell, move focus, or refresh a specific row after a dialog closes.

Initialization code shapes the grid before it is created; runtime code works with the grid after it exists. Although initActions runs during initialization, the actions it configures can be triggered later by users or invoked later by JavaScript code.

Studying Some Simple Customization Examples

Suppose you want your grids to be able to:

• Hide the footer when not needed
• Let users select multiple rows in the grid
• Keep selected rows across page-by-page pagination
• Automatically maintain primary keys of selected rows in a hidden page item.

Each requires setting just a single property in the grid’s JavaScript Initialization Function:

• Hide the footer – footer = false
• Enable multiple row selection – multiple = true
• Track selected keys – selectionStateItem = "P2_SELECTED_EMPNOS"
• Keep selected rows across pagination – persistSelection = true

You can add one or more of these settings to a small initialization function: just add the property assignments you need. You define it in the JavaScript Initialization Function area on the Attributes tab of the Interactive Grid region. For example, to enable a selection state item in a grid with multi-row selection, the necessary code appears below. Notice the function receives an options object as a parameter from the grid. It uses the getNestedObject() utility function to access the default grid view settings object, then assigns two properties on it. Finally, it returns the same options object so APEX initializes the grid with the modified settings.

function( options ) {
    const gridOptions = apex.util.getNestedObject(
                            options,"defaultGridViewOptions");
    gridOptions.selectionStateItem = "P2_SELECTED_EMPNOS";
    gridOptions.multiple = true;
    // gridOptions.footer = false;
    // gridOptions.persistSelection = true;
    return options;
}

Surgically Refreshing an Edited Row

The Interactive Grid's model API has getRecord() and fetchRecords() functions. Use getRecord() to find a record by primary key in the client-side model. Call fetchRecords() to refresh one or more specific records with the latest data from the server. You can combine them to write a refreshGridRowOrRegion() helper function to add to a page's Function and Global Variable Declaration. The code appears below. It uses apex.region() to find the region by HTML DOM ID, then validates the region type to ensure it's working with an Interactive Grid. If the caller provides a primaryKey, it calls getRecord() and fetchRecords() to surgically refresh that record in the client-side model. Conversely, if the key is not provided it refreshes the whole grid instead.

function refreshGridRowOrRegion( regionHtmlDomId, primaryKey ) {
    const region = apex.region( regionHtmlDomId );
    // Ensure the static id identifies an Interactive Grid region
    if ( !region || region.type !== "InteractiveGrid" ) {
        throw new Error( "The region must be an Interactive Grid" );
    }
    // If we have a primary key that was updated, refresh just that row
    if ( primaryKey ) {
        const gridView = region.call( "getViews", "grid" );
        const model = gridView.model;
        const record = model.getRecord( primaryKey );
        // If we find the record in the model, refresh just that row
        if ( record ) {
            model.fetchRecords( [ record ] );
            return;
        }
    }
    // Inserts and deletes require a full grid refresh
    // Although refresh() API supports "maintain pagination" flag,
    // in APEX 26.1, it only applies to Interactive Report regions
    region.refresh();
}

Using the Refresh Row Helper with a Modal Editing Dialog

In the calling page with an Interactive Grid of employees, to react to any dialog closure, add a dynamic action event handler on the Dialog Closed event targeting the body jQuery selector. This fires when the user closes the modal page that lets users create, edit, or delete an employee. In a dynamic action step of type Execute JavaScript Code, call the helper function to refresh the row or grid, passing in the primary key of the row that was edited. As explained in the next section, you can create your modal page to return null if a row was created or deleted. In those cases, the function refreshes the full grid instead of just the edited row. The two lines of code appear below. The first line gets the dialog return item P2_EMPNO_EDITED returned by modal dialog page. The second line calls refreshGridRowOrRegion(), passing null as a fallback if the dialog didn't return a primary key that was edited.

// Get the primary key returned for an updated row in dialog return item
const editedEmpno = this.data && this.data.P2_EMPNO_EDITED;
// Refresh one row when possible; otherwise refresh the grid.
refreshGridRowOrRegion( "employees", editedEmpno || null );

The figure shows these two lines of dynamic action JavaScript in the Code Editor. This handy window is one click away from the Property Editor whenever you want a more developer-friendly editing experience. It features code completion and syntax highlighting for JavaScript, PL/SQL, SQL, HTML, and CSS.

Configuring Modal Dialog to Return Edited Primary Key or Null Otherwise

In the modal dialog page, as shown below, a Pre-Rendering computation sets the value of a hidden P2_EMPNO_EDITED page item to the P2_EMPNO item value passed in for the edit case.

The following figure shows the Close Dialog page process returns P2_EMPNO_EDITED as a dialog return item by configuring its Items to Return setting.

Just before closing the dialog, the figure shows the Clear EMPNO_EDITED for Delete page process of type Clear Session State clears P2_EMPNO_EDITED when the DELETE button is pressed. This ensures the Close Dialog handler on the calling page does a full grid refresh if a row is deleted.

Finally, the modal dialog can avoid the end user's ever seeing a database referential integrity violation error by showing the (Delete) button only when the employee being edited is not referenced as another employee's manager. Using the Server-side Condition type of No Rows Returned, it configures the following SQL query. If an employee is found with the current employee as manager, then a row is returned and the button does not display.

select empno
  from emp
 where mgr = :P2_EMPNO

This combination produces an Employees grid page that automatically updates just the edited row on return from the modal dialog.