Expressions
Expression recipes
Ready-made expressions for common needs. Change the field names to yours: the helper will
point out any that don't exist.
| Need |
Expression |
| Full name |
TRIM([First name] & " " & [Last name]) |
| Line total |
[Price] * [Quantity] |
| Total with tax |
ROUND([Subtotal] * (1 + [Tax rate]), 2) |
| Margin in percent |
IF([Price] = 0, 0, ([Price] - [Cost]) / [Price]) |
| Days until due |
DATEDIFF(TODAY(), [Due], "day") |
| Age in years |
DATEDIFF([Birthday], TODAY(), "year") |
| Overdue? (checkbox) |
AND(ISNOTBLANK([Due]), [Due] < TODAY(), [Status] <> "Done") |
| Grade from a score |
IFS([Score] >= 90, "A", [Score] >= 80, "B", [Score] >= 70, "C", TRUE, "D") |
| Priority number |
SWITCH([Priority], "High", 1, "Medium", 2, 3) |
| End of this month |
EOMONTH(TODAY(), 0) |
| Initials |
UPPER(LEFT([First name], 1) & LEFT([Last name], 1)) |
| A customer's total spend |
SUM(SELECT(Orders[Total], [Customer] = [_THISROW])) |
| A customer's open jobs |
COUNT(FILTER("Jobs", AND([Customer] = [_THISROW], [Status] <> "Done"))) |
| Price from a price list |
LOOKUP([Product code], "Products", "Code", "Price") |
| A formatted label |
[Number] & " · " & TEXT([Date], "DD/MM/YYYY") |
#Defaults
| Need |
Expression |
| Today |
TODAY() |
| Now |
NOW() |
| Due in two weeks |
TODAY() + 14 |
| The person adding the record |
USEREMAIL() (in a person or email field) |
| Copy from the linked record |
[Customer].[Region] |
#Validation
| Rule |
Expression |
Message |
| End after start |
[End] >= [Start] |
The end can't be before the start. |
| Not in the past |
[Date] >= TODAY() |
Pick today or a later date. |
| Six-character code |
LEN([Code]) = 6 |
The code has 6 characters. |
| Company email only |
ENDSWITH(LOWER([Email]), "@example.com") |
Use your work email. |
| Discount at most 30% |
[Discount] <= 0.3 |
Discounts above 30% need a manager. |
| Managers may give more |
OR([Discount] <= 0.3, HASROLE("Manager")) |
Discounts above 30% need a manager. |
| Reason when lost |
OR([Status] <> "Lost", ISNOTBLANK([Reason])) |
Say why it was lost. |
#Show if
| Show the field when |
Expression |
| a choice is picked |
[Type] = "Business" |
| a box is ticked |
[Needs delivery] |
| one of several choices |
IN([Status], {"Lost", "Cancelled"}) |
| for some roles only |
HASROLE("Manager") |
#View filters
| Show |
Expression |
| Open records |
[Status] <> "Done" |
| Mine |
[Assigned to] = USEREMAIL() |
| Due this week |
AND([Due] >= TODAY(), [Due] <= TODAY() + 7) |
| Added this month |
AND(YEAR([Created at]) = YEAR(TODAY()), MONTH([Created at]) = MONTH(TODAY())) |
| Big orders |
[Total] >= 1000 |
| Has a tag |
IN("Urgent", [Tags]) |
| Name contains |
CONTAINS([Name], "smith") |
#Security
| Rule |
Where |
Expression |
| One region, unless dispatcher |
row filter |
OR([Region] = "North", HASROLE("Dispatcher")) |
| Only open records can be edited |
edit → only if |
[Status] <> "Closed" |
| Only the creator can delete |
delete → only if |
[Created by] = USEREMAIL() |
| Anyone with a role |
row filter |
COUNT(USERROLES()) > 0 |
See Roles and security.
#Automations
| Need |
Where |
Expression |
| Only big orders |
trigger only if |
[Total] > 1000 |
| Only when someone is assigned |
trigger only if |
ISNOTBLANK([Assigned to]) |
| Stop if there's no email |
Continue only if |
ISNOTBLANK([Customer].[Email]) |
| Count down stock |
Change records value |
[Stock] - 1 |
| A subject line |
template |
Order {{[Number]}} is {{LOWER([Status])}} |
| Data a webhook sent |
template |
{{INPUT("customer.email")}} |
