Storing Data as Formulas

You can load data with mathematical expressions. DataGrid parses, evaluates such expressions and presents the result in the related cells.

Storing Data as Formulas

To parse data with mathematical expressions, just set the math parameter to true (by default, it’s disabled).
Columns are set as usual.

Activating math support and parsing data with math expressions

var budget = [
  ...
  {id:4, name:"France", rev:"1229000", exp:"1445000", dif:"=[:3,:2]-[:3,:3]",
    date:"2009"}
];

webix.grid({
  columns:[
      { id:"id",   header:"#"},
    { id:"name", header:"Country"},
    { id:"rev",  header:"Revenue"},
    { id:"exp",  header:"Expenditures"},
    { id:"dif",  header:"Deficit/surplus"},
    { id:"date", header:"Date"}
  ],
  math: true, //this parameter enables math support
  data: budget
})

Related sample:  Using Formulas

Syntax:

1. Formulas must start with the ’=’ sign.

2. Within formulas, you can use:

   • basic math operators: +, -, /, *, (, );
   • cell references;
   • custom functions.

You can refer to a cell in 2 ways:

• [id, field] - takes the value from the specified field of an item with the specified id.

var data = [
  { id:1, num1:"67000", num2:"15000", difference:"=[1,num1]-[1,num2]"},
  { id:2, num1:"56000", num2:"11000", difference:"=[2,num1]-[2,num2]"},
  { id:3, num1:"45300", num2:"10000", difference:"=[3,num1]-[3,num2]"}
];

• [:row_ind, :col_ind] - the indexes of the row and column (starting from 0).
  

var data = [
  { id:1, num1:"67000", num2:"15000", difference:"=[:0,:1]-[:0,:2]"},
  { id:2, num1:"56000", num2:"11000", difference:"=[:1,:1]-[:1,:2]"},
  { id:3, num1:"45300", num2:"10000", difference:"=[:2,:1]-[:2,:2]"}
];

You can also combine 2 variants and use [id, :col_ind] or [:row_ind, field].

var data = [
  { id:1, num1:"67000", num2:"15000", difference:"=[:0,num1]-[:0,num2]"},
  { id:2, num1:"56000", num2:"11000", difference:"=[:1,num1]-[:1,num2]"},
  { id:3, num1:"45300", num2:"10000", difference:"=[:2,num1]-[:2,num2]"}
];

You can call any custom function within a formula:

function mysum(a, b){ return a+b };
...
{ id:3, difference"=mysum([:0, :1], [:0,:2]) + 1 - [:0, :3]" }

Assigning Formulas during Columns Configuration

To apply some formula to a column, you may also use the math attribute of a column. Any formula that will be set as the value of this attribute will be applied to the entire column.

Specifying formulas during columns configuring

var budget = [
  { id:4, name:"France ", rev:"1229000", exp:"1445000", date:"2009"}
];

webix.grid({
  columns:[
      { id:"id",   header:"#"},
    { id:"name", header:"Country"},
    { id:"rev",  header:"Revenue"},
    { id:"exp",  header:"Expenditures"},
    { id:"dif",  header:"Deficit/surplus", math:"[$r,:2] - [$r,exp]"},
    { id:"date", header:"Date"}
  ],
  math: true, // this parameter enables math support
  data: budget
})

Related sample:  Applying Formulas to an Entire Column

Syntax:

1. Formulas are written without the ’=’ sign.

2. The $r keyword refers to the current row ID.

3. The $c keyword refers to the name of the current column.

4. Within formulas, you can use:

   • basic math operators: +, -, /, *, (, ).
   • cell references;
   • custom functions.

You can refer to a cell in 2 ways:

• [id, field] - takes the value from the specified field of an item with the specified id (see usage examples above).
• [:row_ind, :col_ind] - the indexes of the row and column (starting from 0).
  

You can also combine 2 variants and use [id, :col_ind] or [:row_ind, field].

You can call any custom function within a formula:

function mysum(a, b){ return a+b };
...
{ id:"dif", header:"", math:"mysum([$r,:2] - [$r,exp]) + 1 - [$r, :3]" }

Sum Counter

To get the total value of a column, you can use the summColumn counter (instead of a math formula).
The counter is added to the footer of a column (can’t be used in the header).

columns:[
    { id:"title", header:"Film title"},
    { id:"year",  header:"Year"},
    { id:"votes", header:"Votes", footer:{content:"summColumn"}}
]

Editing Formulas Directly in DataGrid

Math formulas are editable directly in the datagrid provided that:

• the editMath property is set to true;
• the datagrid is editable;
• the column where math is used has the “text” editor;
• editaction is set;

webix.grid({
  columns:[
    { id:"diff", header: "Math", math:"[$r,:2] - [$r,exp]", editor:"text"},
  ],
  math: true,
  editable:true,
  editaction:"click",
  editMath:true
})

When this property is omitted, you can still open the editor for the calculated cell value and see the result of the formula in the editor input. However, changes won’t be saved, since math has higher priority.