Skip to content

Computed Cells & Columns

Jonathan Eiten edited this page Dec 6, 2018 · 3 revisions

Definition

Computed values are formulas from which a cell value is derived. A single formula can be shared by multiple cells.

Abstract

Hypergrid cell data can be raw data values or computed values.

Hypergrid supports computed cells and columns through the use of calculators which are JavaScript functions:

  • When a cell value is a function, the function is executed to get the display value.
  • When a column's calculator property is set to a function, the function is executed for every cell in the column to get the display values.

Calculators

Hypergrid calculators are simply JavaScript function objects.

The calling signature for a calculator function is:

function calcAge(dataRow, columnName) { ... }

dataRow is a hash of column values returned by the data model's getRow() method. For the standard JSON data model, this is simply a reference to the existing the data row object. For other data models, see advice below under Alternate data models.

columnName is the hash key for the column in which the current cell resides.

An example calculator might be:

function BMI(dataRow, columnName) {
    return dataRow.weight / Math.pow(dataRow.height, 2);
}

Referencing calculators

Hypergrid supports both cell and column calculators.

Cell calculators

Replace a cell value with a reference to a calculator function:

grid.setValue(gridColumn, gridRow, BMI); // grid coordinates

(FYI: The above example uses grid coordinates. This is not the only way to set a cell value. For example, grid.behavior.dataModel.setValue accepts data coordinates. You can also call the grid method with a CellEvent object in place of the two coordinates. CellEvent objects have various methods for for accessing the various other coordinate systems.)

Column calculators

Define a column's calculator property with a reference to a calculator function, which affects all cells in the column:

myGrid.setColumnProperties(idx, { calculator: 'myCalc' });

// Alternatively:
var myColumn = myGrid.behavior.getColumn(columnIndex);  // or .columns[columnName]
myColumn.properties.calculator = 'myCalc';
// or the short-hand:
myColumn.calculator = 'myCalc';

Hypergrid maintains a calculators registry which is persisted on save state along with all grid, column, and cell properties. The purpose of the registry is to normalize function objects. To facilitate this, whenever you set a column's calculator property to a named function, the registry is is checked first for a function of that name and if found that function is used instead. Once a function is in the registry, you can simply refer to it by name:

myColumn.properties.calculator: function myFunc(dataRow, columnName) {...};
myColumn.properties.calculator: 'myFunc'

If you set it to an anonymous function, it still achieves normalization by using the stringified function as the registry key. (Naming obviously is better!) See calculator setter overloads, below, for a full reference on this.

Note that the calculator property is an exception to the usual property pattern described in the Properties wiki. The calculator property is one of several column-only properties which cannot be defined directly on a cell properties object or the grid properties object.

Utilizing the underlying cell values

Because the cells in such a computed column retain their underlying values, these values are available to the calculator's formula. (This is not the case for a cell calculator, which take the place of the cell value.)

Overriding the computed column calculator

This does however raise the question, How do we override the column calculator for a specific cell in a computed column? The answer is that computed cells have priority over computed columns. Therefore, to override the column calculator for a specific cell, give it a calculator of its own.

Serialization strategies

In general properties are string values to facilitate serialization for loading and saving via a stringified format such as JSON. For performance reasons, calculator functions are created on load and collapsed on save.

To facilitate code normalization and avoid creating many copies of the same function, we encourage persisting the property as "registered" calculator names rather than stringified functions. On load, the calculator name is looked up in the registry and set to a (shared) function reference; on save the reference is located in the registry and the associated key is saved to the property.

Thus, stringified functions can be persisted directly in a cell value in the data JSON object; or in a column's calculator property in a meta-data JSON object; or in a function registry JSON object. To help expand (create) functions from their stringified representations, Hypergrid includes a toFunction function (fin-hypergrid/src/lib/toFunction). This function will automatically be invoked when setting a column's calculator property, when the setter's R-value is a string. (R-values that are already functions are just passed through.)

calculator setter overloads

The calculator property setter is overloaded for any of the following use cases:

  1. Registered function name
    • Function is dereferenced from the calculators registry
  2. Anonymous stringified function
    • The string is functionified (inflated into an actual function object)
    • The function is registered (if not already in the registry) using the entire function string as the registry key
    • Registering the function normalizes other references to it, i.e., other references to the identical function string will not be individually functionified but will use the function in the registry
  3. Named stringified function
    • The string is functionified
    • The function is registered (if not already in the registry) using the function name as the registry key
    • Registering the function normalizes other references to it, i.e., referenced either with a function string using the same name (the first such function definition is the one that is registered) or simply by name (use case 1 above)
  4. Actual function object
    • The function object is stringified and then treated as use case 2 or 3 above as the case may be.
    • Note that this process strips the function of its execution context, which would be lost anyway when state is persisted (serialized). For a work-around if context is needed, see the next section.

Calculators with context

Calculator functions lose their execution contexts when they are set. The reason for this is that the calculator functions are meant to be serializable to facilitate persisting state. They are re-inflated when state is reloaded. We don't allow calculators to have their own context because the context cannot be serialized.

Work-around

CAVEAT: The following work-around will create a calculator that depend on its execution context. Saving state will not persist the context of such calculator functions. On reloading the state from persistent storage, the context will have been lost and you will need to programmatically recreate the functions using the same method described below.

"Manually" add your calculator function (with its context intact) to the calculators registry and reference that calculator by name in a calculator property:

function sumCalculatorFactory(cols) {
    return function (dataRow, columnName) {
        var result = 0;
        cols.forEach(each => {result += dataRow[each]});
        return result;
    }
};

var calculatorsRegistry = myGrid.properties.calculators = myGrid.properties.calculators || {};
calculatorsRegistry.totalIncome = sumCalculatorFactory(['passiveIncome', 'earnedIncome']);

myGrid.behavior.columns.income.properties.calculator = 'totalIncome';