Interactive Grid calculations and validations

This article is the continuation of my series about Interactive Grids. I’ve started with simple button customization, dived little bit deeper into the Interactive Grids javascript model and all the other control options, which are given to us there and finally it’s time to look on the Interactive Grid itself. I faced somewhat difficult problem, which I’d be able to solve with JavaScript and HTML quite easily, but not in the way, that would satisfy me in our APEX application. From the solution of this problem came the idea for this article. I hope it’ll be helpful for somebody someday.

Let’s introduce the problem. I was given the task to bring economic statements to our application – this alone is troublesome as my country has several different templates, changing them each 2 – 3 years, but this can be solved with the use of templates and their administration. What brought the problem to different level were necessary calculations and validations. Simple calculations are not problem, but here I faced possible recursive equations. It’d probably be better to demonstrate on example:

Field 1 = Field 2 + Field 6
Field 2 = Field 3 + Field 4 - Field 5
Field 3
Field 4
Field 5
Field 6 = Field 7 / Field 8
Field 7
Field 8
Filed 9 = Field 3 - Field 8

This is just simple example, which indicates what I’ve struggled with. Everything had to be dynamic – you could specify any number of fields in column for the calculation and it doesn’t matter if the field is above or below the calculated field or if it contains value or another equation. The only limitation was to avoid infinite loops, but that’s about the definition of the templates and making sure it doesn’t happen.

Now we can approach definition of the Interactive Grid itself. The code below is taking the data from DUAL table, but of course you’d have take the data from the table you’ll be saving the values with combination of the table storing the templates.

SELECT 1                                      AS id
     , 'R001'                                 AS code
     , 'Sum of R002 + R003'                   AS title
     , NULL                                   AS value
     , 'f([R002])+f([R003])'                  AS equation
     , 'f([R001])>0'                          AS validation
     , 'This row has to be larger than zero.' AS error
FROM DUAL
UNION ALL
SELECT 2                                      AS id
     , 'R002'                                 AS code
     , 'Value 1'                              AS title
     , NULL                                   AS value
     , NULL                                   AS equation
     , NULL                                   AS validation
     , NULL                                   AS error
FROM DUAL
UNION ALL
SELECT 3                                      AS id
     , 'R003'                                 AS code
     , 'Value 3'                              AS title
     , NULL                                   AS value
     , NULL                                   AS equation
     , 'f([R003])<100'                        AS validation
     , 'This row has to be smaller than 100.' AS error
FROM DUAL;

