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.

#orclAPEX 20.1 finally introduces the APEX_IG package

TLDR: play with it here

The long desired APEX_IG package for programically manipulating the Interactive Grid is finally here.

While not feature packed, it brings some vital functionality users have been asking for for a long time, mainly the adding and removing of filters.

The most interesting procedure is add_filter

procedure add_filter
    ( p_page_id             in number
    , p_region_id           in number
    , p_filter_value        in varchar2
    , p_column_name         in varchar2 default null
    , p_operator_abbr       in varchar2 default null
    , p_is_case_sensitive   boolean     default false
    , p_report_id           in number   default null
    );
Let’s see how to use it

p_page_id is easy to get.
For p_region_id we might have to do something like this:

select region_id   
  from apex_application_page_regions
 where application_id = :APP_ID
   and page_id        = :APP_PAGE_ID
   and static_id      = 'emp';

p_filter_value is the actual value for our filter.
p_column_name is the name of the column we wish filter on. Note that if we leave this null, the filter will apply to the whole row.
p_operator_abbr represents the filter operation. Note that in case of a row filter, the operation will always be “contains” and this parameter will be ignored. The following options are available:

AbbriviationSignification
EQEquals
NEQNot Equals
LTLess Than
LTELess than or equal to
GTGreater Than
GTEGreater than or equal to
LIKESQL Like operator
NLIKENot Like
NNull
NNNot Null
CContains
NCNot Contains
INSQL In Operator
NINSQL Not In Operator

p_is_case_sensitive, a boolean, determines if the filter should be case sensitive. Note this is only valid for row based filters.
p_report_id is the ID of the Interactive Grid report we wish to target. We can query the apex_appl_page_ig_rpts view to find it out. More often than not however, we will only want to affect the most recently viewed (or the current) report, in which case we can leave this attribute null.

Our call will end up looking something like this:

declare
    l_region_id number;
begin
    select region_id
      into l_region_id
      from apex_application_page_regions
     where application_id = :APP_ID
       and page_id        = :APP_PAGE_ID
       and static_id      = 'emp';

    apex_ig.add_filter
        ( p_page_id           => :APP_PAGE_ID
        , p_region_id         => l_region_id
        , p_filter_value      => 'PRESIDENT'
        , p_column_name       => 'JOB'
        , p_operator_abbr     => 'EQ'
        , p_report_id         => null
        );
end;
Where does this code go?

You will probably want to call this procedure in the Pre Rendering or Processing section of your page, depending on your business requirement.

Calling this function from an AJAX context (Execute PL/SQL Dynamic Action) also works, but as of now, even if you refresh the Interactive Grid after, although the data will be filtered, the filter will not appear in the header section of the Grid. A page refresh will be needed to achieve visual consistency.

What else is in there?

Others procedures include:

  • The self explanatory get_last_viewed_report_id
  • validate_report_id will validate whether the report_id belongs to a specific region
  • reset_report will reset all changes of the report, be it hidden/rearranged columns, filters, sorting, etc and bring it back to its initial state.
  • clear_report will remove all filters. Currently there is no way to target a specific filter.
  • delete_report deletes a non-primary saved report.
  • change_report_owner can be used to change the owner of a non-primary saved report.