Formula (calculated) columns for the data grids

[ChronosSF]

Component / Area

Grid

Is your feature request related to a problem?

The grid has no concept of a column whose value is derived rather than stored, and no way for an end user
to create one. Every value path in the data pipeline resolves a column against the record by field path —
resolveNestedPath(record, columnFieldPath(field)) in
filtering-strategy.ts:270,
grid-sorting-strategy.ts:103,
merge-strategy.ts:120,
tree-grid-filtering-strategy.ts:34
and pipes.ts:336.

Today a "calculated column" is faked in one of two ways, and both are lossy:

1. formatter (column.component.ts:735) —
   changes only what is rendered. Sorting, filtering, grouping, summaries, search and export all still see the
   underlying (usually undefined) field value. It is also declared /* blazorOnlyScript */, so it does not
   cross over to the Web Components / Blazor wrappers.
2. Pre-computing the value into the data source. This works, but it forces the app to re-derive on every
   edit, breaks down with remote data and batch editing, bloats the row model, and — critically — makes the set
   of derived columns something only a developer can change, at build time.

The escape hatch of "unbound column + custom strategies" exists but is unergonomic: the user must supply a
sortStrategy, a filters operand, a groupingComparer, a mergingComparer and a summaries operand
separately and consistently just to make one derived column behave like a real one. See
grid.groupby.spec.ts:885
(should group unbound column with custom grouping strategy) for how much scaffolding that takes.

Both workarounds miss the part users actually ask for. Someone coming from Excel does not want an API to
declare Price * Quantity — they want to click a button in the grid, type =[Price] * [Quantity], and get a
new column that sorts, filters, groups, summarizes and exports like any other. A formula column that only
a developer can author is a fraction of the feature.

Describe the solution you'd like

Introduce a formula column: a column whose value is produced by a declarative expression evaluated against
the row, which participates in the full data pipeline as if it were a stored field, and which end users can
create, edit and remove at runtime through built-in grid UI.

Scope of the MVP

The MVP is deliberately end-to-end — engine and UI — because a developer-only API does not solve the problem
that motivates the request.

Engine and data pipeline:

• Expression-backed column value, resolved before sorting/filtering/grouping/summaries rather than at render time.
• A safe, serializable expression language (no eval, no Function constructor).
• Cross-column references ([Price] * [Quantity]) and a function library.
• Dependency tracking and incremental recalculation on cell/row edits, transactions and data source changes.
• Read-only cells by default; editing the formula, not the value.
• Participation in: sorting, filtering (including Excel-style filtering value lists), grouping, summaries,
  search, clipboard copy, Excel/CSV export as computed values, and column state persistence.

UI (part of the MVP, not a follow-up):

• A formula editor dialog — expression input with syntax validation, a column reference picker, a function
  list, a result data type selector, a header/name input, and a live preview against the first few rows.
• An entry point to create a calculated column — a grid toolbar action, mirroring
  igx-grid-toolbar-advanced-filtering.
