JavaScript formulas

FastGrid documentation

Not editable JavaScript formulas set in data by developers. Cannot be changed by users.
For formulas entered into cells by users see Editable formulas.
When reading cell value, the JavaScript formula is calculated only if the cell has not set editable formula.
The JavaScript formulas can contain any JavaScript code, no safety restrictions are applied.
The JavaScript formulas can be used to calculate row cells, toolbar cells and cell attributes.
The JavaScript formulas can be used to modify / format the cell / toolbar cell / attribute value for display.

The code in F / Formula can use these predefined variables:
a) Column id as (calculated) cell value from actual row or column id with prefix "_" as (calculated) cell value from actual row converted to number or column id with prefix "$" as value from V / U only. E.g. Formula:"A+B". Only if grid contains less columns than MaxFormulaCols.
b) Value as direct (not calculated) value from actual cell. Or _Value as direct (not calculated) value from actual cell converted to number. Or $Value as direct value from actual cell V / U.
c) CValue as (calculated) value from actual cell. Or _CValue as (calculated) value from actual cell converted to number. Useful for attribute calculations.
d) Grid as actual Grid object, Row as actual Row object, Col as actual column object. To use for API calls.
e) Result as intermediate result of summary calculation (only in condition parameter of summary function).
f) Calc as object with FastGrid functions like sum, count to pass it as parameter when calling custom function that calls the FastGrid functions.
g) FastGrid function calls like sum, count, ... E.g. Formula:"sum('A')*C".
If the formula defines or uses custom variables or functions, their names cannot be the same as the predefined variables, check especially the points a) and e)!

If the formula is only expression, its value is automatically returned. If the formula contains statement(s), it must return value by standard return keyword.
For example "(_A+_B)/get('Toolbar1','XXX',0)" is expression and need not use return, but "var r = get('Toolbar1','XXX',0); return (_A+_B)/r;" contains statements and must return value.

Circular references are not checked, result values in such case are unsure and can differ after every recalculation.

