Functions and operators

Use functions to transform data from records, perform calculations, or to provide values for Visualforce attributes.

Functions must be used in a Visualforce expression to be evaluated. You can use the following functions in your Visualforce pages.

Date and time functions

For date and time functions for use in Payflow only, see Date and time functions (Payflow only).

DATE

Returns a day of the month as a number from 1 to 31 for a given date with year, month, and day values. Salesforce displays an error on the detail page if the value of the DATE function in a formula field is an invalid date, such as February 29 in a non-leapyear.

Syntax

DATE(year,month,day)

Where:

• year is a four-digit year

• month is a two-digit month

• day is a two-digit day

DATEVALUE

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

Syntax

DATEVALUE(expression)

Where:

• expression is one of:

  • A date/time value

  • A text value

  • A merge field

  • An expression

DATETIMEVALUE

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

Syntax

DATETIMEVALUE(expression)

Where:

• expression is one of:

  • A date/time value

  • A text value

  • A merge field

  • An expression

DAY

Returns a day of the month in the form of a number between 1 and 31.

Syntax

DAY(date)

Where:

• date is a date field or value such as TODAY().

Example

To determine if a given date is the last day of a month:

IF (DAY(fHCM2__Start_Date__c+1)=1, True, False)

• Uses Start Date to provide the given date

• Adds 1 to the Start Date and assesses if the result is 1, to signal the first of the month.

  If date + 1 is the first, then date must be the last day of the previous month.

• Returns True if date is the last day of a month.

• Returns False if date is not the last day of a month.

MONTH

Returns the month, a number between 1 (January) and 12 (December) in number format of a given date.

Syntax

MONTH(date)

Where:

• date is the field or expression for the date containing the month you want returned.

NOW

Returns a date/time representing the current moment.

The NOW function returns the current date and time in the GMT timezone.

Syntax

NOW()

Example

Today's date and time is:

{!NOW()}

produces the following:

Today's date and time is: Mon Jul 21 16:12:10 GMT 2023

Tips:

• Do not remove the parentheses.

• Keep the parentheses empty. They do not need to contain a value.

  Use addition or subtraction operators and a number with a NOW function to return a different date and time. For example {!NOW() +5} calculates the date and time five days ahead of now.

• If you prefer to use a date time field, use TODAY

TODAY

Returns the current day as a date data type.

Syntax

TODAY()

Example

See an example of TODAY() in the examples for STARTPERIOD(): Examples

Tips: 

• Do not remove the parentheses.

• Keep the parentheses empty. They do not need to contain a value.

• Use addition and subtraction operators with a TODAY function and numbers to return a date.

  For example: {!TODAY() +7} calculates the date seven days ahead of now.

• If you prefer to use a date time field, use NOW.

YEAR

Returns the four-digit year in number format of a given date.

Syntax

YEAR(date)

Where:

• date is the field or expression that contains the year you want returned.

Example

To determine if a Team Member's start date falls in a leap year:

IF (OR (MOD(YEAR(fHCM2__Start_Date__c),400)=0, 
    AND(MOD(YEAR(fHCM2__Start_Date__c),4)=0,
    MOD(YEAR(fHCM2__Start_Date__c),100)!=0)), 
    True,False)

This formula: 

• Evaluates the YEAR of the Start Date to find if it is:

  • Divisible by 400 with no remainder: MOD(YEAR(fHCM2__Start_Date__c),400)=0

  • Or divisible by 4 with no remainder AND divisible by 100 with a remainder: AND(MOD(YEAR(fHCM2__Start_Date__c),4)=0,MOD(YEAR(fHCM2__Start_Date__c),100)!=0)

• Returns TRUE if either of the conditions is met, to signal that the Start Date falls in a leap year.

• Returns FALSE if both of the conditions are not met, to signal that the Start Date does not fall in a leap year.

Informational functions

BLANKVALUE

Determines if an expression is blank and returns a substitute expression if it is. If the expression has a value, returns the value of the expression.

