Formulas

Overview

Formulas use a small, deterministic DSL (not JavaScript, Python, or SQL). The grammar is deliberately simple, safe, and side-effect free. Formulas are parsed once on the server into an AST, cached, and re-evaluated at runtime — sometimes on the server, sometimes on the client, sometimes inlined into the SQL query.

They appear in:

• Calculated columns — formula columns (columnType: "F") that compute values from other fields
• Generated columns — physical columns (columnType: "G") auto-maintained by the database, used for aggregates over child sets (e.g., SUM([lines].[amount]))
• Default values — set initial values on new records
• Validation rules — CHECK constraints that enforce business logic
• Action conditions — canExecute formulas that control when actions are available
• Row-level security — filter expressions that restrict which records a user can see
• Display strings — toString templates with {field} placeholders

Syntax

Field References

Access the current record’s fields with brackets:

[quantity]
[unit_price]
[account_name]

Arithmetic

[quantity] * [unit_price]
[subtotal] + [tax_amount]
[total] - [discount]
[amount] / [count]

String Operations

[first_name] + ' ' + [last_name]

Comparison

dForge uses SQL-style single equals for equality, not ==.

[amount] > 1000
[status] = 'Active'
[end_date] >= [start_date]
[category] != 'Internal'

Other comparison operators: <, <=, >, >=, IN, NOT IN, BETWEEN.

Logical Operators

[amount] > 1000 AND [status] == 'Approved'
[type] == 'A' OR [type] == 'B'
NOT [is_archived]

Conditional Logic

IF([amount] > 10000, 'Enterprise', IF([amount] > 1000, 'Business', 'Standard'))
IF([discount_pct] > 0, [price] * (1 - [discount_pct] / 100), [price])

IN Operator

[status] IN ('Open', 'In Progress', 'Pending')
[type] NOT IN ('Internal', 'Test')
[amount] BETWEEN 100 AND 1000

String Predicates

[name] CONTAINS 'corp'
[email] STARTS_WITH 'admin@'
[file] ENDS_WITH '.pdf'

Navigation Paths (Async Formulas)

Follow references to access fields from related entities. Navigate through the reference virtual column (not the FK column itself):

[customer].[name]
[customer].[industry]
[order].[customer].[email]

Navigation formulas are async — they require a separate data fetch. The platform detects navigation patterns automatically and resolves them after sync formulas. The frontend caches each referenced entity to avoid N+1 fetches.

How It Works

1. [customer] — the reference column on the current record
2. . — follows the reference to the target entity
3. [name] — reads the field from the referenced record

Multi-level navigation is supported: [order].[customer].[name] follows two references. Currently the client supports up to 2 levels.

Sync vs Async Formulas

Type	When evaluated	Example
Sync	Immediately on data load and field change	[quantity] * [unit_price]
Async	After sync formulas, requires data fetch	[account_id].[industry]

The platform automatically classifies formulas based on whether they contain navigation patterns ([field].[field]).

Dependency Tracking

Formulas declare their dependencies through field references. When a field changes:

1. All sync formulas that depend on it are re-evaluated immediately
2. All async formulas that depend on it trigger a data fetch and re-evaluate when the data arrives

Formulas are topologically sorted by dependencies to ensure correct evaluation order.

Built-in Functions

Date Functions

Function	Description	Example
TODAY()	Current date	[due_date] >= TODAY()
NOW()	Current date/time	[created_at] > NOW()
WEEKDAY(date)	Day of week (1-7)	WEEKDAY(TODAY())
STARTMONTH(date)	First day of the date’s month	STARTMONTH(NOW())
ENDMONTH(date)	Last day of the date’s month	ENDMONTH(NOW())
STARTQUARTER(date)	First day of the date’s quarter	STARTQUARTER(NOW())
ENDQUARTER(date)	Last day of the date’s quarter	ENDQUARTER(NOW())
STARTYEAR(date)	First day of the date’s year	STARTYEAR(NOW())

Context Functions

