DataTable gives you an opportunity to load data stored as mathematical expressions. DataTable parses, evaluates such expressions and presents result in the related cells.
Note that this functionality won't work with the webix.env.strict enabled. By default, the flag is disabled and math is supported.
To start parse data as mathematical expressions, just set parameter math to true (by default, it's disabled).
Columns are specified as usual.
Activating math support and parsing data as math expressions
var budget = [
...
{id:4, name:"France", rev:"1229000", exp:"1445000", dif:"=[:3,:2]-[:3,:3]",
date:"2009"}
];
grid = new webix.ui({
view:"datatable",
container:"box",
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:
Within formulas can be used:
You can refer to a cell in 2 ways:
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]"}
];
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]"}
];
Note that if you use math operations in TreeTable, you can refer to rows only by their IDs, as row indexes are refreshed after each change.
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 in case provided operations are not enough:
function mysum(a, b){ return a+b };
...
{ id:3, difference"=mysum([:0, :1], [:0,:2]) + 1 - [:0, :3]" }
To apply some formula to a column, you may also use the math attribute while configuring 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"}
];
grid = webix.ui({
view:"datatable",
container:"box",
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:
You can refer to a cell in 2 ways:
You can also combine 2 variants and use [id, :col_ind] or [:row_ind, field].
You can call any custom function within a formula in case provided operations are not enough:
function mysum(a, b){ return a+b };
...
{ id:"dif", header:"", math:"mysum([$r,:2] - [$r,exp]) + 1 - [$r, :3]" }
To get the total value of some column you can use the summColumn counter (instead of a math formula), created especially for this need.
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"}}
]
Math formulas are editable directly in the datatable provided that:
view:"datatable",
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 features higher priority.
Back to top