Syntax

BLANKVALUE(expression, substitute_expression_if_blank)

Where:

• expression is the expression you want evaluated

• substitute_expression_if_blank is the value you want to replace any blank values.

ISBLANK

Determines if an expression has a value and returns TRUE if it does not. If it contains a value, this function returns FALSE.

Syntax

ISBLANK(expression)

Where:

• expression is the expression you want evaluated.

NULLVALUE

Determines if an expression is null (blank) and returns a substitute expression if it is. If the expression is not blank, returns the value of the expression.

NULLVALUE does not support text fields. Use BLANKVALUE in new formulas; BLANKVALUE has the same functionality as NULLVALUE but also supports text fields.

Syntax

NULLVALUE(expression, substitute_expression_if_null)

Where:

• expression is the expression you want to evaluate

• substitute_expression_if_null is the value you want to replace any blank values.

PRIORVALUE

Returns the previous value of a field.

Syntax

PRIORVALUE(field)

Logical functions

AND

Returns a TRUE response if all values are true; returns a FALSE response if one or more values are false.

Syntax

AND(logical1,logical2,...)

Where:

• logical1,logical2,... are the values you want evaluated.

Example

See an example of AND() in the example for YEAR.

CASE

Checks a given expression against a series of values. If the expression is equal to a value, returns the corresponding result. If it is not equal to any values, it returns the else_result.

Syntax

CASE(expression,value1, result1, value2, result2,...value_n, result_n, else_result)

Where:

• expression is the field or value you want compared to each specified value.

• value1 is the first value you want to compare with the expression

• result1 is the value you want returned if the expression is equivalent to value1

• value2 is the second value you want to compare with the expression

• result2 is the value you want returned if the expression is equivalent to value2

• value_n is the nth value you want to compare with the expression

• result_n is the value you want returned if the expression is equivalent to value_n

• else_result is the value you want returned when the expression does not equal any values.

Example

An org includes a custom field Total Weighted Score to hold the numeric value of the sum of Competency Score and Weighted Score. To return a different text value depending on the range in which a score falls, create a formula field containing:

CASE
(IF (Total_Weighted_Score__c > 112.99, " Top ",
IF (Total_Weighted_Score__c > 104.99, " High",
IF (Total_Weighted_Score__c > 99.99, " Effective ",
IF (Total_Weighted_Score__c > 77.99, " Improvement Needed
",
"Unsatisfactory") ) ) ) )

IF

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

Syntax

IF(logical_test, value_if_true, value_if_false)

Where:

• logical_test is the expression you want evaluated

• value_if_true is the value you want returned if the expression is true

• value_if_false is the value you want returned if the expression is false

Example

See an example of IF() in the example for YEAR.

ISCHANGED

Compares the value of a field to its previous value, and returns true if the values are different.

Syntax

ISCHANGED(field)

Where:

• field is the API name of the field you want to query

ISNEW

Checks if the formula is running during the creation of a new record and returns TRUE if it is. If an existing record is being updated, this function returns FALSE.

Syntax

ISNEW()

ISNUMBER

Determines if a text value is a number and returns TRUE if it is. Otherwise, it returns FALSE.

Syntax

ISNUMBER(text)

Where:

• text is the merge field name for the text field.

NOT

Evaluates an expression and returns FALSE if the expression is TRUE and TRUE if the expression is FALSE.

Syntax

NOT(expression)

Where:

• expression is the expression you want evaluated.

Example

Checks Social Security Numbers for valid syntax, for use as a validation rule. Assumes that the Social Security Number is not mandatory by permitting an empty field:

NOT (OR (LEN(fHCM2__Social_Security_Number__c)=0,
    REGEX (fHCM2__Social_Security_Number__c , 
    "[0-9]{3}-[0-9]{2}-[0-9]{4}") 
    ))

• NOT reverses the TRUE/FALSE value returned by the OR function, so that invalid data returns a TRUE value for the validation rule.

