Expressions: formulas and query filters

An expression is a short line of text that xMatix evaluates for you — a default value, a calculated field, a validation condition, a business-rule condition, a view filter or a lookup filter. There are two languages. Formulas run against one record while it is created or saved. Query filters become part of the database query that picks records for a list or a picker. The two look alike but are not interchangeable, so knowing which one you are writing is most of the skill. This page covers where each language is used, what it can read, the syntax, and how to test it.

Overview

When to use it

• Decide something about one record — fill a default, compute a value, check a rule, gate an action: write a formula.
• Choose which records appear — in a list, a saved view or a lookup picker: write a query filter.
• Neither is enough — several steps, external data, or writes to other records: use an automation script, which is a full program rather than an expression.

How xMatix handles it

Formulas are evaluated on the server at fixed points in the save (see what runs when you save a record):

1. Default Value Formula — when a record is created, for each field that is still empty.
2. Calculated Formula — simple calculated fields are computed before validations and business rules, so those see the current result. Calculated fields that aggregate child records are computed later in the same save, after validations and business rules.
3. Validation Condition — on every insert, and on an edit when a field the condition reads has changed.
4. Business-rule Condition Expression — when one of the rule's triggers fires.

Before a formula is evaluated, xMatix resolves the parts that reach outside the record — lookups such as Account.Region, aggregates such as SUM(OrderLines, Amount), User. tokens and PRIORVALUE(...) — and hands the rest to the formula engine. That is why these parts work in conditions and calculated fields even though the engine itself only sees the one record.

Query filters are checked against the entity's fields and then turned into the database query each time the list or picker runs. A view's filter is combined (AND) with whatever the user filters by. A lookup filter is combined with the user's search text. Before a lookup filter runs, its references to the record being edited and to the signed-in user are replaced with their current values.

Prerequisites

