demo D128
Formula entry in htmx
A spreadsheet formula typed into a cell, and the evaluator reach limit
formulaFunctions
This grid accepts a formula typed into a cell, like =quantity*unitPrice or =VAT(total), evaluates it against the row's own fields, and stores the number it produces as the cell's value. It lets the grid stand in for a spreadsheet where users expect to compute in a cell, with a defined limit on how far a formula reaches.
This is the htmx version. A server-rendered table is upgraded in place, so the page is readable before any script runs and gains the full grid when the module loads, which suits a progressively enhanced app.
The configuration
<link rel="stylesheet" href="https://cdn.latticegrid.dev/lattice-grid@1.107.0/lattice-grid.min.css">
<!-- A real, server-rendered table: readable with JavaScript off. data-field on
each header names the column that cell becomes, and data-id on each row is
the key the grid carries it under. -->
<table id="formula-entry-table" data-lattice-grid>
<thead>
<tr>
<th data-field="description">Description</th>
<th data-field="quantity">Qty</th>
<th data-field="unitPrice">Unit price</th>
<th data-field="discount">Discount</th>
<th data-field="total">Total</th>
</tr>
</thead>
<tbody>
<tr data-id="1"><td>Cross-connect, 10G</td><td>1</td><td>12</td><td>0</td><td></td></tr>
<tr data-id="2"><td>Rack space, half cabinet</td><td>8</td><td>49</td><td>0</td><td></td></tr>
<tr data-id="3"><td>Remote hands, per hour</td><td>15</td><td>86</td><td>0.05</td><td></td></tr>
<!-- the rest of the rows, rendered by your server -->
</tbody>
</table>
<script type="module">
import { hydrateTable } from 'https://cdn.latticegrid.dev/lattice-grid@1.107.0/modules/htmx.esm.min.js';
// The markup carries the rows; a server-rendered table's column types are
// declared in the config hydrateTable takes. The grid replaces the table
// where it stood.
const grid = hydrateTable(document.getElementById('formula-entry-table'), {
edit: true,
selection: 'multiple',
columns: [
{ field: 'description', title: 'Description', layout: { flex: 1, min: 160, pin: 'start' } },
{ field: 'quantity', title: 'Qty', type: 'number', edit: true, layout: { width: 100 } },
{ field: 'unitPrice', title: 'Unit price', type: 'number', edit: true,
format: { style: 'currency', currency: 'GBP' }, layout: { width: 140 } },
{ field: 'discount', title: 'Discount', type: 'number', edit: true,
format: { style: 'percent', decimals: 0 }, layout: { width: 140 } },
{ id: 'lineNet', title: 'Line net', type: 'number',
format: { style: 'currency', currency: 'GBP' }, layout: { width: 130 },
value: {
deps: ['quantity', 'unitPrice', 'discount'],
compute: (d) => d.quantity * d.unitPrice * (1 - d.discount),
} },
{ field: 'total', title: 'Total', type: 'number',
format: { style: 'currency', currency: 'GBP' },
layout: { width: 140, pin: 'end' }, total: 'sum',
// A text editor on a number column is what lets a cell accept a formula:
// the number type's parse reads the text a user typed and evaluates it,
// where the default number editor would hand back a plain number first.
edit: { enabled: true, editor: 'text' } },
],
});
// A typed formula is evaluated against the row it sits in: arithmetic and a
// closed set of built-in functions, with no eval behind it, so what a cell can
// reach is the row and nothing more.
const column = grid.columns.all().find((c) => c.id === 'total');
const row = grid.rows.get(0);
const value = column.dataType.parse({
text: '=ROUND(quantity * unitPrice * (1 - discount), 2)',
value: null, data: row.data, row, column, grid,
});
</script>Typing a formula into a cell
Formula entry lets a cell hold =quantity*unitPrice or =VAT(total) instead of a value typed by hand. The grid parses the formula, evaluates it against the row’s own fields, and writes the number it produces into the cell. A developer reaches for it wherever a grid stands in for a spreadsheet a user already expects to type into: budget lines, pricing sheets, a total worked out from the other columns on the row rather than pasted in by hand. Lattice Grid registers the functions a formula may call through formulaFunctions, so a column can expose a small, named set such as VAT alongside arithmetic without the evaluator accepting an unbounded expression language. Entry works like inline editing: a formula is typed straight into the cell, committed with Enter, and evaluated once against that row, and its result is stored as a plain number. When you want a value that keeps tracking its inputs as they change, a computed column does exactly that: give the column the fields it depends on and it recalculates whenever any of them is written. Lattice Grid tracks a bounded evaluator reach, so a formula reaching across too many fields fails predictably at the configured limit rather than recursing until the tab stalls, which matters for a data grid rendering tens of thousands of rows where a runaway calculation would otherwise block the main thread. A screen reader announces the evaluated result, not the formula text, so a non-sighted user hears the number a sighted user sees, with the formula itself available on request when the cell is focused for editing.
How do I add a formula to a grid cell?
Set the column’s formulaFunctions to the functions a formula is allowed to call, mark the column editable, then type a formula into a cell starting with =, referring to the row’s fields by name. Lattice Grid parses it, evaluates it against that row, and stores the result as the cell’s value. For a value that recalculates as its inputs change, use a computed column instead: declare the fields it depends on and the grid re-runs it whenever one of them is written. An evaluator reach limit caps how far a single formula can walk, so a malformed or excessive reference fails at that boundary rather than degrading performance across the grid.