Formula fields are auto-calculated fields that derive their value from an expression assigned by the admin. You can create formula fields across all modules ( Contact, Account, and Deal), with a limit of 40 formula fields per module.
1. Defining a formula
When creating a formula field, you need to add the formula using which the field value is calculated. A formula can have four elements to it:
Function: An expression that defines a relationship between one independent variable and the other. Example: sum(5, 10) where 'sum' is a function.
Operator: The symbol indicating an operation to be performed. Example: 5+3=8, where the operator + indicates addition.
Field: This refers to the default fields in the chosen module. The value from the chosen field will be used as a variable in the formula.
Return type: The type of value that will be returned when the formula is computed.
Note: The number of fields you've chosen for formula fields is deducted from the total number of field limits available to you. So if you're on the Pro plan and have a maximum limit of 50 number fields and you have chosen 5 formula fields with return type as number fields, you will now have 45 number fields remaining.
You can read more about field limits across plans here.
2. Creating a formula
Depending on the module you choose and the formula you want to create, you can create a formula as a combination of functions, operators, and fields. Let’s look at an example:
Calculating the commission value for a salesperson based on the deal value
Let’s consider that the commission value for a salesperson is 5% of the deal value. So we’ll create the formula in the deal module, to access the “Deal value” field.
Step 1: Adding a formula field
Go to Admin Settings > Contacts/Accounts/Deals Module and click the . This brings up the Add Field overlay. Select the ‘ buttonFormula’ icon from the custom field tray and click .
Step 2: Name the field and set the return type
Assign your field a name that indicates what the field value is. In this example, we’ve given our field the name, “Commission based on deal value.”
Step 3: Set the return type
Depending on the value that is being calculated, choose the return type of the output from the drop-down. As we’re dealing with commission values in this example, the return type is set as “Number.”
Note: The return type checkbox accepts True or False as the values.
Step 4: Pick the formula entities
Here, we use an operator and the deal field. So our formula would be Formula = (5/100) * Deal value.
Step 5: Check the formula syntax
Once you’ve entered the formula, you can check the syntactic validity of your formula. The system checks if the output matches the set return type. It also checks if the field values are of the same type. To see if your formula is valid, click the “Check Syntax” option. If an error is detected, it will display an error message explaining what went wrong.
Here is an example of an error message. It will indicate the entity in which the error is, in this case, the operator/function section of the formula. A common error that occurs when using functions is forgetting to add the closing parenthesis. This is explained by the error message.
Step 6: Click .
Note: The formula field created will now compute on newly created records or on records where one of the formula field variables are edited.
3. What happens when the field value used in the formula is empty?
Say, the salesperson hasn’t filled in the dependent field yet. Here’s an example, we’ve set the commission based on “Deal Value” and have not entered the value. The formula field “Commission based on Deal Value” will then show the value as 0.
In the case of text functions, the same message is displayed when the field is empty. In this example, we’ve set the formula for the “Trimmed Name” field to concatenate the first and last name. The first name has not been provided, so the trimmed field will display, “Enter value”.
4. What happens when I downgrade?
Formula fields are available to all users who are in plans Pro and Enterprise. In the case of a downgrade, your existing formula fields will not be deleted. The formula is removed, and the field becomes a manually calculated field. The user can enter the value based on their computation of the formula.
5. What are the supported formula field functions?