Expression	Description
CURRENT_USER_ID()	The ID of the current user
$[SettingName]	Value of a module setting (resolved from folder context — see below)

Conditional Functions

Function	Description	Example
IF(cond, then, else)	Conditional value	IF([qty] > 100, 'Bulk', 'Standard')

Aggregate Functions (Generated Columns)

Aggregate functions operate on a set field (1:N child collection) and require columnType: "G" — a physical column auto-maintained by the database.

Function	Description
SUM([set].[field])	Sum of field values across child records
COUNT([set].[field])	Count of child records
AVG([set].[field])	Average value
MIN([set].[field])	Minimum value
MAX([set].[field])	Maximum value

Multi-column expressions inside the aggregate are supported:

SUM([lines].[quantity] * [lines].[unit_price])

The platform automatically picks the right strategy: same-row formulas become PostgreSQL GENERATED ALWAYS AS ... STORED columns, while set aggregates use database triggers on the child table.

Settings References

Module settings are configurable values that can be overridden per folder. Reference them in formulas with $[SettingName]:

[department_id] = $[DepartmentId]               -- row-level security
[amount] <= $[ApprovalThreshold]                -- canExecute condition
$[DefaultWarehouseId]                            -- default value

When a formula references a setting, the platform looks it up in the current folder, then walks up the folder tree, and finally falls back to the module default. Returns NULL if no value is set anywhere.

Usage Examples

Calculated Total

[quantity] * [unit_price] * (1 - [discount_pct] / 100)

A formula column on an invoice line that computes the line total after discount.

Full Name

[first_name] + ' ' + [last_name]

A formula column that combines name parts for display.

Overdue Check

[due_date] < TODAY() AND [status] != 'Paid'

Used as a check constraint or conditional formatting rule.

Stage-Based Amount Forecast

IF([stage] == 'Closed Won', [amount],
IF([stage] == 'Negotiation', [amount] * 0.7,
IF([stage] == 'Proposal', [amount] * 0.4,
[amount] * 0.1)))

A weighted pipeline forecast based on opportunity stage.

Cross-Entity Display

[customer].[name] + ' (' + [customer].[industry] + ')'

An async formula that shows customer name and industry from the linked customer record.

toString Templates

The toString property on an entity uses a different syntax — {field} placeholders inside a text template, not bracket expressions:

"toString": "{invoice_number} — {customer}"
"toString": "{first_name} {last_name}"
"toString": "{product} x {quantity}"

When a placeholder names a reference field (e.g., {customer}), the platform recursively resolves it using the referenced entity’s own toString. This is evaluated at SQL time (via JOINs in a single query), avoiding N+1 fetches.

Formula Columns in Entity Definitions

When defining a formula column (column type F), you must specify:

Property	Required	Description
formula	Yes	The expression text
baseDatatypeCd	Yes	Result data type: string, number, date, bool
fieldTypeCd	Yes	How to display: text, number, currency, date, etc.
columnType	Yes	Must be "F"
flags	Yes	Usually "V" (visible) — formula columns are never editable

Check Constraints

Entities can define check constraints using formula syntax:

"constraints": {
  "positive_quantity": {
    "type": "check",
    "expression": "quantity > 0",
    "message": "Quantity must be positive"
  },
  "valid_date_range": {
    "type": "check",
    "expression": "end_date >= start_date",
    "message": "End date must be on or after start date"
  }
}

Check constraints become real PostgreSQL CHECK constraints (server-side enforcement) and are also evaluated client-side via the formula evaluator for immediate UI feedback. If the expression returns false, the save is rejected with the specified error message.

Where Formulas Run

Context	Evaluated on	Why
toString display	Server / SQL time	JOINs to referenced tables in one query — no N+1
canExecute	Client + Server	Client for instant button state; server re-checks for security
Simple calculated fields	Client	Immediate feedback when the user edits a field
Default values	Server	Applied on insert
Row-level security	Server	Enforced at query time
Validation / CHECK	Server (and client for UI feedback)	Enforced on save
Generated column aggregates	Database	PostgreSQL trigger or GENERATED ALWAYS AS column