Skip to main content

Advanced Computed Fields

Computed fields evaluate a formula expression and display the result as a read-only value in form mode and grid mode. This page covers patterns beyond single-operation formulas — nested conditionals, SWITCH-based scoring, date arithmetic, null-safe patterns, and performance.

For the basics of adding a computed field to a blueprint section, see Blueprint Field Types.

Expression foundations​

Computed field expressions use the same engine as the Output Designer. References to other fields in the same section use square bracket notation:

[FieldName]

For fields in a repeating (grid) section, each row's expression evaluates against that row's field values — [FieldName] refers to the current row.

References to fields outside the current section use fully qualified paths:

[sections.SectionName.FieldName]

See Expression Reference for the complete function library.

Pattern 1 — Nested IF for multi-tier status​

Use nested IF() to compute a text or RAG value from a numeric threshold.

Goal: Classify schedule performance index (SPI) as Red / Amber / Green based on thresholds.

IF([SPI] < 0.85, "Red",
  IF([SPI] < 0.95, "Amber",
    "Green"))

Output type: Text (or RAG if the output type is set to RAG).

Tips for nested IF:

  • Keep nesting to 3 levels maximum — deeper nesting becomes hard to maintain.
  • The first condition that evaluates to true wins — order matters (most restrictive first).
  • Use consistent indentation in the expression editor for readability.

Pattern 2 — SWITCH for discrete value mapping​

SWITCH is cleaner than nested IF when mapping a field's specific values to outputs.

Goal: Map a Select field ("Status") to a numeric score for aggregation.

SWITCH([Status],
  "Complete", 100,
  "In Progress", 50,
  "Not Started", 0,
  "On Hold", 25,
  0
)

The last argument (0) is the default — returned when no case matches.

Use SWITCH when:

  • You are mapping 4+ discrete values.
  • Each value maps to a different result.
  • The condition is equality-based (not range-based).

Use nested IF when:

  • The condition is a range comparison (< 0.85, > 100).
  • Some branches have compound conditions (AND, OR).

Pattern 3 — RAG-triggered narrative label​

Goal: A computed Text field that returns a human-readable label based on a RAG field value, used in output narrative text.

SWITCH([OverallRAG],
  "Red", "significantly behind programme",
  "Amber", "at risk of delay",
  "Green", "progressing to programme",
  "on track"
)

Reference in an output text component:

The project is currently {{var.ScheduleLabel}}.

This pattern avoids hardcoding narrative in the output template — the phrasing updates automatically when contributors update the RAG field.

Pattern 4 — Date arithmetic​

Date arithmetic computes durations, remaining days, or comparative dates.

Days remaining until a date​

DATEDIFF("day", TODAY(), [ForecastFinish])

Returns a positive number if the forecast finish is in the future, negative if overdue.

Duration between two dates (weeks)​

DATEDIFF("week", [ActualStart], [ActualFinish])

Overdue flag​

IF(AND([ForecastFinish] < TODAY(), [Status] <> "Complete"), "Overdue", "On Track")

Number of days since last update​

DATEDIFF("day", [LastUpdated], TODAY())

Date arithmetic with edition tokens​

Edition-level tokens are available in computed field expressions:

TokenResolves to
TODAY()Current date at render time
EDITION_START()Edition reporting period start date
EDITION_END()Edition reporting period end date
EDITION_DATA_DATE()Edition data date

Pattern 5 — Null-safe expressions​

Fields that are optional or not yet filled return null. Operations on null often produce null or errors. Use ISNULL() and IFNULL() to handle missing values gracefully.

ISNULL — check if a value is missing​

ISNULL([ForecastFinish])

Returns true if the field is empty, false if it has a value.

IFNULL — substitute a default for null​

IFNULL([ForecastFinish], [PlannedFinish])

Returns [ForecastFinish] if it has a value; otherwise returns [PlannedFinish].

Safe division (avoid divide-by-zero)​

IF([BudgetCost] = 0, null, [ActualCost] / [BudgetCost])

Or using IFNULL to default to zero:

IFNULL([ActualCost] / NULLIF([BudgetCost], 0), 0)

NULLIF(a, b) returns null if a = b, otherwise returns a. This converts a zero denominator to null before division, avoiding a divide-by-zero error.

Null-safe string concatenation​

CONCAT(IFNULL([FirstName], ""), " ", IFNULL([LastName], ""))

Without IFNULL, concatenating a null field produces null for the entire expression.

Pattern 6 — Weighted score​

Goal: Compute a weighted average of multiple numeric scores.

([CostScore] * 0.4 + [ScheduleScore] * 0.35 + [QualityScore] * 0.25)

To handle missing fields:

(IFNULL([CostScore], 0) * 0.4 +
 IFNULL([ScheduleScore], 0) * 0.35 +
 IFNULL([QualityScore], 0) * 0.25)
note

Weighted scores with null-substituted zeros can be misleading — a missing score treated as 0 will pull the weighted average down. Consider using ISNULL to detect incomplete scoring and return a separate "Incomplete" label instead of a numeric score.

Pattern 7 — Cross-section lookup in computed fields​

Computed fields in one section can reference fields in other sections of the same blueprint using the fully qualified path:

[sections.SectionName.FieldName]

Goal: The Summary section computes a total from the Risk Register section's row count.

[sections.RiskRegister.row_count]

Goal: The Commercial Summary computes variance from the Budget section.

[sections.BudgetSummary.BudgetAtCompletion] - [sections.CostSection.ActualCost]
caution

Cross-section references create a dependency between sections. If the referenced section's field is renamed or removed, the computed field will produce an error. Document cross-section dependencies in the blueprint description.

Performance considerations​

Computed fields recalculate in real time as contributors edit. For complex expressions or blueprints with many computed fields, this can cause noticeable lag in form mode.

High-impact patterns:

  • Long nested IFs (>4 levels)
  • Multiple cross-section references
  • Expressions referencing repeating section aggregates (e.g. SUM of all rows in a grid section)

Optimisation strategies:

  1. Move computations that are only needed in output to the Output Designer as calculated fields — they only evaluate at render time, not during editing.
  2. Cache intermediate values in intermediate computed fields (compute in steps).
  3. Avoid aggregating over repeating sections in computed fields — use output-level aggregation instead.

Expression validation​

The blueprint field editor validates expressions in real time:

  • Green checkmark: Expression is syntactically valid.
  • Red error: Syntax error — hover to see the error message.
  • Yellow warning: Expression is valid but may produce null in some cases (e.g. references an optional field without null handling).

Test computed fields in a test edition before publishing the blueprint — verify that edge cases (null values, zero denominators, all-empty grid rows) produce the expected output.