Formulas

Basics

Formulas are algorithms that derive their values from other fields, expressions, or values.
You can use formulas for:

Best Practices

Only use supported functions and operators in formulas.
Some formulas that work in FieldFX Back Office don’t work in FieldFX Mobile.
Don’t include commented out code in formulas.
Use the ROUND function on date/time values in formulas.

Date & Time Functions

Supported

Function Description

DATE

Returns a date value for specified year, month, and day values. Returns an error for invalid dates, such as February 29 in a non-leap year.

DATEVALUE

Returns a date value for a date/time or text expression.

DATETIMEVALUE

Returns a year, month, day, and GMT time value.

DAY

Returns a day of the month in number form, such as "5" for Jan 5.

MONTH

Returns a month in number form, such as "1" for January or "12" for December.

NOW

Returns the current date/time.

Use NOW instead of TODAY in the formulas for date/time fields.

TIME

Returns a timestamp with millesecond precision but without a date.

TIMENOW

Returns a time value in GMT representing the current moment. Use this function instead of the NOW function if you only want to track time without a date.

TIMEVALUE

Returns the local time value without the date, such as business hours. Based on the organization’s locale settings.

TODAY

Returns the current date.

Use TODAY instead of NOW in the formulas for date/time fields.

YEAR

Returns a year as a four-digit number, such as "2019".

Not Supported

SECOND
MILLISECOND

Informational Functions

Supported

Function Description

BLANKVALUE

Returns a substitute expression if an expression doesn’t have a value (including a null value). Returns the value of an expression if the expression has a value.

ISBLANK

Returns TRUE if an expression doesn’t have a value. Returns FALSE if an expression has a value.

ISNULL ^[1]

Returns TRUE if an expression has a null (blank) value. Returns FALSE if an expression has a value.

Use ISBLANK instead of ISNULL in new formulas. ISBLANK has the same functionality as ISNULL but also supports text fields.

NULLVALUE

Returns a substitute expression if an expression has a null (blank) value. Returns the value of an expression if the expression has a value.

Use BLANKVALUE instead of NULLVALUE in new formulas. BLANKVALUE has the same functionality as NULLVALUE but also supports text fields.

PRIORVALUE

Returns the previous value of a field. Returns the current value if there is no PRIORVALUE property.

Not Supported

None

Logical Functions

Supported

Function Description

Function	Description
`AND`	Returns `TRUE` if all values are true. Returns `FALSE` if one of more values are false. `
`CASE`	Checks an expression against a series of values. Returns the corresponding result if the expression is equal to a value. Returns the `else_result` if the expression isn’t equal to any values.
`IF`	Determines whether expressions are true or false. Returns a given value if `TRUE` and another if `FALSE`.
`ISCHANGED`	Compares the value of a field to the previous value. Returns `TRUE` if the values are different. Returns `FALSE` if the values are the same.
`ISNEW`	Returns `TRUE` if the formula is running during the creation of a new record. Returns `FALSE` if an existing record is being edited.
`ISNUMBER`	Returns `TRUE` if a text value is a number and `FALSE` if not.
`NOT`	Returns `FALSE` for true and `TRUE` for false.
`OR`	Returns `TRUE` if any expression is true. Returns `FALSE` if all expressions are false.

AND

Returns TRUE if all values are true. Returns FALSE if one of more values are false. `

CASE

Checks an expression against a series of values. Returns the corresponding result if the expression is equal to a value. Returns the else_result if the expression isn’t equal to any values.

IF

Determines whether expressions are true or false. Returns a given value if TRUE and another if FALSE.

ISCHANGED

Compares the value of a field to the previous value. Returns TRUE if the values are different. Returns FALSE if the values are the same.

ISNEW

Returns TRUE if the formula is running during the creation of a new record. Returns FALSE if an existing record is being edited.

ISNUMBER

Returns TRUE if a text value is a number and FALSE if not.

NOT

Returns FALSE for true and TRUE for false.

OR

Returns TRUE if any expression is true. Returns FALSE if all expressions are false.

Not Supported

None

Math Functions

Supported

Function Description

ABS

Returns the absolute value of a number.

The absolute value of a number is the number without its positive or negative sign.

CEILING

Returns a number rounded up to the nearest integer.

EXP

Returns a value for e raised to the power of a specified number.

FLOOR

Returns a number rounded down to the nearest integer.

LN

Returns the natural logarithm of a specified number.

Natural logarithms are based on the constant e value of 2.71828182845904.

LOG

Returns the base 10 logarithm of a number.

MAX

Returns the highest number from a list of numbers.

MIN

Returns the lowest number from a list of numbers.

MOD

Returns the remainder after a number is divided by a specified divisor.

ROUND

Returns the nearest number to a specified number, constraining the new number by a specified number of digits.

SQRT

Returns the positive square root of a specified number.

Not Supported

DISTANCE
GEOLOCATION

Text Functions

Supported

Function Description