• OR returns TRUE if either of the following expressions is true:

  • Social Security Number is empty; LENgth is zero.

  • REGEX assesses Social Security Number to comprise 9 characters, split nnn-nn-nnnn

• Returns FALSE if Social Security Number is empty or contains a code matching the required nnn-nn-nnnn format.

• Returns TRUE if Social Security Number contains a non-zero length code that does not match the required nnn-nn-nnnn format.

OR

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

Syntax

OR(logical1, logical2,...logical_n)

Where:

• logical1, logical2,... logical_n are the expressions you want evaluated.

Example

See an example of OR() in the example for YEAR.

Math functions

ABS

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

Syntax

ABS(number)

Where:

• number is a merge field, expression, or other numeric value that has the sign you want removed.

CEILING

Rounds a number up to the nearest integer.

Syntax

CEILING(number)

Where:

• number is the field or expression you want rounded.

EXP

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

Syntax

EXP(number)

Where:

• number is a number field or numeric value

FLOOR

Returns a number rounded down to the nearest integer.

Syntax

FLOOR(number)

Where:

• number is with a number field or numeric value.

LN

Returns the natural logarithm of a specified number. Natural logarithms are based on the constant e value of 2.71828182845904.

Syntax

LN(number)

Where:

• number is the field or expression for which you want the natural logarithm.

LOG

Returns the base 10 logarithm of a number.

Syntax

LOG(number)

Where:

• number is the field or expression for which you want the base 10 logarithm .

MAX

Returns the highest number from a list of numbers.

Syntax

MAX(number1,number2,...number_n)

Where:

• number1, number2,...number_n are the fields or expressions you want evaluating to retrieve the highest number.

MIN

Returns the lowest number from a list of numbers.

Syntax

MIN(number1, number2,...number_n)

Where:

• number1, number2,...number_n are the fields or expressions you want evaluating to retrieve the lowest number.

MOD

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

Syntax

MOD(number, divisor)

Where:

• number is the field or expression you want divided

• divisor is the number to use as the divisor

Example

See an example of MOD() in the example for YEAR.

ROUND

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

Syntax

ROUND(number, num_digits)

Where:

• number is the field or expression you want rounded

• num_digits is the number of decimal places you want to use in the rounded result

SQRT

Returns the positive square root of a given number.

Syntax

SQRT(number)

Where:

• number is the field or expression you want computed into a square root.

Text functions

BEGINS

Determines if text begins with specific characters and returns TRUE if it does. Returns FALSE if it does not.

Case sensitive. Works with text only, not numeric or other data types.

Syntax

BEGINS(text, compare_text)

Where:

• text is the text string or field holding the text you want to assess

• compare_text is the text string or field holding the beginning characters you want to evaluate text against.

Example

The following markup returns true if the opportunity StageName field begins with the string "Closed". Standard stage names "Closed Won" and "Closed Lost" both return true.

{!BEGINS(opportunity.StageName,'Closed')}

BR

Inserts a line break in a string of text.

Syntax

BR()

CASESAFEID

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

Syntax

CASESAFEID(id)

Where:

• id is with the object’s ID.

CONTAINS

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

Case sensitive.

Syntax

CONTAINS(text, compare_text)

Where:

• text is the text string or field holding the text you want to assess

• compare_text is the text string or field holding the contained characters you want to evaluate text against

Example

F (ISPICKVAL(Reason_For_Leaving__c, "Involuntary")
|| CONTAINS(fHCM2__Team_Member__r.fHCM2__Job_Title__c, "Intern")
|| CONTAINS( fHCM2__Team_Member__r.fHCM2__Name_Full__c, "Test"),
"Yes", "No")

Evaluates:

• The selected Reason For Leaving picklist value, looking for the text string Involuntary.

• The Team Member Job Title value, looking for the text string Intern.

• The Team Member Full Name value, looking for the text string Test.

Returns:

• Yes if any of the values match the given strings.

• No if none of the values match the given strings.

FIND

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

