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.

10 Comments

    1. Hey,
      Under the IG region, under your target column, you will see an attribute called “JavaScript Initialization Code”. It’s a bit lower in the list. That’s where this code goes.

      Like

  1. Hi Stefan, I agree it is very useful blog. John Snyder’s comment to your blog “There is also a way to update aggregates”. Do you know how? Can you extend your example for live update of GrandTotal row values eg. for sum of Sal+Comm column? Thank you in advance.

    Like

  2. I have a similar requirement, where I need to check col. A values with the col B values in the interactive GRID. If col A!= col B then change the font color of col A as Red otherwise it should be in Black. I am using 19.1,please help me on this.

    Like

  3. Beautifully done, Stefan. Much appreciated.

    But the logic is not entirely encapsulated, is it, needing to be repeated in the SQL. Although, perhaps it would suffice to merely generate the Salcomm column in the Select, without the initial summation; I will test that later.

    Nonetheless, I cannot help wondering why this most common of Grid requirements cannot be implemented “under the hood” and defined declaratively; for as it stands now, this is a very long way away from “low code”.

    Like

    1. @Vladimir The issues with aggregates option should be fixed in 21.1.
      @chris. I hope in the future this is done more automatic “under the hood” and declarative. The API had to come first. Baby steps.

      Like

Leave a Reply to Adeeaan Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s