Formulas in Grid

The Grid component comes with built-in support for Formulas. In order to use the feature, you will need to do:

• Add a reference to formula-parser.min.js
• Set the formulas property to true

There are two options to define formulas - column formulas by using the column's formula property or cell formulas by using the cell's formula property.
This example shows how to define a column formula which is applied to all cells within the column.

const gridOptions = {
	dataSourceSettings: {
		dataFields: [
			{ name: 'firstName', dataType: 'string' },
			{ name: 'lastName', dataType: 'string' },
			{ name: 'productName', map: 'product.name', dataType: 'string' },
			{ name: 'quantity', map: 'product.quantity', dataType: 'number' },
			{ name: 'price', map: 'product.price', dataType: 'number' },
			{ name: 'total', dataType: 'string' }
		]
	},
	formulas: true,
	behavior: {
		columnResizeMode: 'growAndShrink'
	},
	sorting: {
		enabled: true
	},
	dataSource: [
		{
			firstName: 'Andrew',
			lastName: 'Burke',
			product: {
				name: 'Ice Coffee', price: 10, quantity: 3
			}
		},
		{
			firstName: 'Petra',
			lastName: 'Williams',
			product: {
				name: 'Espresso', price: 7, quantity: 5
			}
		},
		{
			firstName: 'Kevin',
			lastName: 'Baker',
			product: {
				name: 'Frappucino', price: 6, quantity: 4
			}
		}
	],
	selection: {
		enabled: true,
		allowCellSelection: true,
		mode: 'extended'
	},
	editing: {
		enabled: true
	},
	layout: {
		autoHeight: true
	},
	columns: [
		{
			label: 'First Name', dataField: 'firstName'
		},
		{ label: 'Last Name', dataField: 'lastName' },
		{ label: 'Product', dataField: 'productName' },
		{
			label: 'Quantity', dataField: 'quantity', cellsAlign: 'right'
		},
		{ label: 'Unit Price', dataField: 'price', cellsAlign: 'right', cellsFormat: 'c2' },
		{ label: 'Total', align: 'right', dataField: 'total', cellsAlign: 'right', cellsFormat: 'c2', formula: 'COL(quantity) * COL(price)' }
	]
}

Now, we can try something else. Let's assume that we want to display 'True' or 'False' depending on whether the Total is above 25. The new column definition with the formula will look like:

{ label: 'Total > 25', align: 'right', dataField: 'total', cellsAlign: 'right', formula: 'IF(COL(price) * COL(quantity) > 25, "Yes", "No")' }

In case you want to define a cell formula

const rows = this.grid.rows;
rows[0].cells[0].formula = "=SUM(C2:C2)";

Concatenation operator

The concatenation operator is used to combine multiple text strings into a single value.

Operator	Meaning	Example	Description
&	Concatenation	"a" & "b"	Concatenates two arguments
(left and right) into one

Reference operators

The reference operators are used to perform calculations of combined ranges.

Operator	Meaning	Example	Description
: (colon)	Range operator	A1:B1	Makes one reference to multiple cells between the two specified references.
, (comma)	Union operator	A1:B1,A2:B2	Returns the intersection of multiple ranges.
(space)	Intersection operator	A1:B1 A2:B2	Finds the intersection of the two ranges.