Formulas

Formulas are algorithms that derive their values from other fields, expressions, or values.

This topic lists the following functions that can be used:

Table 1. Function List

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

Function Description Supported Not Supported

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.

Green Check

DATEVALUE

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

Green Check

DATETIMEVALUE

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

Green Check

DAY

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

Green Check

MILLISECOND

Green Check

MONTH

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

Green Check

NOW

Returns the current date/time.

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

Green Check

SECOND

Green Check

TIME

Returns a timestamp with millisecond precision but without a date.

Green Check

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.

Green Check

TIMEVALUE

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

Green Check

TODAY

Returns the current date.

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

Green Check

YEAR

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

Green Check

Function

Description

Supported

Not Supported

Informational Functions

Function Description Supported Not Supported

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.

Green Check

ISBLANK

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

Green Check

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

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.

Green Check

None

Green Check

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.

Green Check

PRIORVALUE

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

Green Check

Function

Description

Supported

Not Supported

Logical Functions

Function Description Supported Not Supported

AND

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

Green Check

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.

Green Check

IF

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

Green Check

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.

Green Check

ISNEW

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

Green Check

ISNUMBER

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

Green Check

None

Green Check

NOT

Returns FALSE for true and TRUE for false.

Green Check

OR

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

Green Check

Function

Description

Supported

Not Supported

Math Functions

Function Description Supported Not Supported

ABS

Returns the absolute value of a number.

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

Green Check

CEILING

Returns a number rounded up to the nearest integer.

Green Check

DISTANCE

Green Check

EXP

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

Green Check

FLOOR

Returns a number rounded down to the nearest integer.

Green Check

GEOLOCATION

Green Check

LN

Returns the natural logarithm of a specified number.

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

Green Check

LOG

Returns the base 10 logarithm of a number.

Green Check

MAX

Returns the highest number from a list of numbers.

Green Check

MIN

Returns the lowest number from a list of numbers.

Green Check

MOD

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

Green Check

ROUND

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

Green Check

SQRT

Returns the positive square root of a specified number.

Green Check

Function

Description

Supported

Not Supported

Text Functions

Function Description Supported Not Supported

BEGINS

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

Green Check

BR

Inserts a line break in a string of text.

Green Check

CASESAFEID

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

Green Check

CONTAINS

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

Green Check

FIND

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

Green Check

GETSESSIONID

Green Check

HYPERLINK

Green Check

IMAGE

Green Check

INCLUDES

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

Green Check

ISPICKVAL

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

Green Check

LEFT

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

Green Check

LEN

Returns the number of characters in a text string.

Green Check

LOWER

Converts all letters in a text string to lowercase.

Green Check

LPAD

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

Green Check

MID

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

Green Check

RIGHT

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

Green Check

RPAD

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

Green Check

SUBSTITUTE

Substitutes new text for old text in a text string.

Green Check

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.

Green Check

TRIM

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

Green Check

UPPER

Converts all letters in a text string to uppercase.

Green Check

VALUE

Converts a text string to a number.

Green Check

Function

Description

Supported

Not Supported

Summary Functions

Function Description Supported Not Supported

None

Green Check

PARENTGROUPVAL

Green Check

PREVGROUPVAL

Green Check

Advanced Functions

Function Description Supported Not Supported

GETRECORDIDS

Green Check

INCLUDE

Green Check

LINKTO

Green Check

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.

Green Check

REQUIRESCRIPT

Green Check

URLFOR

Green Check

VLOOKUP

Green Check

Encoding Functions

Function Description Supported Not Supported

HTMLENCODE

Green Check

JSENCODE

Green Check

JSINHTMLENCODE

Green Check

None

Green Check

URLENCODE

Green Check

Math Operators

Operator Description Supported Not Supported

+

Calculates the sum of specified values.

Green Check

Calculates the difference of specified values.

Green Check

*

Multiplies the specified values.

Green Check

/

Divides the specified values.

Green Check

^

Raises a number to the power of a specified number.

Green Check

( )

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

Green Check

None

Green Check

Logical Operators

Operator Description Supported Not Supported

= and ==

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

Green Check

<> and !=

Evaluates if two values aren’t equal.

Green Check

<

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

Green Check

>

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

Green Check

<=

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

Green Check

>=

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

Green Check

&&

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

Green Check

||

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

Green Check

!

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

Green Check

None

Green Check

Text Operators

Operator Description Supported Not Supported

&

Connects two or more strings.

Green Check

None