Recalculation
Formulas are calculated only when the cell value is read for first time. Next time of reading the cell value is returned the previously calculated result.
The stored Formula results are cleared after change in grid.
There are two types of formulas according to stored results, Simple and Complex. It is chosen automatically by formula code or it can be set by FormulaCache attribute (1 = Complex, 2 = Simple).
The Simple formulas use only sources from the same row and their results are cleared only if this row cells values change.
The Simple formula is identified if its JavaScript code does not use return and does not call any function or calls only basic JavaScript functions of Number, String, Date, Array and Math and no source cell has Complex formula.
The Complex formulas can use sources from any row and their results are cleared after any change in grid.
Defines cell JavaScript formulas in the row.
Defined as an object with values assigned to column ids. For example: { id:1, F:{ C:"A+B", D:"sum('C')/B" } }
It supports also Array and String definition by AIndex like A attribute, but these definitions have slower processing and are used rarely.
The JavaScript formulas F / Formula are calculated after the editable formulas in E.
If defined in default row, the values are copied to the rows' F attributes on loading (only where the row F item has null value), so later changes in default row do not change the cell formulas.
In rare cases cell JavaScript formulas can be defined also in columns or default columns.
These values are copied to the rows' F attributes on loading (only where the row F item has null value), so later changes of columns do not change the cell formulas.
JavaScript formulas used in all cells in the Row or Col.
Note that Formula attribute is not set in Cell (A / DefCells) - use F row attribute or ValueFormula attribute instead.
When the cell formula (set in F / Formula) will be recalculated.
0 - the formula will be recalculated on every calculation. Should be used only for very fast formulas.
1 - the formula will be recalculated after every change in grid. This is Complex formula can be related to data outside its row. To explicitly preset Complex formula use return statement in the Formula code.
2 - the formula will be recalculated only after any change in its row. This Simple formula must be related only to data in its row.
If not set, it is automatically chosen according to the formula code as 1 or 2.
Formula to calculate cell attribute value.
To calculate attribute for whole column or row, set column or row attribute DA to the default cell id where you can define the attribute formula.
If changed by API SetAttr, call also Repaint(2).
When the cell attribute formula (set in {Attribute}Formula) will be recalculated.
0 - the formula will be recalculated on every calculation. Should be used only for very fast formulas.
1 - the formula will be recalculated after every change in grid. This is Complex formula can be related to data outside its row.
2 - the formula will be recalculated only after any change in its row. This Simple formula must be related only to data in its row.
If not set, it is automatically chosen according to the formula code as 1 or 2.
If changed by API SetAttr, call also Repaint(2).
Comma separated string or array of attribute names that can be used in F and Formula. Any change in these attributes value will recalculate grid.
The attributes are checked in all sources: Grid, Row, Col, Cell, Toolbar, Tool.
If set, the row or column cells are removed from aggregate calculations by both JavaScript and editable formulas.
By default it is set to 1 in all special rows and columns and to 0 in all data rows.
Maximum columns in grid to replace column names by cell values in JavaScript formulas in F and Formula.
If grid contains more columns, the column names cannot be used in the formulas, to get cell values use Grid.GetValue(Row,'xxx') / Grid.GetNumber(Row,'xxx') instead.
Decrease the value if there are many different formulas in grid and processing the columns slows parsing the formulas.
If set, recalculates RowParts specified by RecalcOnParts after every specified change, bit array:
&1 - after focus change, &2 - after start / end editing, &4 - during editing on change between cursor and selected text, &8 - during editing on every selection / cursor change, &16 - after row / column selection change, &32 - after row / column collapse / expand, &64 - during dragging focus.
Bit values 4 and 8 are ignored if edited toolbar cell in the same row part as the toolbar to recalculate.
List of part ids to be recalculated on action specified by RecalcOn.
If not set, the recalculation uses all Toolbar parts.
If set, does not recalculate grid formulas after standard changes like editing or row / column manipulation.
1. bit &1 - does not recalculate Complex formulas in rows.
2. bit &2 - does not recalculate Complex formulas in toolbars.
3. bit &4 - does not recalculate Simple formulas in rows and toolbars.
4. bit &8 - does not recalculate Editable formulas in rows.
If set, call Recalculate to recalculate grid manually or update results by SetResult.
Comma separated string or array of grid ids that will be recalculated on changes in this grid.
Use if some other grids uses this grid cells as formula sources or mirrors.
If set row to object, sets up recalculating all the row formulas on the next Paint. Recalculates both Simple and Complex formulas.
If not set row, sets up recalculating all Complex formulas in grid on the next Paint.
If set row to 1, sets up recalculating all Simple and Complex formulas in grid on the next Paint.
Fast function, does not do the Paint.
It is faster but less universal and safe than Get.
Returns cell JavaScript formula or editable formula. For edit = 0/null returns JavaScript formula, for edit = 1 return editable formula.
row can be null to read column JavaScript Formula, col can be null to read row JavaScript Formula, only if not set edit.
row, col can be also Toolbar cell, but only for JavaScript formula if not set edit.
Called to get JavaScript formula of any cell, row, column, toolbar and toolbar cell. Called to get editable formula of any cell. It is called from GetFormula with the same row, col and edit parameters.
formula is actual formula to be returned. Return formula or new formula.
Returning null is the same as returning formula. To return null value return global constant FGrid.Null.
Low level API event called many times, especially during calculations.
It is faster but less universal and safe than Set.
Sets formula to the cell JavaScript formula or editable formula.
For changes = null sets JavaScript formula, for changes >= 0 returns editable formula.
Returns true if the formula was changed, false if the formula is the same as the cell formula (no change was done) and null for error and incorrect formula.

changes controls how the cell is marked as changed if setting editable formula:
0 - simple set - fast, does not mark or clear changes, does not store or change original value, does not test if the value is the same.
1 - set and mark - marks changes, stores original value before the first change, tests if the value is the same. If the formula is the same as original value, clears the marked changes.
2 - set, mark and restore - like 2, but if formula is null restores original value and clears the marked changes.
3 - set and clear - clears marked changes and clears original value, the new cell value is taken as original unchanged value. Returns false if the value is the same as actual value and no original value is stored.
4 - set and test - like 0 but tests if the value is the same.

changes=1/2/3 is forced to 4 if not set first bit of CellChanges.

