Calculate Custom Field Values with Formulas
A formula is an algorithm that derives its value from other fields, expressions, or values. Formulas can automatically calculate the value of a field based on other fields.
Formula Data Types
The data type of a formula determines the type of data expected to be returned from a formula.
Elements of a Formula
A formula can contain references to the values of fields, operators, functions, literal values, or other formulas.
Formula Operators and Functions
Use these operators and functions when building formulas. Functions and Operators are available when building a formula field.
Build a Formula Field
A custom formula field requires few attributes to set.
Best practices for building a formula
The Formula Editor can be used to build a simple formula. Use these tips to determine the best way to map out formula logic; making it easier to troubleshoot errors.
Formula Data Types
The data type of a formula determines the type of data expected to be returned from a formula.
|
Data Type |
Description |
|
Text |
Text data type returns a string. To display text in addition to the formula output, insert that text in quotes. Use the text data type for text, phone, email, address, and auto-number fields. |
|
Number |
Returns a positive or negative integer with decimal values. Ordway uses the round half up tie-breaking rule for numbers in formula fields. Example: 12.345 displays as 12.35 and −12.345 displays as −12.35. |
|
Currency |
Returns a number in currency format with a currency sign. |
|
Boolean |
Returns a true or false value. |
|
Date |
Returns data that represents a day on the calendar. The current date can be acquired by calling the built-in function TODAY() in a formula. |
|
Date/Time |
Returns data that represents a moment in time. A date/time field includes the date and also the time of day including hour, minutes, and seconds. Insert the current date and time in a formula using the NOW() function. |
|
Percentage |
Returns a number in percent format followed by a percent sign. Percent data is stored as a decimal divided by 100, which means that 90% is equal to 0.90. |
Elements of a Formula
A formula can contain references to the values of fields, operators, functions, literal values, or other formulas.
One of more of these elements can be used to build a formula:
|
Element Name |
Description |
|
Literal Values |
A text string or number entered that is not calculated or changed. For example, when a value is always multiplied by 2% of an amount, the formula would contain the literal value of 2% of that amount:
Example: ROUND((product.price*0.02), 2)
This example contains every possible part of a formula:
A function called ROUND used to return a number rounded to a specified number of decimal places. A field reference called product.price. An operator, *, that tells the formula builder to multiply the contents of the product.price field by the literal value, 0.02. A literal number, 0.02. Use the decimal value for all percents.
To include actual text in your formula, enclose it in quotes. The last number 2 in this formula is the input required for the ROUND function that determines the number of decimal places to return. |
|
Field Reference |
Reference the value of another custom or standard field. Use the Insert Parameter in the editor and use the drop-down list to insert a field from a related object in the formula where necessary. |
|
Function |
A system-defined formula that can require an input and returns a value or values. For example, The ADDMONTHS(date, number) function requires specified date and number of months that returns the date that is the indicated number of months before or after a specified date. |
|
Operator |
A symbol that specifies the type of calculation to perform or the order in which to do it. For example, the + symbol specifies two values should be added. The open and close parentheses specify which expressions to be evaluated first. |
Formula Operators for Functions
Use these operators and functions when building formulas. Functions and Operators are available when building a formula field.
Operators
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. |
Logical Operators
|
Operator |
Description |
|
= (Equal) and == (Equal) |
Evaluates if two values are equivalent. The = and == operator are interchangeable. |
|
< (Less Than) |
Evaluates if a value is less than the value that follows this symbol. |
|
> (Greater Than) |
Evaluates if a value is greater than the value that follows this symbol. |
|
<= (Less Than or Equal) |
Evaluates if a value is less than or equal to the value that follows this symbol. |
|
>= (Greater Than or Equal) |
Evaluates if a value is greater than or equal to the value that follows this symbol. |
Functions
Date and Time Functions
|
Functions |
Description |
|
ADDMONTHS |
Returns the date that is the indicated number of months before or after a specified date. If the resulting month has fewer days than the start month, the function returns the last day of the resulting month. Otherwise, the result has the same day component as the specified date. |
|
DATEVALUE |
Returns a date value for a date/time or text expression. |
|
TIMEVALUE |
Returns the local time value without the date, such as business hours. |
|
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. |
|
MONTH |
Returns the month, a number between 1 (January) and 12 (December) in number format of a given date. |
|
YEAR |
Returns the four-digit year in number format of a given date. |
|
HOUR |
Returns the local time hour value without the date in the form of a number from 1 through 12. |
|
MINUTE |
Returns a minute value in the form of a number from 0 through 60. |
|
SECOND |
Returns a seconds value in the form of a number from 0 through 60. |
Logical Functions
|
Function |
Description |
|
AND |
Returns a TRUE response when all values are true; returns a FALSE response when one or more values are false. |
|
BLANKVALUE |
Determines if an expression has a value and returns a substitute expression when it does not. When the expression has a value, returns the value of the expression. |
|
IF |
Determines if expressions are true or false. Returns a given value when true and another value when false. |
|
SWITCH |
Checks a given expression against a series of values. When the expression is equal to a value, returns the corresponding result. When it is not equal to any values, it returns the else_result. |
|
CASE |
Checks a given expression against a series of values. When the expression is equal to a value, returns the corresponding result. When it is not equal to any values, it returns the else_result. |
|
ISNULL |
Determines if an expression is null (blank) and returns TRUE when it is. When it contains a value, this function returns FALSE. |
|
ISBLANK |
Determines if an expression has a value and returns TRUE if it does not. When it contains a value, this function returns FALSE. |
|
NULLVALUE |
Determines if an expression is null (blank) and returns a substitute expression if it is. When the expression is not blank, returns the value of the expression. |
|
BOOLEAN |
Returns a boolean value from a text. |
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. |
|
MIN |
Returns the lowest number from a list of numbers. |
|
MAX |
Returns the highest number from a list of numbers. |
|
ROUND |
Returns the nearest number to a number specified, constraining the new number by a specified number of digits. |
|
ROUNDDOWN |
Returns the round values of the number, it always rounds a number down. |
|
ROUNDUP |
Returns the round values of the number, it always rounds a number up. |
|
AVG |
Returns the average of the given number. |
|
SUM |
Returns the sum of the list of numbers. |
|
COUNT |
Returns the count of the given list of numbers. |
|
SQRT |
Returns the positive square root of a given number. |
Text Functions
|
Function |
Description |
|
TRIM |
Removes the spaces and tabs from the beginning and end of a text string. |
|
LEN |
Returns the number of characters in a specified text string. |
|
LEFT |
Returns the specified number of characters from the beginning of a text string. |
|
RIGHT |
Returns the specified number of characters from the end of a text string. |
|
MID |
Returns the specified number of characters from the middle of a text string given the starting position. |
|
FIND |
Returns the position of a string within a string of text represented as a number. |
|
CONCAT |
Returns a string by concatenating the values of the specified columns and input strings |
|
SUBSTITUTE |
Substitutes new text for old text in a text string. |
|
CONTAINS |
Compares two arguments of text and returns TRUE when the first argument contains the second argument. When not, returns FALSE. |
Build a Formula Field
- Build a formula field the same way as creating a custom field for a specific object. Select Formula from the Field Type a drop-down menu. See Manage Custom Fields.
- Select the Field Return Type for the formula from the drop-down menu based on the output of the calculation. See Formula Data Types table above.
- Set Default Value for the formula field.
- Build the formula. Formula fields can contain up to 3,886 characters, including spaces, return characters, and comments. When the formula requires more characters, create separate formula fields and reference them in another formula field.
Insert Parameter, Operator, and Functions
- To insert Parameter, click Insert Operator and choose appropriate parameter from the Insert Parameters drop-down list from related object.
- To insert an operator, click Insert Operator and choose the appropriate operator from the Insert Operator drop-down list.
- To insert Functions, click Insert Functions and choose the appropriate function from the Insert Function drop-down list.
Check Syntax and Save
- To check the formula for errors, click Check Syntax. Error messages are displayed in case of error in the formula.
- Save when finished.
Formula Building Best Practices
Put Every Function on a Separate Line
It’s easy to fall into the habit of keeping an entire formula on a single line, especially when the formula is small. Putting each function on its own line makes the formula easier to read and also troubleshoot.
These examples show the same formula, first with no line breaks, and then with each function on a separate line.
IF(AND(ISBLANK(myDate_c),active_c=true),"Missing Date","Not Applicable")
IF(
AND(
ISBLANK(myDate_c),
active_c=true
),
"Missing Date",
"Not Applicable"
)
Indent Sections Within Parentheses
When the formula involves multiple functions, indentation helps visually isolate each function and makes it easier to identify errors, such as misplaced characters.
In this example, with indentation, the bulk of the formula sits within a single IF statement and the AND statement contains two functions. Inside the AND statement, the function ISBLANK is enclosed in parentheses.
IF(
AND(
ISBLANK(myDate_c),
active_c=true
),
"Missing Date",
"Not Applicable"
)
Indentation can also help identify mistakes. With a flat layout, it’s difficult to see that an extra “)” is included after the ISBLANK statement, and there are no visual clues about how the formula is structured.
IF(
AND(
ISBLANK(myDate_c)
),
active_c=true
),
"Missing Date",
"Not Applicable"
)
The indented layout makes it easy see the formula’s structure. Quickly find and remove the extra character so the AND statement is correctly formatted.
IF(
AND(
ISBLANK(myDate_c)
),
active_c=true
),
"Missing Date",
"Not Applicable"
)
Write Statement and Function Names in Uppercase
All the examples here use uppercase letters for statement and function names, such as IF, AND, and ISBLANK. Using uppercase for these terms creates a clear distinction between functions and parameters and brings some visual clarity to a complex formula.
Handle Null and Required Input Field Values
These examples reference a field called myDate__c and use the ISBLANK check to confirm that the field is populated. It’s important to verify the contents of any field in a formula. Without this verification, a formula can fail.
For example, add a second date to the formula and perform a greater than operation, be sure to include the ISBLANK check for the second date to ensure that the formula executes correctly.
IF(
AND(
ISBLANK(myDate__c),
ISBLANK(mySecondDate__c),
active__c=true,
mySecondDate__c > myDate__c
),
"Missing Date",
"Not Applicable"
)
Comments
1 comment
I'm trying to create a custom field to store the discounted MRR value so that I can sync this value into Salesforce. I've created the formula in the image below and have tested the syntax which is saying the formula is correct. The field on the customer is showing up as blank when I navigate to a customer. Is there something I need to do to activate the formula?
Please sign in to leave a comment.