Calculated columns from the menu

Let people add their own formula columns: a dialog with the fields to click in, a live preview and errors; edit and remove them from the menu, keep them with stateKey, or add them from code.

Paid feature · Formulas. Free for 30 days on a new domain; after that it needs a free personal key or a company key. See pricing

Live demoCalculated columns

Add Amount = qty * price from the column menu.

Code for this demo

import { createGrid, type ColumnDef, type Row } from '@freegridjs/core';
import { makeRows } from '../data';
import { button, separator, text, tr, type DemoContext } from '../ui';

export default function mount(ctx: DemoContext) {
  const h = (en: string, ko: string) => tr(ctx.lang, { en, ko });
  const data = makeRows(300).map((r, i) => ({ ...r, qty: 1 + (i % 9), price: 1000 + (i * 137) % 9000, cost: 600 + (i * 71) % 5000 }));
  const grid = createGrid(ctx.host, {
    columns: [
      { field: 'name', header: h('Name', '이름'), width: 150 },
      { field: 'qty', header: h('Qty', '수량'), type: 'number', width: 90, editable: true },
      { field: 'price', header: h('Price', '단가'), type: 'number', format: '#,##0', width: 110, editable: true },
      { field: 'cost', header: h('Cost', '원가'), type: 'number', format: '#,##0', width: 110, editable: true },
    ],
    data, locale: ctx.lang, headerContextMenu: true,
  });
  grid.on('calculatedColumnsChange', e => ctx.log(JSON.stringify(e.columns)));
  button(ctx, h('Add calculated column…', '계산 컬럼 추가…'), () => grid.openCalculatedColumnDialog());
  button(ctx, h('Add Amount = qty * price', '금액 = qty * price 추가'), () => grid.addCalculatedColumn({ id: 'amount', header: h('Amount', '금액'), formula: '=qty*price', format: '#,##0' }));
  button(ctx, h('Add Margin %', '마진율 추가'), () => grid.addCalculatedColumn({ id: 'margin', header: h('Margin', '마진율'), formula: '=(price-cost)/price', format: '0.0%' }));
  text(ctx, h('Or use the column menu (⋮) → Add calculated column…; a calculated column\'s own menu edits or removes it.',
    '컬럼 메뉴(⋮) → 계산 컬럼 추가…도 됩니다. 계산 컬럼 자신의 메뉴에서 편집·삭제합니다.'));
  return grid;
}

A calculated column is a formula column made while the grid runs, by people from the column menu or by your code. It sorts, filters, groups, totals and exports like any other column, and its values follow edits to the fields it reads.

The dialog

Open the column menu (⋮) of any column and choose Add calculated column…. With headerContextMenu, it is in the header right-click menu too.
Type a name (it becomes the header).
Write the formula, starting with =. Click a field chip to put its name at the caret.
The preview shows the result for the first five rows as you type. A field that does not exist or a syntax error shows a message and Save does nothing.
Save (or Ctrl+Enter) adds the column at the end.

A calculated column's own menu has Edit calculated column… (the same dialog, with Remove) and Remove calculated column.

From code

const id = grid.addCalculatedColumn({ header: 'Amount', formula: '=qty * price' });         // 'calc1'
grid.addCalculatedColumn({ id: 'margin', header: 'Margin', formula: '=(price - cost) / price', format: '0.0%' });
grid.addCalculatedColumn({ id: 'label', header: 'Label', formula: '=name & " (" & city & ")"', type: 'text' });

grid.getCalculatedColumns();          // [{ id, header, formula, type?, format? }]
grid.addCalculatedColumn({ id: 'margin', header: 'Margin %', formula: '=(price - cost) / price * 100' });  // same id: replaced
grid.removeCalculatedColumn('label');
grid.openCalculatedColumnDialog();    // from your own button; openCalculatedColumnDialog('margin') edits one
grid.on('calculatedColumnsChange', e => save(e.columns));

Name	Type	Description
`addCalculatedColumn({ id?, header, formula, type?, format? })`	`string \| null`	Add (or replace, same id) a column. Returns its id, or null when the formula has an error or names a field that does not exist. type is 'number' (default) or 'text'.
`removeCalculatedColumn(id)`	`void`	Take it away.
`getCalculatedColumns()`	`CalculatedColumn[]`	The calculated columns, to save.
`openCalculatedColumnDialog(id?)`	`void`	The add dialog, or the edit dialog of one column.
`calculatedColumns (option)`	`boolean`	false hides the menu entries (the API still works).
`calculatedColumnsChange (event)`	`{ columns }`	Fired after each add, replace or remove.

Keeping them

stateKey keeps calculated columns by default, with their width and place: they come back after a reload.
getState() has them as calculatedColumns; setState() puts them back (before the column order, so the order applies).
Formulas can use every function on the Formulas page: ROUND, IF, SUM, PREV …

Questions about this page

Can end users add their own calculated columns?

Yes. Add calculated column… in the column menu opens a dialog: a name, a formula like =qty*price with the fields to click in, and a preview of the first rows. The column sorts, filters and exports like any other.