Examples of Advanced Formula Fields

Examples of Advanced Formula Fields
Sample Account Management Formulas
Sample Account Media Service Formulas
Sample Case Management Formulas
Sample Commission Calculations Formulas
Sample Contact Management Formulas
Sample Data Categorization Formulas
Using Date, Date/Time, and Time Values in Formulas
Sample Discounting Formulas
Sample Employee Services Formulas
Sample Expense Tracking Formulas
Sample Financial Calculations Formulas
Sample Image Link Formulas
Sample Integration Link Formulas
Sample Lead Management Formulas
Sample Metric Formulas
Sample Opportunity Management Formulas
Sample Pricing Formulas
Sample Scoring Calculations Formulas
Formula Operators and Functions
PDF

Newer Version Available

This content describes an older version of this product. View Latest

Formula Operators and Functions

Use these operators and functions when building formulas. All functions are available everywhere that you can include a formula—such as formula fields, validation rules, approval processes, and workflow rules, unless otherwise specified.
  • Within an email template, merge fields can only be used in formula functions and operations when the merge field belongs to the record the email will be related to, otherwise these fields won’t resolve.
  • Extraneous spaces in the samples below are ignored.

Note

Math Operators

Operator Description
+ (Add) Calculates the sum of two values.
- (Subtract) Calculates the difference of two values.
* (Multiply) Multiplies its values.
/ (Divide) Divides its values.
^ (Exponentiation) Raises a number to a power of a specified number.
() (Open Parenthesis and Close Parenthesis) Specifies that the expressions within the open parenthesis and close parenthesis are evaluated first. All other expressions are evaluated using standard operator precedence.

Text Operators

Operator Description
& and + (Concatenate) Connects two or more strings.

Date and Time Functions

Function Description
ADDMONTHS Returns the date that is the indicated number of months before or after a specified date. If the specified date is the last day of the month, the resulting date is the last day of the resulting month. Otherwise, the result has the same date component as the specified date.
DATE Returns a date value from year, month, and day values you enter. 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-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 the form of a number between 1 and 31.
HOUR Returns the local time hour value without the date in the form of a number from 1 through 24.
MILLISECOND Returns a milliseconds value in the form of a number from 0 through 999.
MINUTE Returns a minute value in the form of a number from 0 through 60.
MONTH Returns the month, a number between 1 (January) and 12 (December) in number format of a given date.
NOW Returns a date/time representing the current moment.
SECOND Returns a seconds value in the form of a number from 0 through 60.
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.
TODAY Returns the current date as a date data type.
WEEKDAY Returns the day of the week for the given date, using 1 for Sunday, 2 for Monday, through 7 for Saturday.
YEAR Returns the four-digit year in number format of a given date.

Logical Functions

Function Description
AND Returns a TRUE response if all values are true; returns a FALSE response if one or more values are false.
BLANKVALUE Determines if an expression has a value and returns a substitute expression if it doesn’t. If the expression has a value, returns the value of the expression.
CASE Checks a given expression against a series of values. If the expression is equal to a value, returns the corresponding result. If it isn't equal to any values, it returns the else_result.
IF Determines if expressions are true or false. Returns a given value if true and another value if false.
ISBLANK Determines if an expression has a value and returns TRUE if it does not. If it contains a value, this function returns FALSE.
ISCLONE Checks if the record is a clone of another record and returns TRUE if one item is a clone. Otherwise, returns FALSE.
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.
ISNULL Determines if an expression is null (blank) and returns TRUE if it is. If it contains a value, this function returns FALSE.

Use ISBLANK instead of ISNULL in new formulas. ISBLANK has the same functionality as ISNULL, but also supports text fields. Salesforce will continue to support ISNULL, so you do not need to change any existing formulas.

Important
ISNUMBER Determines if a text value is a number and returns TRUE if it is. Otherwise, it returns FALSE.
NOT Returns FALSE for TRUE and TRUE for FALSE.
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.

Use BLANKVALUE instead of NULLVALUE in new formulas. BLANKVALUE has the same functionality as NULLVALUE, but also supports text fields. Salesforce continues to support NULLVALUE, so you don’t need to change existing formulas.