Function	Description
`BEGINS`	Returns `TRUE` if text begins with specified characters and returns `FALSE` if not.
`BR`	Inserts a line break in a string of text.
`CASESAFEID`	Converts a 15-character ID to a case-insensitive 18-character ID.
`CONTAINS`	Compares two arguments of text. Returns `TRUE` if the first argument contains the second argument and returns `FALSE` if not.
`FIND`	Returns the position of a string within a string of text, represented as a number.
`INCLUDES`	Determines if any value selected in a multi-select picklist equals a text literal you specify.
`ISPICKVAL`	Determines if the value in a picklist equals a text literal you specify.
`LEFT`	Returns the specified number of characters from the beginning of a text string.
`LEN`	Returns the number of characters in a text string.
`LOWER`	Converts all letters in a text string to lowercase.
`LPAD`	Inserts characters you specify to the left side of a text string.
`MID`	Returns the specified number of characters from the middle of a text string given the starting position.
`RIGHT`	Returns the specified number of characters from the end of a text string.
`RPAD`	Inserts characters you specify to the right side of a text string.
`SUBSTITUTE`	Substitutes new text for old text in a text string.
`TEXT`	Converts a currency, date, date/time, number, percentage, or picklist value into text. Also converts picklist values in approval rules, approval step rules, workflow rules, escalation rules, assignment rules, auto-response rules, validation rules, formula fields, field updates, and custom buttons and links.
`TRIM`	Removes the spaces and tabs from the beginning and end of a text string.
`UPPER`	Converts all letters in a text string to uppercase.
`VALUE`	Converts a text string to a number.

BEGINS

Returns TRUE if text begins with specified characters and returns FALSE if not.

BR

Inserts a line break in a string of text.

CASESAFEID

Converts a 15-character ID to a case-insensitive 18-character ID.

CONTAINS

Compares two arguments of text. Returns TRUE if the first argument contains the second argument and returns FALSE if not.

FIND

Returns the position of a string within a string of text, represented as a number.

INCLUDES

Determines if any value selected in a multi-select picklist equals a text literal you specify.

ISPICKVAL

Determines if the value in a picklist equals a text literal you specify.

LEFT

Returns the specified number of characters from the beginning of a text string.

LEN

Returns the number of characters in a text string.

LOWER

Converts all letters in a text string to lowercase.

LPAD

Inserts characters you specify to the left side of a text string.

MID

Returns the specified number of characters from the middle of a text string given the starting position.

RIGHT

Returns the specified number of characters from the end of a text string.

RPAD

Inserts characters you specify to the right side of a text string.

SUBSTITUTE

Substitutes new text for old text in a text string.

TEXT

Converts a currency, date, date/time, number, percentage, or picklist value into text. Also converts picklist values in approval rules, approval step rules, workflow rules, escalation rules, assignment rules, auto-response rules, validation rules, formula fields, field updates, and custom buttons and links.

TRIM

Removes the spaces and tabs from the beginning and end of a text string.

UPPER

Converts all letters in a text string to uppercase.

VALUE

Converts a text string to a number.

Not Supported

GETSESSIONID
HYPERLINK
IMAGE

Summary Functions

Supported

None

Not Supported

PARENTGROUPVAL
PREVGROUPVAL

Advanced Functions

Supported

Function Description

REGEX

Compares a text field to a regular expression. Returns TRUE if there is a match and FALSE if not.

A regular expression is a string that describes the format of a string according to certain syntax rules.

Not Supported

GETRECORDIDS
INCLUDE
LINKTO
REQUIRESCRIPT
URLFOR
VLOOKUP

Encoding Functions

Supported

None

Not Supported

HTMLENCODE
JSENCODE
JSINHTMLENCODE
URLENCODE

Math Operators

Supported

Operator Description

Operator	Description
`+`	Calculates the sum of specified values.
`–`	Calculates the difference of specified values.
`*`	Multiplies the specified values.
`/`	Divides the specified values.
`^`	Raises a number to the power of a specified number.
`( )`	Specifies that expressions inside the parentheses are evaluated first. All other expressions are evaluated using standard operator precedence.

+

Calculates the sum of specified values.

–

Calculates the difference of specified values.

*

Multiplies the specified values.

/

Divides the specified values.

^

Raises a number to the power of a specified number.

( )

Specifies that expressions inside the parentheses are evaluated first. All other expressions are evaluated using standard operator precedence.

Not Supported

None

Logical Operators

Supported

Operator Description

Operator	Description
`=` and `==`	Evaluates if two values are equal. The = and == operator are interchangeable.
`<>` and `!=`	Evaluates if two values aren’t equal.
`<`	Evaluates if a value is less than the value that follows this symbol.
`>`	Evaluates if a value is greater than the value that follows this symbol.
`<=`	Evaluates if a value is less than or equal to the value that follows this symbol.
`>=`	Evaluates if a value is greater than or equal to the value that follows this symbol.
`&&`	Evaluates if two values or expressions are both true. Use this operator as an alternative to the `AND` function.
`\|\|`	Evaluates if at least one of multiple values or expressions is true. Use this operator as an alternative to the `OR` function.
`!`	Logical complement operator. Inverts the value of a Boolean, so that true becomes false, and false becomes true.

= and ==

Evaluates if two values are equal. The = and == operator are interchangeable.

<> and !=

Evaluates if two values aren’t equal.

<

Evaluates if a value is less than the value that follows this symbol.

>

Evaluates if a value is greater than the value that follows this symbol.

<=

Evaluates if a value is less than or equal to the value that follows this symbol.

>=

Evaluates if a value is greater than or equal to the value that follows this symbol.

&&

Evaluates if two values or expressions are both true. Use this operator as an alternative to the AND function.

||

Evaluates if at least one of multiple values or expressions is true. Use this operator as an alternative to the OR function.

!

Logical complement operator. Inverts the value of a Boolean, so that true becomes false, and false becomes true.

Not Supported

None

Text Operators

Supported

Operator	Description
`&`	Connects two or more strings.

Not Supported

None

1. Strings are never null and never return TRUE for the ISNULL function.