Syntax

FIND(search_text, text[, start_num])

Where:

• search_text is the string you want to find

• text is the field or expression you want to search

• start_num is the position of the character from which to start searching from left to right

GETSESSIONID

Returns the user’s session ID.

Syntax

GETSESSIONID()

HTMLENCODE

Encodes text and merge field values for use in HTML by replacing characters that are reserved in HTML, such as the greater-than sign (>), with HTML entity equivalents, such as >.

Syntax

{!HTMLENCODE(text)}

Where:

• text is the merge field or text string that contains the reserved characters.

ISPICKVAL

Determines if the value of a picklist field is equal to a text literal you specify.

Syntax

ISPICKVAL(picklist_field, text_literal)

Where:

• picklist_field is the merge field name for the picklist

• text_literal is the picklist value in quotes. text_literal cannot be a merge field or the result of a function.

Example

See an example of ISPICKVAL() in the example for CONTAINS.

JSENCODE

Encodes text and merge field values for use in JavaScript by inserting escape characters, such as a backslash (\), before unsafe JavaScript characters, such as the apostrophe (').

Syntax

{!JSENCODE(text)}

Where:

• text is the merge field or text string that contains the unsafe JavaScript characters.

JSINHTMLENCODE

Encodes text and merge field values for use in JavaScript inside HTML tags by replacing characters that are reserved in HTML with HTML entity equivalents and inserting escape characters before unsafe JavaScript characters. JSINHTMLENCODE(someValue) is a convenience function that is equivalent to JSENCODE(HTMLENCODE((someValue)). That is, JSINHTMLENCODE first encodes someValue with HTMLENCODE, and then encodes the result with JSENCODE.

Syntax

{!JSINHTMLENCODE(text)}

Where:

• text is the merge field or text string that contains the unsafe JavaScript characters.

LEFT

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

Syntax

LEFT(text, num_chars)

Where:

• text is the field or expression you want returned

• num_chars with the number of characters from the left you want returned

Example

To return a Team Member's initials:

LEFT( fHCM2__First_Name__c,1) 
    + LEFT( fHCM2__Middle_Name__c ,1) 
    + LEFT( fHCM2__Surname__c, 1)

• Takes the left-most single character from each of the Team Member's name elements.

LEN

Returns the number of characters in a specified text string.

Syntax

LEN(text)

Where:

• text is the field or expression whose length you want returned

Example

See an example of LEN() in the example for NOT.

LOWER

Converts all letters in the specified text string to lowercase. Any characters that are not letters are unaffected by this function. Locale rules are applied if a locale is provided.

Syntax

LOWER(text, [locale])

Where:

• text is the field or text you wish to convert to lowercase

• locale is the optional two-character ISO language code or five-character locale code, if available

LPAD

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

Syntax

LPAD(text, padded_length[, pad_string])

Where:

• text is the field or expression you want to pad to the left

• padded_length is the total number of characters in the text to be returned

• pad_string is the character or characters to insert. pad_string is optional and defaults to a blank space.

If the value in text is longer than padded_length, text is truncated to the size of padded_length.

MID

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

Syntax

MID(text, start_num, num_chars)

Where:

• text is the field or expression to use when returning characters

• start_num is the number of characters from the left to use as a starting position

• num_chars is the total number of characters to return

RIGHT

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

Syntax

RIGHT(text, num_chars)

Where:

• text is the field or expression you want returned

• num_chars is the number of characters from the right you want returned

RPAD

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

Syntax

RPAD(text, padded_length[,'pad_string'])

Where:

• text is the field or expression after which you want to insert characters

• pad_length is the total number of characters in the text string to be returned

• pad_string is the character or characters that should be inserted. pad_string is optional and defaults to a blank space.

If the value in text is longer than padded_length, text is truncated to the size of padded_length.

SUBSTITUTE

Substitutes new text for old text in a text string.

Syntax

SUBSTITUTE(text, old_text, new_text)

Where:

• text is the field or value you want to change

• old_text is the text you want replaced

• new_text is the text you want to replace old_text

TEXT

Converts a percentage, number, date, date/time, or currency type field into text anywhere formulas are used. Also converts picklist values to text 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.

Syntax

TEXT(value)

Where:

• value is the field or expression you want to convert to text format.

Do not use special characters except:

• Decimal point (period)

• Minus sign (dash)

TRIM

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

Syntax

TRIM(text)

Where:

• text is the field or expression you want to trim

UPPER

Converts all letters in a specified text string to uppercase. Any characters that are not letters are unaffected by this function. Locale rules are applied if a locale is provided.

Syntax

UPPER(text, [locale])

Where:

• text is the field or expression to convert to uppercase

• locale is the optional two-character ISO language code or five-character locale code, if available

URLENCODE

Encodes text and merge field values for use in URLs by replacing characters that are illegal in URLs, such as blank spaces, with the code that represent those characters as defined in RFC 3986, Uniform Resource Identifier (URI): Generic Syntax. For example, blank spaces are replaced with %20, and exclamation points are replaced with %21.

Syntax

{!URLENCODE(text)}

Where:

• text is the merge field or text string that you want to encode

VALUE

Converts a text string to a number.

Syntax

VALUE(text)

Where:

• text is the field or expression to convert into a number.

Advanced functions

GETRECORDIDS

Returns an array of strings in the form of record IDs for the selected records in a list, such as a list view or related list.

Syntax

{!GETRECORDIDS(object_type)}

Where:

• object_type is a reference to the custom or standard object for the records you want to retrieve.

INCLUDE

Returns content from an s-control snippet. Use this function to reuse common code in many s-controls.

Syntax

{!INCLUDE(source, [inputs])}

Where:

• source is the s-control snippet you want to reference

• inputs is the information you need to pass to the snippet.

LINKTO

Returns a relative URL in the form of a link (href and anchor tags) for a custom s-control or Salesforce page.

Syntax

{!LINKTO(label, target, id, [inputs], [no override]}

Where:

• label is the text for the link

• target is the URL

• id is a reference to the record

• inputs are optional and can include any additional parameters you want to add to the link

• no override is optional and defaults to false. It applies to targets for standard Salesforce pages such as $Action.Account.New. Replace no override with true when you want to display a standard Salesforce page regardless of whether you have defined an override for it elsewhere.

REGEX

Compares a text field to a regular expression and returns TRUE if there is a match. Otherwise, it returns FALSE. A regular expression is a string used to describe a format of a string according to certain syntax rules.

Syntax

REGEX(text, regex_text)

Where:

• text is the text field

• regex_text is the regular expression you want to match

Example

See an example of REGEX() in the example for NOT.

REQUIRESCRIPT

Returns a script tag with source for a URL you specify. Use this function when referencing the Force.com AJAX Toolkit or other JavaScript toolkits.

Syntax

{!REQUIRESCRIPT(url)}

Where:

• url is the link for the script

URLFOR

Returns a relative URL for an action, s-control, Visualforce page, or a file in a static resource archive in a Visualforce page.

This can be used to return a reference to a file contained in a static resource archive, such as a .zip or .jar file.

Syntax

Option 1:

{!URLFOR(resource, path)}

Where:

• resource is the name of the static resource archive expressed as a merge variable. For example:

  $Resource.resourceName)

• path is the local path to the file in the archive you want to reference.

Option 2

{!URLFOR(target, id, [inputs], [no override])}

Where:

• target is the URL or action,s-control, or static resource merge variable

• id is a reference to the record

• inputs are optional and can include any additional parameters

• no override is optional and defaults to false. It applies to targets for standard Salesforce pages such as $Action.Account.New. Replace no override with true when you want to display a standard Salesforce page regardless of whether you have defined an override for it elsewhere.

To access a Visualforce page, enter the name of your page preceeded by an "apex/". For example, if your Visualforce page is named myTestPage, you would use {!URLFOR("apex/myTestPage"}.

VLOOKUP

Returns a value by looking up a related value on a custom object similar to the VLOOKUP() Excel function.

Syntax

VLOOKUP(field_to_return, field_on_lookup_object, lookup_value)

Where:

• field_to_return is the field that contains the value you want returned

• field_on_lookup_object is the field on the related object that contains the value you want to match

• lookup_value with the value you want to match.

You can only use VLOOKUP() in validation rules. If the function fails because, for example, the field_on_lookup_object does not exist, you can specify an error message in the validation rule itself.

Encoding functions

See HTMLENCODE, JSENCODE, JSINHTMLENCODE, and URLENCODE.

Expression operators

Use operators to join expressions together to create compound expressions.

Operators must be used within Visualforce expression syntax to be evaluated. Visualforce supports the following operators.

Math operators

Plus +

Calculates the sum of two values.

Syntax

value1 + value2

Where:

• value1, value2 are merge fields, expressions, or other numeric values.

Minus -

Calculates the difference between two values.

Syntax

value1 - value2

Where:

• value1, value2 are merge fields, expressions, or other numeric values.

Multiply *

Calculates the product of two values.

Syntax

value1 * value2

Where:

• value1, value2 are merge fields, expressions, or other numeric values.

Divide /

Calculates the result of dividing one value by another.

Syntax

value1 / value2

Where:

• value1, value2 are merge fields, expressions, or other numeric values

Power ^

Raises a number to a specified power.

Syntax

number^integer

Where:

• number is a merge field, expression, or numeric value

• integer is a merge field that contains an integer, expression, or any integer

Brackets ()

Specifies that the expression(s) within the brackets/braces is evaluated first. All other expressions are evaluated using standard operator precedence.

Syntax

(expression1) expression2...expression_n

Where:

• expression1, expression2,...expression_n are merge fields, expressions, or other numeric values.

Logical operators

Equals = and ==

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

Syntax

expression1=expression2

expression1==expression2

Where:

• expression1, expression2 are merge fields, expressions, or other numeric values.

Returns true if expression1 is equivalent to expression2.

Returns false if expression1 is not equivalent to expression2.

Does not equal <> and !=

Evaluates if two values are not equivalent.

Syntax

expression1<>expression2

expression1!=expression2

Where:

• expression1, expression2 are merge fields, expressions, or other numeric values.

Returns true if expression1 is not equivalent to expression2.

Returns false if expression1 is equivalent to expression2.

Less than <

Evaluates if one value is less than another.

Syntax

value1 < value2

Where:

• value1, value2 are merge fields, expressions, or other numeric values.

Returns true if value1 is less than value2.

Returns false if value1 is not less than value2.

Greater than >

Evaluates if one value is greater than another.

Syntax

value1 > value2

Where:

• value1, value2 are merge fields, expressions, or other numeric values.

Returns true if value1 is greater than value2.

Returns false if value1 is not greater than value2.

Less than or equal <=

Evaluates if one value is less than or equal to another.

Syntax

value1 <= value2

Where:

• value1, value2 are merge fields, expressions, or other numeric values.

Returns true if value1 is less than or equal to value2.

Returns false if value1 is not less than or equal to value2.

Greater than or equal >=

Evaluates if a value is greater than or equal to another.

Syntax

value1 >= value2

Where:

• value1, value2 are merge fields, expressions, or other numeric values.

Returns true if value1 is greater than or equal to value2.

Returns false if value1 is not greater than or equal to value2.

&&

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

See also AND.

Syntax

(logical1) && (logical2)

Where:

• logical1, logical2 are the values or expressions you want to evaluate

||

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

See also OR.

Syntax

(logical1) || (logical2) ||...(logical_n)

Where:

• logical1, logical2,...logical_n are the values or expressions you want to evaluate

Text operators

Concatenate &

Connects two or more strings.

Syntax

string1&string2

Where:

• string1, string2 are merge fields, expressions, or other values.