Formulas & Computed Fields

Set up fields that calculate themselves from other fields on the smarttable.

ℹ️ Where formulas come from A formula field's expression is defined when the smarttable is created from the SharePoint sheet — the cell formula in the entry worksheet becomes the field's formula automatically. No separate setup step is required for basic formulas.

The Engine

Formulas run on HyperFormula — the same open-source spreadsheet engine class used by real spreadsheet products, not a small custom expression parser. That means the formula syntax is genuine Excel-compatible spreadsheet syntax: standard functions (SUM, AVG, IF, ROUND, etc.), operators, and nested expressions all work the way they would in a spreadsheet cell. Instead of referencing a cell address like A1, a formula references another field by its field ID: <pa_12> means "the value currently in field 12."

How Computed Fields Behave

  • Formula fields are editable by default — a user can type over the computed value, though it gets recalculated (and overwritten again) the next time a field it depends on changes
  • Set is_disabled to TRUE on the field to lock it instead, so only the computed value can ever appear
  • The value recalculates instantly whenever a field it depends on changes
  • If the field has min/max acceptance values configured, the computed result is validated against them just like a manually entered value
  • Formulas can reference any other field on the same smarttable, including fields inside a table

Formulas Inside Table Fields

Table fields (Handsontable grids) support formulas per row and per column:

  • A formula in a table column is evaluated independently for every row
  • Table formulas can also reference regular (non-table) fields elsewhere on the smarttable
  • Common uses: computing a row total, flagging out-of-range readings, or aggregating a column (e.g. sum or average across all rows)
💡 Example A "Total" table column can be set to multiply "Quantity" × "Unit Price" for each row, while a summary field elsewhere on the smarttable sums the "Total" column across every row.

Display & PDF Output

When an entry is printed or exported to PDF, the display sheet shows the same computed value that was calculated during entry — it isn't recalculated at print time, so the PDF always matches what the user saw and submitted. There is one exception: a display-sheet cell can carry its own separate formula (marked with a pa_FORMULA comment on the cell in the display worksheet), evaluated fresh at display/print time from the entry's other field values — used for a computed summary or label that only needs to exist on the printed output, not as its own stored field.

💡 Why formulas use unformatted numbers internally If a number field is configured to show comma separators (e.g. 12,34,567 — see Number Formatting), formulas always calculate against the real underlying numeric value, never the display string. A comma-formatted "1,000" isn't valid numeric input for a spreadsheet engine, so the engine is deliberately fed the raw value and only the final result gets formatted for display.
⚠️ Circular References Avoid setting up two formula fields that reference each other (directly or indirectly). As with a spreadsheet, this will produce an error instead of a value.