How to dynamically compute Interactive Grid Columns in #orclAPEX 20.2

Consider this scenario: You have a number of editable columns that dictate the value of another column and you wish for the said column to update live, even before hitting Save. If you’ve had this requirement in the past, you probably found it not to be a trivial task.

Currently, one would have to set up a dynamic action that listens to the change event of the base columns, then computes and applies the new value to the target column via a Set Value action. This can get a bit trickier when you have multiple triggering columns. I have also found this approach to be hit or miss, as it seems to fire at undesired times, for example on cell focus. It also seems to have a weird dependency on the “Fire on Initialization” setting of the Set Value action, which appears to have an effect on whether this.data will be populated or not.

A more reliable approach I’ve taken in the past was to subscribe to the model and provide a custom onChange callback. This allows for much more control but is still unnecessarily complex.

As of APEX 20.2 however, an enhanced method has been introduced to achieve this behavior. It is simpler and does not require the creation of an extra Dynamic Action. Meet calcValue and dependsOn.

As part of the model, we can tell a field on the change of which other fields it should be updated, and provide a function that computes and returns its new value. It’s not exactly clear from the docs where these options should go, but after checking the source code, it turns out they should be used like this:

// JavaScript Initialization Code of a specific column
function(options){
    options.defaultGridColumnOptions = {
        dependsOn: ['COL1', 'COL2', ...],
        calcValue: function(argsArray, model, record){
            return 'newValue';
        }
    };
    return options;
}

For a more real-world example, say you have a column that always shows the sum of two other columns. In order to show the correct value on page load, but also in things like exports, the same logic should be duplicated in the initial query. Here I sum the employee salary and commission in an extra column called SALCOMM.

select empno
     , ename
     , job
     , mgr
     , hiredate
     , deptno
     , sal
     , comm
     , nvl(sal, 0) + nvl(comm, 0) as salcomm
  from emp

I have found that the new calcValue approach unfortunately only works on editable columns, even though for this use case the column should be Display Only. To get around this, I have set the column type to Text Field, but I have added CSS class is-readonly to the column’s Appearance -> CSS Classes attribute. I have also set this column to be Query Only.

Onto the JavaScript side, we want to update said column whenever the SAL or COMM columns change, so we set dependsOn to ['SAL', 'COMM']. In the calcValue function, we can either grab the current salary and commission values from the argsArray argument as argsArray[0] and argsArray[1], or use the more verbose model argument. I have also included some basic input checks.

function(options){
    options.defaultGridColumnOptions = {
        dependsOn: ['SAL', 'COMM'],
        calcValue: function(argsArray, model, record){
            const sal = parseFloat(model.getValue(record, 'SAL') || 0);
            const comm = parseFloat(model.getValue(record, 'COMM') || 0);
            if(isNaN(sal) || isNaN(comm)){
                return 'error';
            } else {
                return sal + comm;
            }
        }
    };
    return options;
}

Here it is in action:

Overall I’m very happy with this new approach. The logic is nicely encapsulated in the column node, and we don’t have to create an extra Dynamic Action.