If you don’t need to edit anything (except for the value column, almost all your columns can be set to always readonly and in the column source with query only = Yes. Of course the ID column will be primary key and hidden as a column. The result is visible in the image below.

Each of the columns have their purpose:

• ID: unique identifier
• CODE: by this code you identify the rows for the validations and calculations
• TITLE: name of the row from template – to tell users what they’re filling up
• VALUE: the value you’ll fill in the template or will be calculated by the equation (don’t forget to setup the value column static ID: value-field)
• EQUATION: how to calculate value of the row (no calculation if empty)
• VALIDATION: row value validation (no validation if empty)
• ERROR: error to display to user if it doesn’t pass the validation

So far it should be pretty clear and the only thing you might be wondering about is why in the equation or validation I’m using f([…]), but those are just simply delimitations to identify each row code in the equation and validation (I’d personally use something simpler, but we got the definition  of templates premade and I could not be bothered to replace it).

As for the concept itself – the algorithm which is running for each row from top to bottom every time value changes is:

• Does the row have equation?
  • No = skip the row (doesn’t matter if value is there or not for now)
  • Yes = get the equation into variable – search for every row mentioned in the equation and replace the reference to that row with the row value or equation. Repeat until all references are replaced by values (here is one of the places to check for the infinite loops, as it uses recursion to replace the references) and then calculate the result and save it to the row.
• Move to next row and repeat.

After the code runs for every row of the grid, you can process the validations now (technically you’d be able to do it during the first access to the row, when you’re calculating it, but that’d make it worse from the performance perspective). For every row now:

• Does the row have validation?
  • No = skip the row
  • Yes = get the validation into variable and replace all references of other rows with their values. It’s no longer need to calculate those values as they were calculated in the step before so you can avoid second time using recursion. After you replace all references, you evaluate the validation resulting in true or false. In case of false add the error message to the value to inform user.

There was one step, which I found difficult to work with to make the performance of the calculation better and that’s the fact, you have to access the information in the Interactive Grid by the row ID, which when you have reference by the code of the row is little bit difficult (and you cannot put IDs in the equation or validation as it changes with each new created statement). After some thinking I came with the solution that on the load of the page, you run AJAX callback to DB, where you get the one specific statement and ID’s for each row code – this matrix will help you then identify that e.g. row R001 is in fact ID 1. This resulted in two things created on page – javascript dynamic action run during the page load and AJAX callback process.

Dynamic action:

// Adding overlay and wait popup
$('body').append('<div class="ui-widget-overlay ui-front"/>');
var wp = apex.widget.waitPopup();

// Call the Ajax Process and set the result JSON into page javascript variable
apex.server.process (  'getJson'
                     , {}
                     , {
                         success: function( pData ) {
                           codesJson = pData;
                           wp.remove();
                           $('body').children('div.ui-widget-overlay').remove();                           
                         }
                       } 
                    );

AJAX callback process:

BEGIN
  APEX_JSON.open_object;
  FOR src IN (
    SELECT '1' as id, '[R001]' as CODE FROM DUAL UNION ALL
    SELECT '2' as id, '[R002]' as CODE FROM DUAL UNION ALL
    SELECT '3' as id, '[R003]' as CODE FROM DUAL
  ) LOOP
    APEX_JSON.write(src.code, src.id);
  END LOOP;
  APEX_JSON.close_all;
END;

With this done, we finally can move onto the code which does the actual calculation and validation. It’s placed in the page javascript part – Function and Global Variable Declaration:

// JSON to get primary key (index) for row codes (1: "[R001]") used in validations and calculations - see the dynamic action and AJAX callback
var codesJson  = {};

// Function to sanitize string before using EVAL()
function sanitize (stringIn) {

  // Replace the {} brakets with () brakets, then remove everything exept:
  //   numbers: 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 (including decimal point)
  //   math operators: +, -, *, /
  //   brackets: (, )
  //   logical operators: =, <, >, &, |, !, ?, :, "
  return stringIn.replace(/{/g, '(').replace(/}/g, ')').replace(/[^\d\.\+\-\*/()=<>&|!\?:"]/g, '');

}

// Function to calculate and validate rows in the Interactive Grid region
function calculateValidate (regionID) {

    // Variables
    var model             = apex.region(regionID).widget().interactiveGrid('getViews','grid').model, // Interactive Grid model
        eqColKey          = model.getFieldKey("EQUATION"), // column with equation
        valueColKey       = model.getFieldKey("VALUE"), // column with value
        validationColKey  = model.getFieldKey("VALIDATION"), // column with validation
        errorColKey       = model.getFieldKey("ERROR"), // column with error message
        cycleLimit        = 1000;

    // For each record do calculation (if equotation for the row exists)
    model.forEach(function(record, index, id) {

      var eqExist = record[eqColKey];

      // Calculation only if equotation exists for the row
      if (eqExist) {

        // Get array of fields "f([...])" needed for the equotation and replace until only values remain
        var eqArray = eqExist.match(/[^f\(\)]+/g),
            counter = 0;

        while (eqArray.join().indexOf('[') > -1) {

          // Get value for field linked from equotation
          var linkRowCode   = eqArray.join().substring(eqArray.join().indexOf('['), eqArray.join().indexOf(']') + 1),
              linkPosition  = eqArray.indexOf(linkRowCode),
              linkIndex     = codesJson[linkRowCode],
              linkRecord    = model._index[linkIndex].record,
              linkEqExist   = linkRecord[eqColKey];

          // If field from equotation contains another equotation, we replace the field link in array for its equotation
          // otherwise we replace it with rows value
          if (linkEqExist) {
            var a1  = eqArray.splice(0, linkPosition),
                a2  = linkEqExist.match(/[^f\(\)]+/g),
                a3  = eqArray.splice(1);
            eqArray = a1.concat("{", a2, "}", a3);
          } else {
            eqArray[linkPosition] = '{' + Number(linkRecord[valueColKey]) + '}';
          }

          // Protection against infinite loop - ending the code after 1000 loops with error
          counter++;
          if (counter > cycleLimit) {
            throw new Error('Row [' + id + ']: More than ' + cycleLimit + ' cycles. Please check the calculation for infinite loop.');
          }

        }

        // All fields replaced by values - final calculation and saving field value
        var finalEq = eqArray.join(''),
            result  = eval(sanitize(finalEq));

        result = ((!isFinite(result) || isNaN(result)) ? '' : result);
        model.setValue(record, "VALUE", result.toString());

      }

    });

    // Validate each record (if validation for the row exists)
    model.forEach(function(record, index, id) {

      var validationExist = record[validationColKey];

      // Control only if validation exists
      if (validationExist) {

        // Get array of fields needed for validation, replace by value until all references replaced
        var errorMessage    = record[errorColKey],
            validationArray = validationExist.match(/[^f\(\)]+/g);

        while (validationArray.join().indexOf('[') > -1) {

          // We get the value for the referenced field
          var linkRowCode   = validationArray.join().substring(validationArray.join().indexOf('['), validationArray.join().indexOf(']') + 1),
              linkPosition  = validationArray.indexOf(linkRowCode),
              linkIndex     = codesJson[linkRowCode],
              linkRecord    = model._index[linkIndex].record;

          // Replace reference with the field value
          validationArray[linkPosition] = '{' + Number(linkRecord[valueColKey]) + '}';
        }

        // All references replaced by values - final validation
        var finalValidation = validationArray.join(''),
            result          = eval(sanitize(finalValidation));

        if (!result) {
          model.setValidity("error", id, 'VALUE', errorMessage);
        } else {
          model.setValidity("valid", id, 'VALUE');
        }

      }

    });

}

// Do calculation & validations after value changed
$(document).on('change', '#value-field', function() {

  // Necessary timeout - to save edited value into Interactive Grid JSON
  setTimeout(function () {

    // Call the calculate / validate function
    calculateValidate('test_ig');

  }, 100);

});

Some notes about the block of code above:

• Line 2: definition for the variable where the row code and ID value JSON pairs are load to from AJAX.
• Lines 5 – 14: as I’m using the javascript eval function to evaluate the results – either for validations or calculations, I’m preferring to remove everything I deemed unnecessary for the action as it might bring some security issues.
• Lines 119 – 129: This is the reason we setup the static ID for the value column. Everytime the value changes it will wait for 0,1s (to save the value into javascript model of interactive grid) and then run calculation for the whole report.
• Line 28 + 79: The definition to run the next block for every row in the model (first for calculations, second for validations).
• Lines 50 – 57: Either replace the reference of the row with the value of that row or with the equation of that row
• Lines 68 – 72: After all replaces in equation sanitize the variable and then evaluate the value and save it to the row.
• Lines 103 – 110: After all replaces in validation sanitize the variable, evaluate the value. If it’s false, add error to the value column and display for the user, otherwise remove any potential error previously attached to the row.

The final result might look something like this (it’d be good idea to hide the equation, validation and error columns from users):

Hope this would be any help or give you some ideas how to do something more with APEX.

Afterword

The code above is perfectly fine and it works as intended, but I didn’t realize one thing during making this script and it came later in the testing when we uploaded our templates, which had over 200 rows. As you can see in the console log below – the total amount of rows is 218, while the page size (pagination setup to scroll in interactive grid attributes) is 100 and the number of loaded rows is 50.

In sense it doesn’t matter, but if the validation or equation references some not yet loaded row, it will crash as it won’t be able to find that row. There is just one simple fix and that’s to preload all rows of the report on page load by the javascript dynamic action:

// Definování proměnných
var $ig       = apex.region("test_ig").widget(),
    gridView  = $ig.interactiveGrid("getViews").grid,
    model,
    count;

function loadBatchOfRecords(model, offset, count) {
  var i = 0;
  model.forEachInPage(offset, count, function(r) {
    i += 1;
    if (i === count || !r) {
      if (r) {
        loadBatchOfRecords(model, offset + count, count);
      }
    }
  });
}

if (gridView) {
    model = gridView.model;
    count = model.getOption("pageSize");
    loadBatchOfRecords(model, 0, count);
}

This change has impact on the page load time, but in the end, there is positive impact as user won’t be loading more data as he’s scrolling through the interactive grid.