If changing JavaScript formula by changes = null, it tests if the value is the same, but does not mark the changes.
row, col can be also Toolbar cell, but only for JavaScript formula if not set changes.
Called before changing JavaScript formula of any cell, row, column, toolbar and toolbar cell and editable formula of any cell. It is called from SetFormula with the same row, col and changes parameters.
formula is formula string to be set. Return formula or new formula to change it.
Returning null is the same as returning formula. To return null formula return global constant FGrid.Null.
To cancel setting the formula return global constant FGrid.Cancel.
Called after changed JavaScript formula of any cell, row, column, toolbar and toolbar cell and editable formula of any cell. It is called from SetFormula with the same row, col and changes parameters.
formula is the formula that has been set.
It is called only if the formula was really changed, so it is not called after every OnSetFormula call.
Calculates formula like it is in the given cell. Does not cache the results. For edit = 1 caculates editable formula, for 0 calculates JavaScript formula.
Returns pre-calculated Formula value stored for given cell or null if does not exist. If set attr, returns pre-calculated value for given attribute stored for given cell.
Sets pre-calculated Formula value stored for given cell to value. If value is null, clears the pre-calculated value, so the next read will recalculate the Formula.
If set attr, sets pre-calculated value for given attribute stored for given cell.
always controls setting, when the pre-calculated value does not exist: 0 - returns false, 1 sets the value as Complex formula result, 2 sets the value as Simple formula results.
Returns true for success and false if value does not exist and always is not set.
Adds custom formula function func to FastGrid globally under name.Use type = 0 or 2 to add JavaScript formula function.
If func is null and type&2, removes the function name, if exists.
For type&1 = 0 - adds to JavaScript formula functions, = 1 - adds to Editable formula functions.
For type&2 = 0 - if name exists returns false, = 1 if name exists, overwrites it.
localname is function name used in Editable formulas as localized name; if not set, name is used.
suggest is function text for suggest; if not set, localname or name is used.
Returns true for success or false if func is not function or name is not set or name exists and type&2 is not set.

Custom JavaScript formula functions
The func is added as method of internal calculation object and can use this keyword with:
this.Row - actual row object where it is calculated.
this.Col - actual column object where it is calculated.
this.Grid - actual grid object where it is calculated.
this.func(...) - any predefined or custom function in JavaScript formulas.

For example: FGrid.AddFunction(function(v){ return this.Row.id+","+this.Col.id+","+v; },"myfunc");

Functions for JavaScript formulas

Aggregate functions

It is JavaScript formula function, for sum in editable formula see sum.
Sums all values in column or row col in part part, in cells where the condition is satisfied.
col can be Row or Col object or string id. If col is null, sums in actual column where the formula is. If col id 1, sums in actual row where the formula is.
part is string id of the Part where will be summed all cells in the column or row col.
If part is null, sums all children of actual row or column (opposite to col).

condition is formula string to evaluate, must return boolean value. It is JavaScript code like the F / Formula.
Unlike standard JavaScript formula it can use Result variable as intermediate result of the summary function.
And the Row variable contains actually iterated row or column (the opposite to col).

type specifies which rows will be iterated, bit array:
1. bit &1 for 1 - all children / all rows in part, 0 - only immediate children / root rows in part
2. bit &2 include collapsed children,
3. bit &4 include manually hidden rows / columns,
4. bit &8 include filtered rows,
5. bit &16 include deleted rows,
6. bit &32 not used
7. bit &64 if the row has no children to iterate returns the cell value.
Default type is 14.

Examples:

sum()	Sums values in column where is called. Iterates only immediate child rows of row where is called, including hidden, filtered and deleted rows.
sum('A')	Sums values in column 'A'. Iterates only immediate child rows of row where is called, including hidden, filtered and deleted rows.
sum(null,'Body')	Sums values in column where is called. Iterates all root rows in "Body" part, including hidden, filtered and deleted rows.
sum(null,null,null,1)	Sums values in column where is called. Iterates all child rows, including their children, of row where is called, excludes hidden, filtered and deleted rows.
sum(1,'Cols')	Sums values in row '1'. Iterates all root columns in "Cols" part, including hidden, filtered and deleted columns.
sum(null,null,'Grid.GetD(Row)=="N1"')	Sums all values in column where is called. Iterates only immediate child rows with DefRow id='N1', including hidden, filtered and deleted rows.
sum('A',null,'A<5')	Sums values in column A, only values less than 5. Iterates only immediate child rows of row where is called, including hidden, filtered and deleted rows.
sum('A','Body','B>=5 && B<10',1)	Sums values in column A, only values from rows where B is in range <5,10). Iterates all rows including all their children in "Body" part, excluding hidden, filtered and deleted rows.
sum(null,null,'A<5 && B<10 || A>=5 && Grid.GetNumber(Grid.Rows.F1,"B")<10')	Sums values in actual column in immediate child rows of actual row where is called that satisfy the condition, including hidden, filtered and deleted rows.

