Interactive Grid Change Review Before Save (Oracle APEX)

An Interactive grid is a component that is implemented in almost every project, it enables user to edit data "like in Excel". Users can add rows, change many values and then just click save.

But in some cases, users would like to review what was changed, and see all the changes they did, before they are written to the database. This is exactly what this feature does, it adds one more step before the data is updated.

How is it done

Interactive grid and its model has a API documentation here. There are many useful functions and configurations, one of the function is model.getChanges here.

When used, it generates an array of records with changes. Let's look at an example.

I created an app with one page editable interactive grid, that is based on EBA_GRAPHVIZ_EMPLOYEES table. My grid static ID is "IG".

When I open console and use the code below, it will give me array of changed records.

var grid = apex.region("IG").widget().interactiveGrid("getViews", "grid");
var model = grid.model;
var changedRecords = model.getChanges();

I changed just one row - the employee named Steven King. I changed Email, Job and Manager fields.

If I open the record in the console, I can see that there are "original" and "record" values. The original represent values before the changes, and the record values are current values in the grid model.

It is visible, that the Email is changed from SKING to SKING2, the Job from President to Finance Manager and the Manager from nothing to Lex De Haan.

This is all that is needed to show what was changed and let user confirm the changes.

Implementing the Changes Dialog

To show the changes to users, I will need a Region. I create static content region and place it into Dialogs, Drawers and Popups Slot. The static ID of the region will be "CHANGES". And the Template will be Inline Dialog.

As next, I create a hidden page item P1_CHANGES. It will hold my changes JSON value, that will be generated for the changes region.

Then I add a button "Review Changes" to the grid region (the function can be also used in grid config, to replace a native grid Save button).

I attach a dynamic action to the button and I use my JavaScript function, that will get the changes into page item and then open changes region (dialog). It is not the most elegant solution, but it will works.

The function can also resolve the dropdown lists, so in the changes, the display values will be visible instead of the return values (ids).

var grid = apex.region("IG").widget().interactiveGrid("getViews", "grid");
var model = grid.model;
if (model.isChanged()) {
  var jsonChanges = [];
  var mainFieldId = model.getFieldKey('LAST_NAME'); //Last name is a column that will represent the record name
  var gridColumns = grid.getColumns();

  var changedRecords = model.getChanges();
  changedRecords.forEach(function (record) {
    var recordName = record.original[mainFieldId];
    var changedFields = {};
    for (var field in record.fields) {
      if (record.fields[field].changed === true) {
        if (!changedFields.fields) changedFields.fields = [];
        // Find the column with matching property
        var columnInfo = gridColumns.find(function (col) {
          return col.property === field;
        });
        var columnId = gridColumns.find(function (col) {
          if (col.property === field) {
            return col.index;
          }
        });
        columnId = columnId.index;
        var originalValue = record.original[columnId];
        // Check if originalValue is an object with 'd' property
        if (originalValue && typeof originalValue === 'object') {
          originalValue = originalValue.d;
        }
        var newValue = record.record[columnId];
        // Check if newValue is an object with 'd' property
        if (newValue && typeof newValue === 'object') {
          newValue = newValue.d;
        }
        // Push the heading if found, otherwise use the field name
        if (columnInfo && columnInfo.label) {
          changedFields.fields.push({
            field: field,
            label: columnInfo.label,
            originalValue: originalValue,
            newValue: newValue
          });
        } else {
          changedFields.fields.push({
            field: field,
            label: field,
            originalValue: originalValue,
            newValue: newValue
          });
        }
      }
    }
    jsonChanges.push({
      recordName: recordName,
      fields: changedFields.fields ? changedFields.fields : {}
    });

  });
  console.log(JSON.stringify(jsonChanges));
  apex.item("P1_CHANGES").setValue(JSON.stringify(jsonChanges));
  apex.theme.openRegion("CHANGES");
}

Next I create an Interactive Grid in Changes region. The source will use json_table from the page item. The "Page items to submit" value should be P1_CHANGES.

Source of the grid below.

SELECT 
    record_name,
    field,
    label,
    original_value,
    new_value
FROM 
    JSON_TABLE(:P1_CHANGES,
        '$[*]' 
        COLUMNS (
            record_name VARCHAR2(1000) PATH '$.recordName',
            NESTED PATH '$.fields[*]' COLUMNS (
                field VARCHAR2(1000) PATH '$.field',
                label VARCHAR2(1000) PATH '$.label',
                original_value VARCHAR2(1000) PATH '$.originalValue',
                new_value VARCHAR2(1000) PATH '$.newValue'
            )
        )
    ) 
;

Now to make it all smooth I add one more dynamic action, on change of P1_CHANGES that will refresh the interactive grid. So it will automatically submit the page item when the value change. I give it a debounce, like 200ms, so it will have time to refresh the region.

Result

Now, when I change few values and click the review button, the changes are visible in the popup window.

The JSON created from the changes looks like that:

[
  {
    "recordName": "King",
    "fields": [
      {
        "field": "JOB_ID",
        "label": "Job",
        "originalValue": "President",
        "newValue": "Finance Manager"
      },
      {
        "field": "SALARY",
        "label": "Salary",
        "originalValue": "24000",
        "newValue": "999"
      },
      {
        "field": "MANAGER_ID",
        "label": "Manager",
        "originalValue": "",
        "newValue": "David Austin"
      }
    ]
  },
  {
    "recordName": "Faviet",
    "fields": [
      {
        "field": "EMAIL",
        "label": "Email",
        "originalValue": "DFAVIET",
        "newValue": "DFAVIET_2"
      }
    ]
  }
]

It is also possible to add a button to save the changes directly from the dialog by invoking the "save" action on the grid. But it depends if you need that functionality.

This same logic works on page 0, so if you use input parameters in the function, it can show changes for literally all the grids you have in the same one dialog on page 0. You set it up once and then you can use it on any page.

Please be aware, that you should only use javascript API functions, that are documented in the official documentation. And always implement exceptions to prevent any errors, that may break your application.