Queryable Formula — Directus Extension

A computed field extension for Directus that stores formula results in the database, making them queryable, sortable, and filterable. Includes a visual formula builder, relational field lookups (M2O, O2M, M2M — with nested chain support), cross-formula references with dependency ordering, scheduled recalculation, and a force-recalculate button.

Here's the complete reference of everything available in the formula engine:

Arithmetic Operators

| Operator | Description | Example | | -------- | ------------------ | -------------------------------------- | | + | Addition | {{price}} + {{tax}} | | - | Subtraction | {{total}} - {{discount}} | | * | Multiplication | {{price}} * {{quantity}} | | / | Division | {{total}} / {{count}} | | % | Modulo (remainder) | {{number}} % 2 | | () | Grouping | ({{price}} + {{tax}}) * {{quantity}} |

You can chain them freely:

({{base_price}} * {{quantity}}) - {{discount}} + {{shipping}}

Comparison Operators

Used inside IF():

| Operator | Description | Example inside IF | | -------- | ---------------- | --------------------------------- | | == | Equal | IF({{status}} == "active", ...) | | != | Not equal | IF({{type}} != "free", ...) | | > | Greater than | IF({{age}} > 18, ...) | | < | Less than | IF({{stock}} < 10, ...) | | >= | Greater or equal | IF({{score}} >= 50, ...) | | <= | Less or equal | IF({{price}} <= 100, ...) |

Logical Operators

| Operator | Description | Example | | -------- | ----------- | -------------------------------------------------------- | | && | AND | IF({{age}} > 18 && {{verified}} == true, ...) | | \|\| | OR | IF({{role}} == "admin" \|\| {{role}} == "editor", ...) |

String Functions

| Function | Description | Example | Result | | ------------------- | -------------------------------- | -------------------------------------------- | -------------------- | | CONCAT(a, b, ...) | Join values together | CONCAT({{first_name}}, " ", {{last_name}}) | "John Doe" | | UPPER(a) | Uppercase | UPPER({{name}}) | "JOHN" | | LOWER(a) | Lowercase | LOWER({{email}}) | "[email protected]" | | TRIM(a) | Remove whitespace from both ends | TRIM({{input}}) | "hello" |

Nesting works:

UPPER(CONCAT({{first_name}}, " ", {{last_name}}))
→ "JOHN DOE"

CONCAT(UPPER({{first_name}}), " ", LOWER({{last_name}}))
→ "JOHN doe"

Math Functions

| Function | Description | Example | Result | | ------------------------ | ------------------------- | --------------------------- | -------------- | | ROUND(value) | Round to nearest integer | ROUND({{price}} * 1.1) | 11 | | ROUND(value, decimals) | Round to N decimal places | ROUND({{price}} * 1.1, 2) | 11.05 | | FLOOR(value) | Round down | FLOOR({{rating}}) | 4 (from 4.7) | | CEIL(value) | Round up | CEIL({{rating}}) | 5 (from 4.2) |

Logic Functions

IF(condition, then, else)

IF({{stock}} > 0, "In Stock", "Out of Stock")

IF({{price}} > 100, {{price}} * 0.9, {{price}})

IF({{type}} == "premium", {{price}} * 1.5, {{price}})

Nested IFs (inner IF evaluates first, its result is correctly passed to the outer IF):

IF({{score}} >= 90, "A", IF({{score}} >= 80, "B", IF({{score}} >= 70, "C", "F")))

IF({{age}} > 100, "Not a kid", IF({{age}} > 10, "Youth", "Child"))
→ With age=40: "Youth"

COALESCE(a, b, ...)

Returns the first non-null value:

COALESCE({{nickname}}, {{first_name}}, "Unknown")

If nickname is null but first_name is "John" → returns "John".

Date Functions

Date Creation

| Function | Description | Example | Result | | ---------------------------- | --------------------------------- | --------------------------- | ---------------------------- | | NOW() | Current ISO timestamp | NOW() | "2026-09-03T10:00:00.000Z" | | TODAY() | Current date (no time) | TODAY() | "2026-09-03" | | DATE(year, month, day) | Create a date from components | DATE(2026, 3, 15) | "2026-03-15" | | DATEVALUE(date_string) | Parse a date string to ISO date | DATEVALUE({{created_at}}) | "2026-01-20" | | TIME(hour, minute, second) | Create a time string (HH:MM:SS) | TIME(14, 30, 0) | "14:30:00" | | TIMEVALUE(datetime_string) | Extract time part from a datetime | TIMEVALUE({{created_at}}) | "10:00:00" |