• Access to the screen where the expression lives: Setup → Design Studio → Entities for fields, validations, views and lookup filters (setup.metadata.model.manage), and Setup → Process Studio → Business Rules for rules (setup.automation.businessrules.manage).
• The internal names of the fields you will use (the Formula Builder's Fields tab lists them). Expressions use names, not labels. Custom fields carry the _c suffix, for example Region_c.
• The stored values of any picklist you compare against. Expressions compare stored values, not the labels users see.

Procedure

Step 1 — Open the Formula Builder

Formulas are written in the Formula Builder dialog:

• For a field: Setup → Design Studio → Entities → entity → Schema → Fields, edit the field, tab Formulas & Defaults. A Calculated field shows Calculated Formula. Other field types show Default Value Formula. Lookup fields have no formula tab.
• For a validation: Logic → Validations → New or Edit, box Condition.
• For a business rule: open the rule, tab Condition, box Condition Expression.

The dialog has a Fields tab (the entity's fields, its relationships and child collections) and a Functions tab (operators, text, number and date templates, Variables, Collection / LINQ and, for validations and rules, Old values (before this edit)). Clicking an entry inserts it into the Editor. Clicking a field of a child collection inserts a ready-made aggregate.

Step 2 — Write the formula

Type or insert the expression, for example Quantity * UnitPrice for a calculated amount, "Draft" for a default, or Status == "Closed" && ClosedDate == null for a validation that rejects a closed record with no closing date. A validation condition must be true when the record is wrong. A rule condition must be true when the actions should run.

Step 3 — Apply and check the result

Choose Apply. For calculated formulas, validations and rule conditions, the dialog checks the expression on the server first. If it does not pass, the dialog stays open with a Validation error that names the problem. Default value formulas are not checked here.

When you save the field or the rule, xMatix compiles plain formulas on standard entities once more and refuses the save if they do not compile. The message starts "The formula does not compile for …" (fields) or "ConditionFormula does not compile for …" (rules). Formulas that use lookups, aggregates, User. tokens, PRIORVALUE or custom fields, and all formulas on custom entities, depend on the record. They are not judged at this point, so test them.

Step 4 — Write a query filter

• View: Experience → Views → New or Edit, Filter Condition under Data Source.
• Lookup filter: Schema → Lookup Filters → New, choose Open Filter Builder. Build conditions on the Builder tab. A value can be a literal or come from Record Entity, Related Entity or User. Check the raw text on the Advanced tab with Validate.

See Entity behavior for the rest of these dialogs.

Step 5 — Test with real records

• Default: create a record and leave the field empty. It should be filled when the record is saved.
• Calculated field: save a record and read the field. For an aggregate, add, change and delete a child line, and check that the parent's value follows.
• Validation: save a record that breaks the rule. The save should be refused with your message. Then save one that does not break it.
• Business rule: use the rule's Test Runner and read its Diagnostics. A condition that fails to evaluate is reported there.
• View or lookup filter: open the view, or open the picker on a record that should match, and confirm the expected records appear.

Formula reference

Fields and literals

• Fields are written by internal name: Status, TotalAmount, Region_c. The casing of a standard field's name is corrected for you. A custom field can be written with or without its _c suffix.
• Text goes in double quotes: "Draft". A word without quotes is read as a field name, and a field that does not exist breaks the whole formula. This is the most common mistake.
• Numbers are written plainly. Add m for a decimal value where the type matters, as in 0m.
• true, false and null are literals. An empty yes/no field reads as false.
• Picklists compare by stored value: Priority == "High". A value outside the picklist never matches, and nothing warns you.

Operators

"Or" is written as two vertical bars: Status == "Open" || Status == "On Hold". Use brackets to group: (A || B) && C.

Functions

The Formula Builder's Functions tab lists ready-made templates. The most useful:

• Text: Name.Contains("x"), StartsWith, EndsWith, ToUpper(), ToLower(), Trim(), Replace, Substring, Length, string.IsNullOrWhiteSpace(Name).
• Numbers: Math.Round(Amount, 2), Math.Abs, Math.Floor, Math.Ceiling, Math.Min, Math.Max.
• Dates: DueDate.AddDays(30), AddMonths, AddYears, .Date, .Year, .Month, .Day, .DayOfWeek; DateTime.Today and DateTime.UtcNow.
• Patterns: Regex.IsMatch(Phone, "^[0-9]{10}$"). Pattern checks have a time limit, and a pattern that is invalid or takes too long counts as no match.

Variables

User. values are never null: a user with no roles has an empty list, so User.Roles.Contains("X") is simply false. When a background job saves a record, there is no signed-in user and every User. token reads as empty. In validation conditions use DateTime.Today or DateTime.UtcNow for the date — Env is not supplied there.

Related records

Follow a lookup with a dot: Account.Region, Order.Account.CreditLimit. Use the relationship's name, not the id field (Account.Region, not AccountId.Region). A path can be up to five hops long, and a missing related record reads as empty. Formulas read through relationships but never write through them — a value formula always writes a field on the record itself.

Lookups work in calculated fields, validations and business rules. A Default Value Formula cannot read related records: the path reads as empty when the default is applied. Use a field mapping to copy a value from a looked-up record at creation.

Child records (aggregates)

An aggregate reads the record's own child collection, by the relationship's name. Two spellings are accepted:

The expression inside the aggregate reads the child's own fields and can follow the child's lookups (l.Item.ItemType == "Service"). Aggregates work in calculated fields, validations and business rules, and can sit inside a larger formula (SUM(OrderLines, Amount) > 10000). They do not work in default value formulas. Only the record's own collections can be aggregated, not a collection reached through a lookup.

An empty collection gives 0 for a sum or count and false for Any. A calculated field that aggregates child records is recomputed when the parent is saved, and also when a child is added, changed, deleted or moved to another parent.

Prior values: PRIORVALUE

PRIORVALUE(Field) is the value the field had before the current save. Use it in validations and business rules to react to a change:

• Status != PRIORVALUE(Status) — the status was changed.
• PRIORVALUE(CreditLimit) != null && CreditLimit < PRIORVALUE(CreditLimit) — the credit limit was lowered.
• PRIORVALUE(Status) == "Closed" && Status != "Closed" — a closed record is being reopened.

On a new record there is no prior value, so PRIORVALUE reads as empty (a yes/no field reads as false). The Formula Builder offers these patterns under Old values (before this edit). A business rule's Only when the condition becomes true option uses the same mechanism: it runs the rule only when the condition was false before the save and is true after it.

Query filter reference

Query filters use the same comparison style, written against the fields of the entity being listed:

• Comparisons and logic: ==, !=, >, >=, <, <=, &&, ||, !. The words AND, OR and NOT are accepted too.
• Text: Name.Contains("North"), StartsWith, EndsWith, ToLower(), ToUpper(), Trim(), string.IsNullOrEmpty(Code).
• Dates: DueDate < DateTime.Today, CreatedDate >= DateTime.Today.AddDays(-30), .Year, .Month, .Day.
• Sets: Status in ("Open", "On Hold").
• Related records: one relationship away, such as VisitPlan.AssignedToResourceId. A child collection of the entity can be tested with Any, All or Count, as in OrderLines.Any(Quantity > 10).
• Null checks: ClosedDate == null.

A filter is limited in length and may only name fields and functions xMatix allows. Anything else is refused with "Unknown identifier '…' in filter." when the list or picker runs. The PRIORVALUE function, SUM-style aggregates and the Env and Org variables are formula features and do not work in query filters.

Lookup filter tokens

A lookup filter can compare the candidates with the record being edited and with the user. The Filter Builder writes these tokens for you:

A user who has none of something (no business unit, say) gets a value that matches no record, so the picker is empty rather than unfiltered. A __target path deeper than one hop, or a field the form does not carry, resolves to empty and usually matches nothing.

Example

An administrator builds a custom Purchase Request entity. It has a Customer lookup to Account, a Status picklist (stored values Draft, Submitted, Approved, Closed) and a child entity Request Line with Quantity and Unit Price. The relationship from the request to its lines is named RequestLines.

1. Calculated field. A Calculated field Request Total (RequestTotal_c, return type Currency) with Calculated Formula SUM(RequestLines, Quantity_c * UnitPrice_c). Adding a line of 4 × 2,500 to a request that already totals 15,000 updates the request to 25,000 as soon as the line is saved.
2. Default. On the custom date field Follow Up On, Default Value Formula Env.UtcNow.AddDays(7). A request created on 3 March gets 10 March.
3. Validation. Condition PRIORVALUE(Status_c) == "Approved" && Status_c == "Draft", message "An approved request cannot go back to draft." Moving an approved request back to Draft is refused.
4. Business rule. Condition SUM(RequestLines, Quantity_c * UnitPrice_c) > 500000 && !User.Roles.Contains("Purchase Manager") with a Block Save action, so only purchase managers can save very large requests.
5. Lookup filter. On the Customer lookup, Filter Condition IsActive == true, so the picker leaves out inactive accounts.
6. View. Open requests, Filter Condition Status_c in ("Draft", "Submitted"), which users narrow further from the list toolbar.

Training

Practice exercise

In a sandbox, on a custom entity with a number field, a picklist field and a child entity:

1. Add a Calculated field whose Calculated Formula counts the child records. Expected: the value follows as you add and delete child records.
2. Add a validation with Condition Priority_c == High (no quotes) and choose Apply. Expected: the Formula Builder shows a Validation error. Fix it to Priority_c == "High".
3. Add a validation with Condition PRIORVALUE(Amount_c) != null && Amount_c < PRIORVALUE(Amount_c) and message "Amount cannot be lowered." Expected: lowering the amount on an existing record is refused; creating a record with any amount is not.
4. Add a lookup filter whose condition names a field that does not exist, and choose Validate on the Advanced tab. Expected: an "Unknown identifier" message.

Quick reference

• Formulas decide about one record; query filters choose records.
• Quote text: "Draft". A bare word is a field name.
• Compare picklists by stored value, never by label.
• Lookups: Account.Region, up to five hops; not in default formulas.
• Aggregates: SUM(Lines, Amount), COUNT(Lines), Lines.Any(l => …); own collections only.
• PRIORVALUE(Field) reads the value before the save; empty on a new record.
• A validation condition is true when the record is wrong.
• A condition that cannot be evaluated counts as false — always test.
• Lookup filters reach the form through __target.Field and the user through User..

Permissions

• Fields, validations, views and lookup filters: setup.metadata.model.manage. Without it, Entities does not appear under Design Studio.
• Business rules: setup.automation.businessrules.manage.
• Expressions run with the rights of the save or query they belong to. A view filter or lookup filter can only narrow what the user may already see; it never reveals records the user's access does not allow.

Configuration

• Field formulas: Setup → Design Studio → Entities → entity → Schema → Fields → field → Formulas & Defaults. A picklist field with no Default Value Formula uses the picklist value marked as default. A formula, when present, wins.
• Validation conditions: Logic → Validations.
• View filters: Experience → Views → Filter Condition.
• Lookup filters: Schema → Lookup Filters → Open Filter Builder.
• Business-rule conditions and value formulas: Setup → Process Studio → Business Rules → rule → Condition and Actions.
• Changing any of these takes effect on the next save or query. Defaults and validations never touch records already saved. A calculated field that aggregates child records keeps its stored value on existing records until the record, or one of its child records, is saved again.

Common problems

"The formula does not compile for …" when I save a field

The Calculated Formula or Default Value Formula does not fit the field. Usual causes are an unknown field name, text where the field holds a number ("0" into a decimal field), or a malformed expression. The message names the field and its type. Fix the formula and save again.

"ConditionFormula does not compile for …" when I save a rule

The rule's condition names a field the entity does not have, or does not produce true or false. Check the names on the Formula Builder's Fields tab.

My default value never appears

A default fills only an empty field on a new record — never on an edit. Empty text and an unset lookup count as empty. A number, date or yes/no field of a standard entity that always holds a value (such as 0) counts as filled, so a default on it never applies. A default formula that cannot be evaluated is skipped silently. Defaults also cannot read related records.

My validation or rule never fires

A condition that cannot be evaluated is treated as false and the save goes ahead. Common causes are a misspelled field, an unquoted text value, a picklist compared with its label, Env used in a validation, or a related record that is not set. For rules, open the Test Runner and read Diagnostics. For validations, save a record that should fail.

My aggregate field shows 0

The collection name is wrong (use the relationship name shown on the Fields tab), the inner expression names a field the child does not have, or the record has no children yet. A child row whose expression cannot be evaluated is left out of the total.

"Unknown identifier '…' in filter."

A view or lookup filter names a field the entity does not have, a path more than one relationship deep, or a function that is not allowed. Correct the name, or use Validate in the Filter Builder to find the part it rejects.

The lookup picker is empty

The filter matches nothing. Typical causes: a __target field that is empty on the form, a token that resolves to nothing, or a user with no business unit when the filter uses User.BusinessUnitIds. See Entity behavior.

Common questions

Why does an expression work in a view filter but fail as a formula?

They are different languages on different engines. A query filter is turned into a database query and accepts words such as AND and the in (...) set syntax. A formula is evaluated against one record and accepts PRIORVALUE, SUM(...)-style aggregates, Env and Org. Simple comparisons work in both, which is why copying between them often seems to work until it does not. Write each expression for the surface it lives on, and test it there.

How do I show the total of the lines on the header?

Add a Calculated field on the header with a Calculated Formula such as SUM(OrderLines, Quantity * UnitPrice). xMatix recomputes it when the header is saved and whenever a line is added, changed, deleted or moved, so the total stays current without a business rule. If you need the total only to decide something — block a save, require a field — put the aggregate straight into the validation or rule condition instead. Validations and rules run before the aggregated field is recomputed in the same save.

Can a condition see what the value was before?

Yes. PRIORVALUE(Field) returns the value the field had before the current save, so Status != PRIORVALUE(Status) detects a change and PRIORVALUE(Status) == "Closed" && Status != "Closed" detects a reopen. It works in validation conditions and business-rule conditions. On a new record it reads as empty. For a rule that should fire once, when its condition first becomes true, switch on Only when the condition becomes true instead of writing the before-and-after comparison yourself. For a status moving between two named values, the OnPhaseTransition trigger is another option.

Can a formula depend on who is saving?

Yes. User. tokens read the signed-in user's roles, profiles, teams and business units by name, for example User.Roles.Contains("Finance Approver"). Because they are resolved on the server, a block based on them cannot be bypassed from the form. Use names rather than ids where you can. Names survive copying configuration between environments, while ids differ. When a background process saves a record there is no signed-in user, and the conditions read as false.

Related topics

• Entity behavior
• Business rules
• Approval processes
• What runs when you save a record
• Your first automation script
• The data model