How to write expressions for form conditions and rules

You will learn

How to reference fields in a NextTables form expression, which operators and functions are available, and how to test an expression before you rely on it.

This article is a reference for data platform owners and application builders who configure form conditions and validation rules.

Prerequisites

• A table whose form you can edit, and the steps in How to set up a data entry form for a view.
• The Name of each field you want to reference. Names appear in the property panel under General.

Where expressions are used

See How to show, hide, and require form fields with conditions for the conditions, and How to validate form entries with validation rules for the expression rule type.

1) Find the field names you need

• Open the table and select Edit form.
• Select a field and read Name under General.
• Use the name, not the label. Renaming a label leaves the name unchanged, so a condition written against a label fails silently.

2) Write the expression

• Wrap each field reference in braces: {Booking Type}.
• Quote text values with single quotes: 'Internal'.
• Write numbers and true or false without quotes: 1000, true.
• Group with parentheses whenever and meets or.

3) Test both outcomes

• Save the form.
• Open Add row and enter values that make the expression true.
• Enter values that make it false.
• Confirm both. An expression that is always true and an expression that is always false both look like a working configuration from one direction.

Referencing values

Comparison operators

The word forms and the symbol forms behave identically. Pick one and keep to it across a form, so the next person reading it has one thing to learn.

Logic

Parenthesize as soon as an expression mixes and with or. These two read alike and behave differently:

{Type} = 'A' or {Type} = 'B' and {Amount} > 1000 ({Type} = 'A' or {Type} = 'B') and {Amount} > 1000 

Arithmetic

Functions

Conditional

Nest iif for a rule that depends on a value rather than on a yes or no:

iif({Booking Type} = 'Internal', {Cost Center} notempty, true) 

Dates and times

Units for dateDiff and dateAdd: seconds, minutes, hours, days, months, years.

Arithmetic across fields

These take a list of fields on the same row. Each argument is one value.

What an expression cannot reach

A NextTables form holds one value per field for one row, so anything that needs a second row or a repeating group has nothing to work with.

General-purpose survey documentation lists aggregation functions ending in InArray, and placeholders for matrix rows and repeating panels. A NextTables form has none of those structures, so those functions and placeholders have nothing to read.

Worked examples

Copy these and replace the field names with your own.

Require a field when another has a specific value

{Booking Type} = 'Internal' 

Require a field when another has any of several values

{Region} anyof ['DACH', 'Iberia'] 

Show a field only when another is filled in

{Approver} notempty 

Keep an end date on or after its start date

{End Date} >= {Start Date} 

Keep a period within one year

dateDiff({Start Date}, {End Date}, 'days') <= 365 

Reject a date in the past

{Delivery Date} >= today() 

Restrict a date to working days

weekday({Delivery Date}) anyof [1, 2, 3, 4, 5] 

Keep a total consistent with its parts

sum({Q1}, {Q2}, {Q3}, {Q4}) = {Total Year} 

Keep an amount inside a budget on the same row

{Amount} <= {Budget} 

Warn above a threshold without blocking

Write the expression as a validation rule and set its message type to Warning:

{Amount} <= 50000 

Require one of two fields

{Email} notempty or {Phone} notempty 

Apply a rule only to one category

iif({Category} = 'Capex', {Asset Number} notempty, true) 

Troubleshooting / FAQs

• Q: My expression never becomes true. What should I check first?
  A: The field name. Compare what is inside the braces against Name under General, character for character. A label you edited does not change the name.
• Q: I compare a dropdown against the text I see, and nothing matches.
  A: A field bound to master data stores the key and displays the description. Compare against the key.
• Q: My combination of and and or behaves unexpectedly.
  A: Add parentheses around each or group. Precedence rules are easy to misread, and explicit grouping removes the question.
• Q: An expression on an empty field behaves oddly.
  A: Test for emptiness first. {Amount} > 1000 on an empty field is not a comparison worth relying on, whereas {Amount} notempty and {Amount} > 1000 states what you mean.
• Q: How do I compare two dates that include a time?
  A: Compare them directly with >= and <=, or reduce them to the part you care about with year, month, or day. Use dateDiff when the distance between them is what matters.
• Q: Can an expression change a value?
  A: A default value expression and a set value expression produce a value, and both apply in the form dialog. Conditions and validation rules read values and never write them. See How to show, hide, and require form fields with conditions.
• Q: Where do I write a regular expression?
  A: In a pattern validation rule, not in an expression. See How to validate form entries with validation rules.
• Q: Does an expression run on file imports and API writes?
  A: Visibility, conditional required, enablement, and expression validation rules run on every channel. Default value, set value, and reset value apply in the form dialog.