Important
OR Determines if expressions are true or false. Returns TRUE if any expression is true. Returns FALSE if all expressions are false.
PRIORVALUE Returns the previous value of a field.

Math Functions

Function Description
ABS Calculates the absolute value of a number. The absolute value of a number is the number without its positive or negative sign.
CEILING Rounds a number up to the nearest integer, away from zero if negative.
DISTANCE Calculates the distance between two locations in miles or kilometers.
EXP Returns a value for e raised to the power of a number you specify.
FLOOR Returns a number rounded down to the nearest integer, towards zero if negative.
GEOLOCATION Returns a geolocation based on the provided latitude and longitude. Must be used with the DISTANCE function.
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.
MCEILING Rounds a number up to the nearest integer, towards zero if negative.
MFLOOR Rounds a number down to the nearest integer, away from zero if negative.
MIN Returns the lowest number from a list of numbers.
MOD Returns a remainder after a number is divided by a specified divisor.
ROUND Returns the nearest number to a number you specify, constraining the new number by a specified number of digits.
SQRT Returns the positive square root of a given number.

Text Functions

Function Description
BEGINS Determines if text begins with specific characters and returns TRUE if it does. Returns FALSE if it doesn't.
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 and returns TRUE if the first argument contains the second argument. If not, returns FALSE.
FIND Returns the position of a string within a string of text represented as a number.
GETSESSIONID Returns the user’s session ID.
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 >.
HYPERLINK Creates a link to a URL specified that is linkable from the text specified.
IMAGE Inserts an image with alternate text and height and width specifications.
INCLUDES Determines if any value selected in a multi-select picklist field equals a text literal you specify.
ISPICKVAL Determines if the value of a picklist field is equal to a text literal you specify.
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 (').
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.
LEFT Returns the specified number of characters from the beginning of a text string.
LEN Returns the number of characters in a specified text string.
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.
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 that you specify to the right-side of a text string.
SUBSTITUTE Substitutes new text for old text in a text string.
TEXT Converts a percent, 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.
TRIM Removes the spaces and tabs from the beginning and end of a text string.
UPPER Converts all letters in the 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.
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.
VALUE Converts a text string to a number.

Summary Functions

The following functions are available with summary, matrix, and joined reports.

Function Description
PARENTGROUPVAL This function returns the value of a specified parent grouping. A “parent” grouping is any level above the one containing the formula. You can use this function only in custom summary formulas and at grouping levels for reports, but not at summary levels.
PREVGROUPVAL This function returns the value of a specified previous grouping. A “previous” grouping is one that comes before the current grouping in the report. Choose the grouping level and increment. The increment is the number of columns or rows before the current summary. The default is 1; the maximum is 12. You can use this function only in custom summary formulas and at grouping levels for reports, but not at summary levels.

Advanced Functions

Function Description
CURRENCYRATE Returns the conversion rate to the corporate currency for the given currency ISO code. If the currency is invalid, returns 1.0.
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.
IMAGEPROXYURL Securely retrieves external images and prevents unauthorized requests for user credentials.
INCLUDE Returns content from an s-control snippet. Use this function to reuse common code in many s-controls.
ISCHANGED Compares the value of a field to the previous value and returns TRUE if the values are different. If the values are the same, this function returns FALSE.
JUNCTIONIDLIST Returns a JunctionIDList based on the provided IDs.
LINKTO Returns a relative URL in the form of a link (href and anchor tags) for a custom s-control or Salesforce page.
PREDICT Returns an Einstein Discovery prediction for a record based on the specified record ID or for a list of fields and their values.
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.
REQUIRESCRIPT Returns a script tag with source for a URL you specify. Use this function when referencing the Lightning Platform AJAX Toolkit or other JavaScript toolkits.
URLFOR Returns a relative URL for an action, s-control, Visualforce page, or a file in a static resource archive in a Visualforce page.
VLOOKUP Returns a value by looking up a related value on a custom object similar to the VLOOKUP() Excel function.