Expression operators and functions

LandTracker expressions calculate values in asset templates and evaluate action-definition preconditions. Expressions can reference one or more fields and can combine operators and functions to represent business rules.

Where expressions are used

  • Calculated columns: Enter an expression in a CalculationExpression endnote on an asset-template field.
  • Action preconditions: Enter an expression in the Precondition row of an action definition.

Field references and values

  • Reference a field by its exact, case-sensitive name in braces; for example, {expiryDate}.
  • Use single or double quotation marks for text values; for example, 'Live' or "Live".
  • Use commas to separate function arguments.
  • Use parentheses to group parts of an expression.

Operators

  • Arithmetic: +, -, *, /, % and ^.
  • Comparison: =, !=, <, <=, > and >=.
  • Logical: AND, OR and NOT.

Functions

Conditional

  • If(condition, trueValue, falseValue): Returns one of two values. Conditions can be nested.

Numeric

  • Abs(number), Round(number, decimals), Min(number1, number2, ...) and Max(number1, number2, ...).

String

  • Len(string), Left(string, count), Right(string, count) and Mid(string, start, length).
  • Lower(string), Upper(string), Trim(string) and Concat(string1, string2, ...).

Membership

  • In(value, value1, value2, ...): Returns whether a value matches one of the supplied values.

Date

  • Today() and Now().
  • Year(date), Month(date) and Day(date).
  • AddDays(date, days), AddMonths(date, months) and AddYears(date, years).
  • DateDiff(date1, date2): Returns the difference between two dates in days.

Null handling and type conversion

  • IsNull(value): Returns whether a value is null.
  • Coalesce(value1, value2, ...): Returns the first non-null value.
  • ToNumber(value), ToString(value), ToDate(value) and ToDateTime(value).

Operator precedence

LandTracker evaluates expressions in the following order:

  1. Parentheses
  2. Exponentiation
  3. Multiplication, division and modulo
  4. Addition and subtraction
  5. Comparison
  6. AND
  7. OR

Examples

Calculated columns

Calculated-column expressions return the value that LandTracker stores in the column. For example:

  • AddDays({expiryDate}, -180): Calculates a notification date 180 days before expiry.
  • DateDiff(Today(), {expiryDate}): Calculates the number of days until expiry.
  • Concat({holders}, ' - ', {locality}, ' (', {identifier}, ')'): Combines holder, locality and tenement identifier details.
  • If({status} = 'Expired', 'Expired - Investigate', If(DateDiff(Today(), {expiryDate}) < 90, 'Renewal Urgent', 'Current')): Displays a renewal status based on the current status and expiry date.
  • Round(ToNumber({nextMTORent}) / ToNumber({approxAreaHa}), 2): Calculates rent per hectare to two decimal places.
  • If(IsNull({nextMTOCommitment}), 'Not Specified', ToString({nextMTOCommitment})): Displays alternative text when the next commitment is null.

Action preconditions

Precondition expressions must evaluate to true for LandTracker to create the action. For example:

  • {status} = 'Live': Creates the action only for live assets.
  • {status} = 'Live' AND DateDiff(Today(), {expiryDate}) <= 180 AND DateDiff(Today(), {expiryDate}) > 0: Creates the action for live assets that expire within 180 days.
  • In({tenementType}, 'Exploration Licence', 'Mining Licence'): Creates the action for either of the specified tenement types.
  • Abs(ToNumber({nextMTORent}) - ToNumber({lastMTORent})) > 2000: Creates the action when the rent changes by more than 2,000.
  • NOT IsNull({nextMTOCommitment}) AND ToNumber({nextMTOCommitment}) > 100000: Creates the action when the next commitment is present and exceeds 100,000.

Null values

Arithmetic, comparison and most function expressions return null when an operand is null. Use IsNull, Coalesce or If when an expression must handle a null value explicitly. OR returns true when either operand is true, even if the other operand is null.