Formula Engine Introduction
TX1 ships a custom spreadsheet-style formula engine. It is built on the Shunting Yard algorithm for parsing, an AST (Abstract Syntax Tree) for evaluation, and a dependency graph that tracks every reference so cells recalc when their inputs change.
Where You Can Use Formulas
| Location | Scope | Notes |
|---|---|---|
| Direct Cost item Qty / Rate / Total | Global | Full access to project defines and line references. |
| Direct Cost Breakdown Qty / Rate / Total | Scoped to the breakdown | L# refers to breakdown rows. |
| Overhead item and breakdown fields | As above | |
| Resource Built-Up Breakdown | Scoped to the built-up resource | |
| Custom Columns | Per-row or aggregate | Computed or manual. |
| Project Parametric "Formula" type | Global | Define stores an expression that resolves on use. |
Starting a Formula
Start any cell with =. Hit Enter to save. The cell displays the result; editing it shows the formula again.
Engine Characteristics
- 70+ built-in functions (section 07.4).
- Operators — arithmetic, comparison, logical (07.2).
- References — variables, lines, ranges, custom columns (07.3).
- Project Parametrics and scoped Parametrics (07.5 / 07.6).
- Dependency tracking with cycle detection (07.7).
- Custom column aggregates with the
*CCfunctions (07.8). - Short-circuit logical operators —
&&and||only evaluate the right side if needed. - Lazy IF / IFERROR — unevaluated branches never throw.
Not Reentrant
The engine is single-threaded during evaluation. Concurrent calls throw InvalidOperationException. In practice you never hit this because the UI serialises edits.
Error Handling
| Error | Display |
|---|---|
| Syntax error | #ERR (hover for detail) |
| Unknown variable | #NAME? |
| Division by zero | #DIV/0 |
| Circular reference | #CIRC |
| Overflow / NaN | #NUM! |
Wrap brittle expressions in IFERROR(expr, fallback).
Caching
Parsed formulas are cached (up to 10,000 entries). Saving an identical formula reuses the AST rather than re-parsing. Cache is invalidated when the formula text changes or a referenced define is edited.
When to Use Formulas vs Code Sets vs Custom Columns
| Need | Right tool |
|---|---|
| Derive a value from others | Formula |
| Categorise for reporting | Code set |
| Store extra data | Custom column |
| Reuse a value across items | Project parametric (07.5) |
The Rest of Section 07
- 02 — operators and precedence.
- 03 — variable, line and range references.
- 04 — function reference.
- 05 — project parametrics.
- 06 — scoped parametrics.
- 07 — dependency graph and cycles.
- 08 — custom column aggregates.