Date Extraction

| Function | Description | Example | Result | | ------------------------ | ----------------------------------- | ---------------------------- | ------ | | YEAR(date) | Extract the year | YEAR({{created_at}}) | 2026 | | MONTH(date) | Extract the month (1–12) | MONTH({{created_at}}) | 3 | | DAY(date) | Extract the day of the month (1–31) | DAY({{created_at}}) | 15 | | HOUR(datetime) | Extract the hour (0–23) | HOUR({{created_at}}) | 14 | | MINUTE(datetime) | Extract the minute (0–59) | MINUTE({{created_at}}) | 30 | | SECOND(datetime) | Extract the second (0–59) | SECOND({{created_at}}) | 0 | | WEEKDAY(date [, type]) | Day of the week | WEEKDAY({{created_at}}) | 1 | | WEEKNUM(date [, type]) | Week number in the year | WEEKNUM({{created_at}}) | 12 | | ISOWEEKNUM(date) | ISO 8601 week number | ISOWEEKNUM({{created_at}}) | 11 |

WEEKDAY types:

| Type | Scheme | | ---- | ----------------------------------- | | 1 | Sunday = 1 … Saturday = 7 (default) | | 2 | Monday = 1 … Sunday = 7 | | 3 | Monday = 0 … Sunday = 6 |

WEEKNUM types:

| Type | Week starts on | | ---- | ---------------- | | 1 | Sunday (default) | | 2 | Monday |

Date Arithmetic

| Function | Description | Example | Result | | --------------------------- | ----------------------------------------- | -------------------------------------------- | -------------- | | DATEDIF(start, end, unit) | Difference between two dates | DATEDIF({{start_date}}, {{end_date}}, "D") | 90 | | DAYS(end, start) | Number of days between two dates | DAYS({{end_date}}, {{start_date}}) | 30 | | EDATE(start, months) | Date offset by N months | EDATE({{start_date}}, 3) | "2026-06-15" | | EOMONTH(start, months) | Last day of the month, offset by N months | EOMONTH({{start_date}}, 1) | "2026-04-30" | | NETWORKDAYS(start, end) | Number of working days (excl. weekends) | NETWORKDAYS({{start_date}}, {{end_date}}) | 22 |

DATEDIF units:

| Unit | Returns | | ------ | -------------------------------- | | "Y" | Complete years between dates | | "M" | Complete months between dates | | "D" | Days between dates | | "YM" | Months, excluding years | | "MD" | Days, excluding months and years | | "YD" | Days, excluding years |

Nesting works with date functions too:

CONCAT("Created in ", YEAR({{created_at}}))
→ "Created in 2026"

DATEDIF({{hired_date}}, NOW(), "Y")
→ 3  (years of service)

IF(DAYS(NOW(), {{due_date}}) > 0, "Overdue", "On track")
→ "Overdue"

CONCAT(YEAR({{date}}), "-Q", CEIL(MONTH({{date}}) / 3))
→ "2026-Q1"

IF(WEEKDAY({{event_date}}, 2) > 5, "Weekend", "Weekday")
→ "Weekday"

CONCAT("Due: ", EDATE({{start_date}}, 6))
→ "Due: 2027-03-15"