It is JavaScript formula function, for sumsq in editable formula see sumsq.
Sums squares of values in the column / row. See sum function.
It is JavaScript formula function.
Sums absolute values in the column / row. If any the value is negative or zero, returns the final result negative.
Use to calculate Bool value according to all its children, see also BoolTree. For parameters see sum function.
It is JavaScript formula function.
Counts not zero values in the column / row. If any child value is negative or zero, returns the result negative.
Use to calculate Bool value according to its immediate children, see also BoolTree. For parameters see sum function.
It is JavaScript formula function, for count in editable formula see count.
Returns count of rows / columns. See sum function.
It is JavaScript formula function, for counta in editable formula see counta.
Returns count of not empty cells in the column / row. See sum function.
It is JavaScript formula function, for count in editable formula see count.
Returns count of empty cells in the column / row. See sum function.
It is JavaScript formula function.
Calculates the expression for every iterated cell.
expression should read Result variable and return its updated value. Result is 0 on start calculation.
expression has the same condition in sum. For other parameters see sum function.
It is JavaScript formula function, for max in editable formula see max.
Returns maximal value in the column / row. If there are no rows / columns to iterate, returns empty string. See sum function.
It is JavaScript formula function, for min in editable formula see min.
Returns minimal value in the column / row. If there are no rows / columns to iterate, returns empty string. See sum function.
It is JavaScript formula function, for product in editable formula see product.
Multiplies all values in the column / row. If there are no rows / columns to iterate, returns 1. See sum function.
It is JavaScript formula function, for average in editable formula see average.
Calculates average of column / row values (sum/count). See sum function.
It is JavaScript formula function, for median in editable formula see median.
Returns median of column / row (middle value of range). If there is no number in the range, returns NaN. See sum function.
It is JavaScript formula function, for mode in editable formula see mode.
Returns modus of column / row (the most frequent number value in range). If there is no number in the range, returns NaN. See sum function.
It is JavaScript formula function, for avedev in editable formula see avedev.
Calculates average deviation of column / row. If there is no number in the range, returns NaN. See sum function.
It is JavaScript formula function, for stdev in editable formula see stdev.
Calculates standard deviation of column / row. If there is no number in the range, returns NaN. See sum function.
It is JavaScript formula function, for stdevp in editable formula see stdevp.
Calculates standard deviation of column / row. The column / row is the selection of data. If there is no number in the range, returns NaN. See sum function.
It is JavaScript formula function, for vara in editable formula see vara.
Calculates the sample variance of column / row. If there is no number in the range, returns NaN. See sum function.
It is JavaScript formula function, for varp in editable formula see varp.
Calculates the sample variance of column / row. The column / row is the selection of data. If there is no number in the range, returns NaN. See sum function.
It is JavaScript formula function, for rank in editable formula see rank.
Returns position of val in sorted column / row (column / row is sorted ascending). Starting from 0. If the val is not present, returns NaN.
See sum function.

String aggregate functions

It is JavaScript formula function.
Puts all values in the column / row to the range of values like "1;4;7;12~15;19".
The source values can be single values or ranges of any Type. Converts date values to numbers.
Sorts the range and joins the overlapped ranges.
See sum function.
It is JavaScript formula function.
Joins values in column / row. Puts all values in the column / row to the range list of values as strings like "5;prs;7;3;abc".
It never converts values to another type. It does not sort or join the ranges.
See sum function.
It is JavaScript formula function.
Joins values in column / row. Puts all values in the column / row to the range list of values as strings like "5;prs;7;3;abc".
The same values will be counted and placed once as "count x value". If the input values are already joined values, it splits them and recalculates the counts.
See sum function.
It is JavaScript formula function.
Sums number values in column as standard sum function.
If any value is a not empty string, it joins the values as joinsum.
If all the values are empty strings or no values are in the column, returns empty string instead of zero.
See sum function.

Other functions

It is JavaScript formula function.
Reads one or more values or attributes from FastGrid object by Get function.
For time critical formulas it is faster to use GetValue to read cell value like: "get(3,'C')" => "Grid.GetValue(Grid.Rows[3],Grid.Cols.C)".
It is JavaScript formula function, for date in editable formula see date.
Converts value to date in milliseconds since 1/1/1970, or if set ExcelDates, to count of days since 1/1/1900.
format can be date format string in that is the value formatted.
It is JavaScript formula function.
Returns minimal value from its arguments. Compares only number values. If no number is in arguments, returns empty string.
It is JavaScript formula function.
Returns maximal value from its arguments. Compares only number values. If no number is in arguments, returns empty string.