• Edit and delete of an existing formula column from its header menu / column actions.
• Error presentation — an error value in the offending cell (#DIV/0!-style) plus an indicator on the
  column header, and inline validation in the editor before the formula is committed.
• Full keyboard, screen reader, theming and localization parity with the rest of the grid UI (see
  non-functional requirements — these are acceptance criteria, not polish).

Explicitly out of scope for the MVP — call this out in the issue so it is not assumed:

• Cross-row / cross-sheet references (SUM(A1:A10), references to other rows). The MVP is strictly
  row-scoped, plus column-level aggregates only if Q4 below is answered yes.
• Circular reference resolution beyond detection and clear error reporting.
• Server-side formula evaluation for remote data (see risk R3).
• Export of live = worksheet formulas to Excel (values only in the MVP).
• Tree Grid aggregate references, Pivot calculated measures, Grid Lite support.

Functional requirements — engine

#	Requirement
F1	A column can declare a formula instead of (or in addition to) a field.
F2	The formula is evaluated per record and the result is what the whole pipeline sees, not just the renderer.
F3	Formulas may reference other columns, including other formula columns, by header or field name.
F4	Formula columns are sortable, filterable, groupable and summarizable with no extra strategy code.
F5	The result has a dataType (inferred from the expression, overridable), so date/number/currency pipeArgs, editors and filtering operands work unchanged.
F6	Cells are read-only by default; editable on a formula column means "edit the formula", not "edit the value".
F7	Editing a dependency recalculates dependents — through cell edit, row edit, batch editing/transactions and data reassignment.
F8	Evaluation errors are contained: a bad row yields an error value in that cell, never a thrown exception that kills the pipeline.
F9	Circular dependencies are detected at definition time and reported, not left to blow the stack.
F10	Formula definitions — including ones created at runtime by the user — round-trip through IgxGridStateDirective.
F11	Excel and CSV export contain the computed values.
F12	The API is expressible for the Web Components and Blazor wrappers — i.e. the primary form must be a string, not a callback.

Functional requirements — UI

#	Requirement
U1	The grid exposes a toolbar action that opens the formula editor and adds a new calculated column on commit.
U2	The editor validates the expression as it is typed — unknown column, unknown function, arity mismatch, syntax error — and blocks commit with a message pointing at the offending token.
U3	The editor offers autocomplete for column references and function names, reusing IgxAutocompleteDirective.
U4	The editor shows a live preview of the computed result over the first N visible rows before the column is created.
U5	The user can set the new column's header, result data type and format (pipeArgs) from the editor.
U6	An existing formula column can be edited or deleted from its header context menu / column actions, without losing grid state.
U7	Cells whose evaluation failed render a themed error state with the error kind; the header carries an indicator when any row in the column errored.
U8	The whole feature can be disabled, or restricted to a set of columns, by the developer (allowFormulaColumns) — apps that must not let users derive new data can turn it off.
U9	User-created columns are indistinguishable from developer-declared ones for every other grid feature (pinning, hiding, moving, resizing, selection, export).
U10	All editor strings come from the grid resource strings and ship translations.

Non-functional requirements

• Virtualization-safe. Evaluation must not run over the whole data set on every change detection cycle.
  Memoize per record and formula version; evaluate visible rows eagerly and aggregates lazily.
• Zoneless-safe. No NG0100 (see the in-flight NG0100-zoneless-audit.md work) — recalculation must not
  mutate bound state during a render pass.
• No eval / new Function. The evaluator must be a real tokenizer plus AST walker so the feature works
  under a strict CSP. This is a hard requirement given SECURITY.md, and it is sharper now that formulas are
  authored by end users rather than only by developers.
• Accessible. The editor is a dialog: focus trap, labelled controls, aria-describedby on validation
  errors, full keyboard operation, and screen-reader-announced error states in cells — same bar as the
  advanced filtering dialog.
• Themed. Theme files for all supported themes, light and dark, following the
  query builder theme layout
  (_base.scss, _derived.scss, light/, dark/, shared/).
• Localized. New strings added to
  grid-resources.ts and translated in
  projects/igniteui-angular-i18n/.
• Tree-shakable. Apps that never use a formula column must pay for neither the parser nor the editor UI —
  the editor should be an opt-in import like the toolbar actions are.

Proposed API or Usage

<!-- Developer-declared formula column -->
<igx-column header="Total"
            [formula]="'[Price] * [Quantity] * (1 - [Discount])'"
            dataType="currency"
            [pipeArgs]="{ digitsInfo: '1.2-2', currencyCode: 'EUR' }">
</igx-column>

<igx-column header="Status" [formula]="statusFormula" dataType="string"></igx-column>

<!-- End-user authoring: toolbar entry point, mirroring the advanced filtering action -->
<igx-grid [data]="data" [allowFormulaColumns]="true">
    <igx-grid-toolbar>
        <igx-grid-toolbar-actions>
            <igx-grid-toolbar-formula-column></igx-grid-toolbar-formula-column>
        </igx-grid-toolbar-actions>
    </igx-grid-toolbar>
</igx-grid>



// statusFormula = `IF([Stock] = 0, 'Out of stock', IF([Stock] < 10, 'Low', 'OK'))`

// --- Programmatic / column definition object ---
grid.columns = [
    { field: 'Price', dataType: 'number' },
    { field: 'Quantity', dataType: 'number' },
    {
        header: 'Total',
        // string form: serializable, works in WC/Blazor, round-trips through grid state,
        // and is the same shape the UI produces
        formula: '[Price] * [Quantity]',
        dataType: 'currency'
    },
    {
        header: 'Margin %',
        // escape hatch: full TS, Angular-only, /* blazorOnlyScript */, not authorable from the UI
        formulaFn: (row: any) => (row.Total - row.Cost) / row.Total,
        dependsOn: ['Total', 'Cost'],   // explicit deps when they cannot be parsed
        dataType: 'percent'
    }
];

// --- Runtime authoring (what the UI calls under the hood) ---
grid.addFormulaColumn({ header: 'Total', formula: '[Price] * [Quantity]', dataType: 'currency' });
grid.getColumnByName('Total').formula = '[Price] * [Quantity] * 1.2';   // recalculates dependents

// --- Validation, usable standalone by an app building its own editor ---
const result = grid.formulaEngine.validate('[Price] * [Qty]');
// { valid: false, error: 'UNKNOWN_COLUMN', token: 'Qty', position: 10 }

// --- Events ---
grid.formulaColumnAdded.subscribe((e: IFormulaColumnEventArgs) => { /* ... */ });
grid.formulaColumnEdited.subscribe((e: IFormulaColumnEventArgs) => { /* ... */ });
grid.formulaError.subscribe((e: IFormulaErrorEventArgs) => { /* ... */ });


### New / changed public surface (sketch)

| Member | Location | Notes |
|---|---|---|
| `formula: string` | `IgxColumnComponent` | Primary, serializable form; what the UI writes. |
| `formulaFn?: (rowData) => any` | `IgxColumnComponent` | Angular-only escape hatch, `/* blazorOnlyScript */`. |
| `dependsOn?: string[]` | `IgxColumnComponent` | Explicit dependencies for `formulaFn`. |
| `isFormulaColumn: boolean` (readonly) | `ColumnType` | Consumed by editing, export, state and the header menu. |
| `formulaErrorTemplate` | `IgxColumnComponent` | Themed error cell override. |
| `allowFormulaColumns: boolean` | grid base | Master switch for end-user authoring (U8). |
| `addFormulaColumn(def)` / `removeFormulaColumn(field)` | grid base | Runtime API behind the UI. |
| `formulaEngine: IgxFormulaEngine` | grid base | Parse / validate / evaluate; injectable and swappable. |
| `IgxFormulaEditorComponent` | new, `grids/core/src/formula/` | The dialog; usable standalone. |
| `IgxGridToolbarFormulaColumnComponent` | `grids/core/src/toolbar/` | Entry point, mirrors the advanced filtering action. |
| `formula?: string` on `IColumnState` | [state-base.directive.ts:61](projects/igniteui-angular/grids/core/src/state-base.directive.ts#L61) | Persistence of user-created columns. |
| `formulaColumnAdded` / `formulaColumnEdited` / `formulaError` | grid base | Events and diagnostics. |

### Suggested expression language (MVP)

- **Operators:** `+ - * / %`, `^`, comparison `= <> < <= > >=`, logical `AND` / `OR` / `NOT`, string concat `&`.
- **References:** `[Column Header]` or `[field.nested.path]`.
- **Literals:** number, single-quoted string, `TRUE` / `FALSE`, `NULL`.
- **Functions (starter set, Excel-named):**
  - Math: `ABS ROUND ROUNDUP ROUNDDOWN CEILING FLOOR MIN MAX SUM POWER SQRT MOD`
  - Logic: `IF IFS AND OR NOT ISBLANK ISERROR IFERROR`
  - Text: `CONCAT LEFT RIGHT MID LEN UPPER LOWER TRIM SUBSTITUTE TEXT`
  - Date: `TODAY NOW YEAR MONTH DAY DATEDIF EDATE`
- **Error values:** `#DIV/0!`, `#VALUE!`, `#REF!`, `#CIRCULAR!` — mirroring Excel so the semantics are familiar.

The function registry must be public and extensible, since the editor's function list and autocomplete are
generated from it: registering a custom function must surface it in the UI with no extra work.

Describe alternatives you've considered

• formatter only — rejected: display-only, invisible to sorting, filtering, grouping, summaries and
  export, and unusable as a target for end-user authoring.
• Pre-computing in the data source — the current guidance; works, but pushes recalculation, edit tracking
  and remote-data consistency onto every app, and cannot support user-created columns at all.
• Unbound column plus custom strategies — possible today, but requires a matched set of sorting, filtering,
  grouping, merging and summary strategies per column; too much ceremony for a common need, and impossible to
  drive from a dialog.
• Engine-only MVP, UI in a later release — considered and rejected: the primary ask is end-user authoring,
  and shipping the engine alone would mean every customer builds their own editor against a parser API that was
  never designed to support one. Deferring the UI would also let the parser ship without structured, positioned
  errors, which is exactly the thing an editor needs.
• A pipe-based derived data source — recomputes the whole array on any change and defeats virtualization.

How important is this feature to you?

Important — affects my workflow

Additional context

No response

[ChronosSF]

Implementation considerations:

Engine

1. Parser and evaluator. New, framework-agnostic, in core/src/data-operations/formula/. Tokenizer → AST →
   evaluator with a pluggable function registry. Zero Angular dependencies, so it is unit-testable in isolation
   and reusable by Grid Lite and the Pivot Grid later. The parser must expose structured errors with token
   positions, not just a boolean — the editor's inline validation depends on it, so design that in from the
   start rather than retrofitting it.
2. Column integration. IgxColumnComponent gains formula; ColumnType gains isFormulaColumn.
3. Value resolution hook. The single most important change: every
   resolveNestedPath(record, columnFieldPath(field)) call site listed in section 1 must route through a
   resolver that first asks "is this field a formula column?". This is the piece that makes sorting, filtering,
   grouping and merging work for free, and it is also the main performance risk — it sits on the hot path
   of every data operation.
4. Dependency graph and invalidation. Build a DAG from parsed references; on update_cell / update_row
   (api.service.ts:148) and on transaction
   commit, invalidate only the affected records × dependent columns.
5. Editing. Block value edits on formula cells; IgxGridValidationService should not run field validators
   against derived values.
6. Summaries. IgxSummaryOperand.operate(data, allData, fieldName, groupRecord)
   (grid-summary.ts:53) receives the
   record array, so it works once step 3 lands; verify group-level summaries over formula columns.
7. Excel-style filtering. The unique-values list in
   excel-style-search.component.ts:632
   must enumerate computed values.
8. State. Extend IColumnState and the column feature's save/restore so user-created columns survive a
   reload — without this, runtime authoring is a toy.

UI

9. IgxFormulaEditorComponent. Overlay-hosted dialog following the
   advanced filtering dialog
   pattern (IgxOverlayService, focus trap, apply/cancel footer). Contents: header name input, expression
   editor with inline validation, column reference list, function list grouped by category, result data type +
   format controls, live preview table.
   The Query Builder is the closest existing precedent for
   structured expression authoring and should be mined for interaction patterns, drag-to-insert, overlay
   handling and theming — but not for grammar, since it is a boolean tree and this is arithmetic.
10. Autocomplete. Wire IgxAutocompleteDirective
    to the expression input, sourcing column names and the function registry, triggered on [ and on
    identifier characters.
11. Toolbar entry point. IgxGridToolbarFormulaColumnComponent, copying the structure of
    grid-toolbar-advanced-filtering.component.ts
    (grid-toolbar.base.ts, its own token, public_api export).
12. Header menu integration. Edit / delete entries for formula columns, alongside the existing column
    actions (column-actions/).
13. Error UI. Themed error cell state, header indicator, formulaError event, and an accessible
    announcement path.
14. Theming. Theme files for the editor in all supported themes, light and dark, per the query builder layout.
15. Localization. New strings in
    grid-resources.ts plus translations in
    projects/igniteui-angular-i18n/.
16. Wrappers. Verify the Blazor/WC code-gen annotations: the string formula must marshal; formulaFn is
    /* blazorOnlyScript */; the editor component and toolbar action need wrapper coverage.

Suggested delivery phases

• MVP (v1) — steps 1–16 above. Engine, full pipeline participation, recalculation on edit, state
  persistence, and the formula editor UI with a toolbar entry point, validation, autocomplete, error
  presentation, theming, a11y and localization. Ships on igx-grid only, values-only export, deliberately
  small function library.
• v1.next. Real = worksheet formula export from IgxExcelExporterService (AST → A1/R1C1 translation),
  expanded function library, richer editor affordances (function signature help, formula history/reuse),
  conditional formatting over formula columns
  (extras conditional-formatting directive).
• v2 — grid variants. Tree Grid (formulas over hierarchical records, including references to aggregated
  children), Hierarchical Grid (per-island formulas), Pivot Grid calculated measures (related but a distinct
  feature — likely its own issue), Grid Lite (own pipeline; scope decision needed, see Q5).

Risks and open questions

• R1 — Performance. Injecting formula resolution into the value path taxes every row of every data
  operation. Needs memoization keyed by record identity and formula version, plus a benchmark in
  projects/igniteui-angular-performance/ before merge.
• R2 — Security. No eval / new Function under any circumstance. This hardens now that end users author
  formulas: expressions are untrusted input arriving from the UI and from persisted state. The parser must be
  fuzz-tested for depth and DoS, and allowFormulaColumns gives apps a way to switch authoring off entirely.
• R3 — Remote data. With noop strategies the grid does not own sorting or filtering, so a formula column
  cannot be sorted or filtered server-side. This is worse with a UI, because the user can create a column the
  backend has never heard of. Options: disable authoring under remote strategies, degrade to client-side
  operations for formula columns only, or emit the expression for backend translation. Decision needed.
• R4 — Scope creep toward a spreadsheet. Ignite UI already ships a spreadsheet component; the formula
  column must stay row-scoped and not drift into range references. The editor UI makes this pull stronger —
  users who get a formula bar will ask for cell references next.
• R5 — MVP size. Including the UI roughly doubles the MVP, and the UI work (dialog, autocomplete, theming,
  a11y, i18n, wrapper coverage) is not parallelizable with the engine until the parser's error API is stable.
  Land step 1's structured-error contract early so UI and engine can proceed in parallel.
• Q1 — Reference columns by header (user-friendly, unstable) or field (stable, opaque)? Proposal: accept
  both, canonicalize to field internally, and render headers in the editor. The UI makes this urgent — users
  type what they see, but a renamed header must not break a saved formula.
• Q2 — Should a formula column optionally persist its computed value back into the data record
  (writeBack)? Convenient for export and round-tripping, but it mutates user data.
• Q3 — Locale: is the argument separator , or ;, and are decimal separators locale-aware? Excel is
  locale-dependent here; the proposal is to be locale-independent in the stored string and localize only in
  the editor's display and input handling.
• Q4 — Do we expose column-level aggregates inside row formulas ([Price] / SUM([Price]) for a
  share-of-total)? Powerful, but it introduces two-pass evaluation and interacts badly with virtualization and
  paging.
• Q5 — Does this land in Grid Lite? The engine is dependency-free enough to reuse, but the editor UI pulls
  in overlay, drop-down and dialog dependencies that Lite deliberately avoids.
• Q6 — Are function names localized in the editor (Excel does this per locale) or always English? Proposal:
  always English in the stored expression; the editor may show localized descriptions but not localized names.
• Q7 — Where does the editor live in the package graph? Bundling it into grids/core costs every app;
  a separate secondary entry point keeps it opt-in but adds a package.