EOMONTH({{invoice_date}}, 0)
→ "2026-03-31"  (last day of the invoice's month)

NETWORKDAYS({{project_start}}, {{project_end}})
→ 65  (working days in project)

Literal Values

| Type | Syntax | Example | | ------- | -------------------- | --------------------------------------- | | String | "text" or 'text' | CONCAT({{name}}, " - ", "active") | | Number | 123, 45.67 | {{price}} * 1.15 | | Boolean | true / false | IF({{active}} == true, ...) | | Null | null | COALESCE({{field}}, null, "fallback") |

Field References

Local Fields

Use {{field_name}} to reference any field on the same item:

{{price}}           → numeric field
{{first_name}}      → string field
{{is_active}}       → boolean field
{{created_date}}    → date field (treated as string)

Calculated Fields (Formula-to-Formula References)

You can reference other formula fields in the same collection using the same {{field_name}} syntax. The engine automatically detects the dependency graph and evaluates formulas in the correct order so that each formula always sees a freshly computed value from the fields it depends on.

{{subtotal}}        → another formula field (e.g. price * quantity)
{{vat_amount}}      → formula field that itself references {{subtotal}}

For example, if you have three formula fields:

| Field | Formula | | --- | --- | | subtotal | {{price}} * {{quantity}} | | vat_amount | {{subtotal}} * {{vat_rate}} / 100 | | total | {{subtotal}} + {{vat_amount}} |

All three will compute correctly in a single pass — subtotal is evaluated first, then vat_amount (using the fresh subtotal), then total (using the fresh subtotal and vat_amount).

Cascading on update: If you update a raw field like price, the engine recalculates subtotal, notices it changed, then cascades to vat_amount and total — all in the same write operation.

In the formula builder, formula fields appear in the Available Fields palette with a purple formula badge so you can clearly tell them apart from regular fields. They are also included in the {{ autocomplete popup.

Circular dependency protection

If field A references B and B references A (directly or through a chain), the engine detects the cycle using a topological sort:

• In the UI: the validation panel shows a red error message listing the fields involved in the cycle.
• At runtime: circular fields are logged as a warning and skipped — they keep their last stored value rather than causing an infinite loop.

✘ field_a → field_b → field_a   (cycle — both fields are skipped)
✔ field_a → field_b → raw_field (no cycle — evaluated in order)

Relational Fields

Use dotted notation to pull values from related items. The engine automatically resolves relations via the Directus schema.

M2O (Many-to-One) Lookups

{{category.name}}       → field from the related "categories" table
{{author.email}}        → field from the related "users" table
{{supplier.country}}    → field from the related "suppliers" table

How it works:

1. category is the local FK field on the current item (stores a foreign key like 3)
2. .name is the field on the related table to read
3. The engine looks up the relation in the Directus schema, queries the related table for the record matching the FK, and returns the name column

Nested Relational Lookups

Chain multiple levels of M2O relations using additional dots:

{{category.parent.name}}          → parent category's name (2 hops)
{{author.department.manager.name}} → 3 hops deep

Each segment is resolved in order, following the FK chain through each related table. Relation IDs are normalized during lookup, so nested chains continue to work when Directus provides expanded relation objects or when the database driver returns matching IDs as different primitive types (for example 1 vs "1").

O2M (One-to-Many) Lookups

Reference fields from the "many" side of a relationship. When multiple related items exist, values are returned comma-separated:

{{reviews.rating}}     → "5, 4, 3" (all ratings for this item)
{{comments.text}}      → "Great!, Love it, Nice" (all comment texts)

The field name (e.g., reviews) must match the O2M alias field configured in Directus.

M2M (Many-to-Many) Lookups

Works the same way as O2M — reference the M2M alias field, and values from all related items are comma-separated:

{{tags.name}}          → "Tech, Science, AI"
{{skills.label}}       → "JavaScript, TypeScript, Python"

Mixed Chains

You can combine relation types in a single reference:

{{category.products.name}}   → M2O then O2M: names of all products in the same category
{{reviews.author.name}}      → O2M then M2O: names of all reviewers

Usage Examples

You can use relational refs anywhere you'd use a normal field:

CONCAT({{name}}, " — ", {{category.name}})
→ "Widget Pro — Electronics"

IF({{category.type}} == "premium", {{price}} * 1.2, {{price}})
→ applies a 20% markup for premium categories

CONCAT({{author.first_name}}, " ", {{author.last_name}})
→ "Jane Smith"

UPPER({{supplier.country}})
→ "GERMANY"

Note: O2M/M2M formulas recalculate on item creation, manual "Recalculate All", and CRON schedules. They do not auto-recalculate when related items are created/updated/deleted — use a CRON schedule for those cases.

Real-World Examples

E-commerce — line total:

ROUND({{unit_price}} * {{quantity}} * (1 - {{discount_pct}} / 100), 2)

Full name:

CONCAT({{first_name}}, " ", {{last_name}})

Display label with status:

CONCAT({{name}}, " (", UPPER({{status}}), ")")
→ "Widget Pro (ACTIVE)"

Price tier:

IF({{price}} >= 1000, "Premium", IF({{price}} >= 100, "Standard", "Budget"))

Stock status with quantity:

IF({{stock}} > 50, CONCAT("In Stock (", {{stock}}, ")"), IF({{stock}} > 0, "Low Stock", "Out of Stock"))

Percentage:

CONCAT(ROUND({{completed}} / {{total}} * 100, 1), "%")
→ "73.5%"

Fallback chain:

COALESCE({{display_name}}, CONCAT({{first_name}}, " ", {{last_name}}), {{email}}, "Anonymous")

Product with category label (relational):

CONCAT({{name}}, " [", UPPER({{category.name}}), "]")
→ "Widget Pro [ELECTRONICS]"

Order total with tax rate from related region:

ROUND({{subtotal}} * (1 + {{region.tax_rate}} / 100), 2)
→ 107.50 (if subtotal=100, region.tax_rate=7.5)

Author display name with fallback:

COALESCE({{author.display_name}}, CONCAT({{author.first_name}}, " ", {{author.last_name}}), "Unknown Author")

Multi-step pricing using formula fields (cross-formula references):

-- subtotal formula:
{{price}} * {{quantity}}

-- vat_amount formula (references subtotal):
{{subtotal}} * {{vat_rate}} / 100

-- total formula (references both):
{{subtotal}} + {{vat_amount}}

Tiered commission using a base formula:

-- base_revenue formula:
ROUND({{price}} * {{quantity}} * (1 - {{discount_pct}} / 100), 2)

-- commission formula (references base_revenue):
IF({{base_revenue}} > 10000, {{base_revenue}} * 0.1, {{base_revenue}} * 0.05)

Force Recalculate

Each formula field includes a "Recalculate All" button on the item detail page. Clicking it triggers a full recalculation of every row in the collection for that field via a REST API call.

• Endpoint: POST /queryable-formula/recalculate
• Body: { "collection": "products", "field": "total_price" } (field is optional — omit to recalculate all formula fields in the collection)
• Auth: Admin access required
• Response: { "updated": 42, "collection": "products", "field": "total_price" }

You can also check which formula fields exist:

• Endpoint: GET /queryable-formula/status
• Response: { "fields": [{ "collection": "products", "field": "total_price", "formula": "...", "cronSchedule": "*/15 * * * *", "debugMode": false }] }

Debug Calculation

Administrators can enable Debug Mode in any formula field's configuration. When enabled on one formula field, the item detail page shows a "Debug Calculation" button below every queryable formula field in that collection.

Clicking the button runs a record-level debug trace:

• Endpoint: POST /queryable-formula/debug
• Body: { "collection": "products", "field": "total_price", "primaryKey": 1 }
• Auth: Admin access required
• Shows: original formula, resolved relationship refs, M2O/O2M/M2M hop details, expression after field replacement, expression after function processing, stored value, calculated value, and warnings/errors.

This is especially useful when a nested relationship formula returns null even though related data exists. The trace identifies which relation segment was used, which collection was queried, how many rows were found, and where a chain stopped.

Scheduled Recalculation (CRON)

You can configure a cron schedule per formula field to automatically recalculate all values at a regular interval. This is useful for formulas that reference NOW() or relational data that may change without triggering a direct update.

Set the cron expression in the field configuration panel under "Scheduled Recalculation". Preset buttons are provided for common intervals:

| Preset | Cron Expression | Interval | | -------------- | --------------- | ---------- | | Every 5 min | */5 * * * * | 5 minutes | | Every 15 min | */15 * * * * | 15 minutes | | Hourly | 0 * * * * | 1 hour | | Daily midnight | 0 0 * * * | 24 hours | | Weekly (Sun) | 0 0 * * 0 | 7 days |

Leave the field empty to disable scheduling. Schedules are picked up on server startup and refresh automatically when you create or update a formula field.

What's NOT Supported

These are not currently implemented:

| Feature | Notes | | -------------------------------------- | --------------------------------------------------------- | | Aggregations | No SUM() / AVG() across related items | | LENGTH(), SUBSTRING(), REPLACE() | String manipulation beyond CONCAT/UPPER/LOWER/TRIM | | MIN(), MAX(), ABS(), POWER() | Extended math | | Regex | No pattern matching | | Holiday-aware NETWORKDAYS | Holidays parameter not supported — weekends only | | Cross-collection formula references | Can only reference fields within the same collection |