Expressions
Boolean operators, field references, functions, table functions, and special variables for P9 calculations and conditions.
Expression Language
Expressions are used in calculated fields, workflow conditions, filters, triggers, and visibility rules throughout P9.
Field References
Reference field values using curly braces:
{FieldName} // Value of a field on the current record
{Lookup.FieldName} // Value from a related record
{Table.FieldName} // Value from a table detail row
Comparison Operators
| Operator | Description | Example |
|---|---|---|
== |
Equal | {Status} == "Active" |
!= |
Not equal | {Status} != "Closed" |
> |
Greater than | {Amount} > 1000 |
< |
Less than | {Amount} < 500 |
>= |
Greater or equal | {Score} >= 80 |
<= |
Less or equal | {DaysOpen} <= 30 |
in |
In list | {Status} in ("Active", "Pending") |
not in |
Not in list | {Type} not in ("Draft", "Cancelled") |
Boolean Operators
| Operator | Description | Example |
|---|---|---|
AND |
Both conditions true | {Amount} > 100 AND {Status} == "Approved" |
OR |
Either condition true | {Priority} == "High" OR {Priority} == "Critical" |
Combining with parentheses:
({Amount} > 5000 AND {Department} == "Finance") OR {ApprovedBy.Role} == "Director"
Boolean Expression Shortcuts
Simplified syntax for boolean field conditions:
| Syntax | Resolves To | Example |
|---|---|---|
[FieldName] |
FieldName == true |
show:[IsActive] |
[!FieldName] |
FieldName == false |
show:[!IsInactive] |
Examples:
field UtilityCharge type:Money show:[AddUtilityCharge]
field Discount type:Number show:[!ApplyDiscountIndividually]
field ApplicationRole type:Lookup show:[HasLoginPrivilege]
section show:[IsRenewingContract] {
...
}
Expression Functions
Text Functions
| Function | Description | Example |
|---|---|---|
COMBINE(a, b, ...) |
Join non-empty values with space | COMBINE({FirstName}, {MiddleName}, {LastName}) |
CONCAT(a, b, ...) |
Concatenate without separator | CONCAT({Code}, "-", {Number}) |
UPPER(text) |
Convert to uppercase | UPPER({Code}) |
LOWER(text) |
Convert to lowercase | LOWER({Email}) |
LEFT(text, n) |
First n characters | LEFT({Name}, 3) |
RIGHT(text, n) |
Last n characters | RIGHT({Phone}, 4) |
LEN(text) |
Length of text | LEN({Description}) |
COMBINE vs CONCAT:
COMBINEautomatically filters out empty/null values and joins with a single spaceCONCATdoes traditional concatenation without filtering
Logic Functions
| Function | Description | Example |
|---|---|---|
IF(cond, then, else) |
Conditional value | IF({Amount} > 1000, "High", "Low") |
ISBLANK(field) |
Check if empty | ISBLANK({Notes}) |
IFBLANK(field, default) |
Default if empty | IFBLANK({Phone}, "N/A") |
SWITCH(field, v1, r1, ...) |
Multi-value switch | SWITCH({Rating}, "A", 100, "B", 80, 0) |
Math Functions
| Function | Description | Example |
|---|---|---|
ROUND(n, decimals) |
Round number | ROUND({Total}, 2) |
ABS(n) |
Absolute value | ABS({Variance}) |
MAX(a, b) |
Maximum of values | MAX({Budget}, {Actual}) |
MIN(a, b) |
Minimum of values | MIN({Quota}, {Available}) |
SUM(...) |
Sum values | SUM({Item1}, {Item2}, {Item3}) |
AVG(...) |
Average values | AVG({Score1}, {Score2}) |
Date Functions
| Function | Description | Example |
|---|---|---|
TODAY |
Current date | {DueDate} < TODAY |
NOW |
Current date+time | {ExpiresAt} < NOW |
DATEADD(date, n, unit) |
Add to date | DATEADD({StartDate}, 12, "months") |
DATEDIFF(d1, d2, unit) |
Difference between dates | DATEDIFF({EndDate}, {StartDate}, "days") |
YEAR(date) |
Extract year | YEAR({CreatedAt}) |
MONTH(date) |
Extract month | MONTH({CreatedAt}) |
Table Functions
Operate on table detail (child) rows:
| Function | Description | Example |
|---|---|---|
ANY(Table.field) |
True if any row has field populated | show:[ANY(ItemDetail.approved)] |
ALL(Table.field) |
True if all rows have field populated | show:[ALL(ItemDetail.verified)] |
COUNT(Table.field) |
Count of rows with field populated | show:[COUNT(ItemDetail.amount) > 5] |
SUM(Table, field) |
Sum of field across rows | SUM(LineItems, {Total}) |
AVG(Table, field) |
Average of field | AVG(LineItems, {UnitPrice}) |
MIN(Table, field) |
Minimum value | MIN(LineItems, {Quantity}) |
MAX(Table, field) |
Maximum value | MAX(LineItems, {Total}) |
Negation:
All functions support negation with !:
| Expression | Meaning |
|---|---|
!ANY(Table.field) |
True if NO rows have the field populated |
!ALL(Table.field) |
True if NOT ALL rows have the field populated |
Use Cases:
// Prevent operations when linked to another object
field ItemDetail type:Table allow-new:[!ANY(ItemDetail.materialRequest)] allow-delete:[!ANY(ItemDetail.materialRequest)]
// Show button only when all items verified
field SubmitButton type:Button show:[ALL(ItemDetail.verified)]
// Show warning when items incomplete
field WarningMessage type:Text show:[!ALL(ItemDetail.completed)]
// Show total only when items exist
field Total type:Money show:[COUNT(ItemDetail.amount) > 0]
Special Variables
| Variable | Description |
|---|---|
{CurrentUser} |
Current logged-in user |
{CurrentUser.Department} |
User's department |
{CurrentUser.Role} |
User's role |
{Record.Id} |
Current record's ID |
{Record.CreatedBy} |
Who created the record |
{Record.CreatedAt} |
When the record was created |
TODAY |
Current date |
NOW |
Current date and time |
Dynamic Value Functions
Use bracket syntax for dynamic defaults and conditions:
| Expression | Description | Example |
|---|---|---|
[now()] |
Current date/time | default:[now()] |
[ageLimit()] |
Date 18 years ago | maximum:[ageLimit()] |
[User.EmployeeId] |
Current user's employee ID | default:[User.EmployeeId] |
[$CurrentModule] |
Current active module | show:[$CurrentModule = 'Leasing'] |
[$RecordId] |
Record ID (null for create) | show:[$RecordId != null] |
Module and Role Checks
Check company modules and user roles:
// Show field only if Leasing module is enabled
field TenantInfo type:Text show:['Leasing' in Company.Modules]
// Show field only for admin users
field AdminSettings type:Text show:['Admin' in User.Roles]
// Combine conditions
field SpecialField type:Text show:['Leasing' in Company.Modules AND 'Manager' in User.Roles]
Calculated Field Examples
// Full name from parts
field FullName type:Calculated label:"Full Name" expression:[COMBINE(FirstName, MiddleName, LastName)] readonly:true
// Total with tax
field TotalWithTax type:Calculated label:"Total (inc. Tax)" expression:"SubTotal + (SubTotal * TaxRate / 100)" readonly:true
// Remaining quantity
field RemainingQty type:Calculated label:"Remaining" expression:"Quantity - IF(ISBLANK(UsedQuantity), 0, UsedQuantity)" readonly:true
// Annual rent from monthly
field AnnualRent type:Calculated label:"Annual Rent" expression:"{MonthlyRent} * 12" readonly:true
// Days until due
field DaysUntilDue type:Calculated label:"Days Until Due" expression:"DATEDIFF({DueDate}, TODAY, 'days')" readonly:true
Conditional Visibility Examples
// Show based on select value
field CommercialTerms type:Text show:[{Type}='Commercial']
// Hide based on status
field EditableField type:Text hide:[{Status}='Approved']
// Show only in edit mode
section show:[$RecordId != null] {
title:"Status Information"
...
}
// Show only in create mode
section show:[$RecordId == null] {
title:"Initial Setup"
...
}
// Complex condition
field SpecialDiscount type:Money show:[{CustomerType}='VIP' AND {OrderTotal} > 10000]