Formulas in Salesforce play a crucial role in boosting the functionality and productivity of the platform. They automate calculations, ensure data accuracy, and control data visibility efficiently. With formulas, users can customize business logic easily without extensive coding skills, making Salesforce a powerful tool for both simple and complex operations.
Where Can Formulas Be Used in Salesforce?
In Salesforce, the versatility of formulas extends across various features and functionalities, but there are specific areas where they are most commonly used:
- Custom Formula Fields: Create custom fields of “formula” type that automatically calculate values based on other data entered in your records. For example, calculating a discount or a total price.
- Validation Rules: Ensure data integrity by using formulas to validate the data entered into Salesforce. For example, you can set a rule that a discount field must be less than 30%.
- Salesforce Flows & Approval Process: Automate actions based on specific conditions. Formulas can be used in Flows to determine if a record meets the criteria for triggering an action or routing it for approval.
- Email Templates: Dynamically customize email content sent from Salesforce by including formula fields that insert data relevant to the recipient.
- Reports & Dashboards: Enhance dashboards by using formula fields to create calculated metrics, providing deeper insights into your data.
The focus of this blog post will be on custom formula fields.
Salesforce Formula Data Types
You can create custom formula field of different data types, each tailored to specific needs within your CRM. Here’s a quick guide to understanding these formula data types and how they’re used:
Checkbox: This formula returns a true or false value, displayed as a checkbox on record detail pages and reports. Use True to indicate a checked box and False for an unchecked one.
Currency: This type returns a numeric value formatted as currency, capable of handling up to 18 digits, complete with a currency symbol. Salesforce rounds currency values using the “round-half-up” method. For instance, 23.5 rounds to 24, and 22.5 rounds to 23, while negative values like −22.5 round to −23, and −23.5 rounds to −24.
Date: This field represents a specific calendar day. You can fetch the current date in your formulas with the TODAY() function. However, this data type is not available for custom summary formulas in reports.
Date/Time: This returns a moment in time, including both the date and time (hours, minutes, and seconds). The NOW() function allows you to insert the current date and time into a formula. Like the Date type, it’s not available for custom summary formulas in reports.
Number: This type returns either a positive or negative integer or decimal number, up to 18 digits. Salesforce applies the “round half up” rule here as well; for example, 12.345 becomes 12.35, and −12.345 becomes −12.35.
Percent: This returns a number formatted as a percentage, up to 18 digits, followed by a percent sign. It’s stored as a decimal divided by 100, meaning 90% is stored as 0.90.
Text: Capable of returning a string up to 3900 characters, this type is versatile for formulas needing to display text alongside the output. It’s suitable for fields like text, text area, URL, phone, email, address, and auto-number. Note that text area is not a supported data type for custom summary formulas in reports.
Time: This returns a time of day, excluding the date, and includes hours, minutes, seconds, and milliseconds. The TIMENOW() function inserts the current time into a formula.
Each of these formula types serves a distinct purpose, allowing for precise data manipulation and presentation in Salesforce. Understanding how to use these effectively can significantly enhance your Salesforce implementation, offering tailored solutions for your business processes.
Implementing Formulas: Tips and Best Practices
To get the most out of Salesforce formulas, here are some key strategies:
Start with the Basics: Before diving into complex formulas, familiarize yourself with simpler ones. This will help you understand the basics of formula syntax and logic.
Iterate and Test: Salesforce lets you preview and test your formulas. Use this to your advantage to refine them until they perform exactly as needed.
Focus on Efficiency: Remember, the more complex a formula, the more resources it uses. Aim to keep your formulas as simple and efficient as possible to maintain your system’s performance.
How to Create a Formula Field in Salesforce?
To create a formula field, navigate to Setup -> Object Manager -> select the object -> Fields & Relationships -> New & select the data type as Formula
Select the Right Data Type: Choose a data type for your formula. This decision should be based on what your formula calculates and the result you expect. Salesforce provides a variety of formula data types to match your needs as shown here.
Decide on Decimal Places: If your formula deals with numbers, currency, or percentages, you’ll need to set the number of decimal places. Keep in mind that for organizations using multiple currencies, Salesforce will apply the decimal settings specific to each currency. Salesforce rounds numbers using the “round half up” method, meaning 12.345 rounds to 12.35, and similarly, -12.345 rounds to -12.35.
Proceed to the Next Step: Click “Next” to continue building your formula.
Craft Your Formula: Now, you’ll construct your formula. Remember, formula fields in Salesforce can hold up to 3,900 characters, counting every space, return character, and comment. If you find yourself needing more space, consider creating additional formula fields and then referencing them within your main formula. It’s worth noting that after a formula is evaluated, the maximum number of characters that can be displayed is 1,300.
Check for Errors: To ensure your formula is error-free, click “Check Syntax.” This will highlight any issues that need to be addressed before proceeding.
Add a Description (1) (Optional): It’s a good practice to include a brief description of your formula’s purpose in the Description box. This can help you and others understand the formula’s function at a glance in the future.
Handle Blank Fields (2): If your formula involves number, currency, or percent fields, you’ll need to decide how to treat blank fields. You have two options:
Treat Blank Fields as Zeros: This setting assigns a zero value to any blank fields, ensuring calculations proceed without interruption.
Treat Blank Fields as Blanks: Choose this if you prefer to leave blank fields as they are, which might be necessary for certain calculations or data presentations.
Set Field-Level Security: Next, decide who should see this field. Set the field-level security to make the field visible only to specific profiles, enhancing data privacy and relevance.
Select Page Layouts and Dynamic Forms: When adding a new field, it’s automatically placed in the first two-column section on page layouts and at the user detail page’s bottom for custom fields.
If using Dynamic Forms, manually add the field to the form. Dynamic Forms let you place fields anywhere on the page and adjust visibility.
Save Your Work: Click “Save” to complete the process and add your new formula field to Salesforce. If you’re planning to create additional fields, select “Save & New” to keep working without interruption.
Advanced Formulas in Salesforce
What about complex formulas? What’s the difference between simple formulas and complex or advanced formulas? The main difference lies in the complex logic or advanced functions used in the formula.
Add Business Days Formula
Let’s look at an example of complex formulas. Imagine a company, “Acme Corp,” uses Salesforce to manage customer follow-ups. They promise to provide updates three business days after an initial meeting. Using this formula in their Salesforce setup allows team members to automatically calculate the follow-up date, ensuring they adhere to their commitment without needing to manually calculate dates.
For instance, if Acme Corp has a meeting on Thursday, the formula calculates the follow-up date as the following Tuesday (Thursday + 5 days, including Saturday and Sunday). This automation streamlines the workflow, improves efficiency, and ensures consistency in customer communication.
This formula calculates three business days from a specified date, considering weekends:
CASE(
MOD( date - DATE( 1900, 1, 7 ), 7 ),
3, date + 2 + 3,
4, date + 2 + 3,
5, date + 2 + 3,
6, date + 1 + 3,
date + 3)Here’s how it works:
The MOD function is used to find the day of the week for the given date, with the reference point being January 7, 1900 (a Sunday). This function returns a number from 0 (Sunday) to 6 (Saturday).
The CASE function then determines the day of the week and calculates the future date accordingly:
- If the date falls on Wednesday (3), Thursday (4), or Friday (5), the formula adds five days to account for two weekend days plus three weekdays.
- If the date is Saturday (6), it adds four days, considering the weekend plus the next three business days.
- For Sunday through Tuesday (0, 1, 2), it simply adds three days, as these days do not require crossing over a weekend.
This formula can be adjusted to accommodate different numbers of business days by changing the numbers added based on the day of the week. It’s a versatile tool that can be tailored to fit various business needs, from shipping timelines to payment processing deadlines.
Stars for Rating Formula
In many business scenarios, visually representing data can significantly enhance understanding and engagement. For instance, displaying customer feedback or product ratings as a set of stars is more intuitive and visually appealing than simply showing a numeric score.
Salesforce enables this visual representation through the use of formulas and image files. Here’s how you can create a formula to display one to five stars based on a rating or score field within Salesforce.
This Salesforce formula uses the IMAGE function to display star ratings as images, based on the value of a custom field (Rating__c):
IMAGE(
CASE(Rating__c,
"1", "/img/samples/stars_100.gif",
"2", "/img/samples/stars_200.gif",
"3", "/img/samples/stars_300.gif",
"4", "/img/samples/stars_400.gif",
"5", "/img/samples/stars_500.gif",
"/img/samples/stars_000.gif"),
"rating") 
Here’s how it works:
The CASE function checks the value of Rating__c and matches it to a corresponding image file. Each image file (e.g., stars_100.gif for a rating of 1) represents a different number of filled stars.
If the rating value matches one of the cases (1 through 5), the formula returns the path to the corresponding star image. If there’s no match, it defaults to an image with no filled stars (stars_000.gif).
The IMAGE function then takes this path and renders the appropriate star image in the Salesforce record, with “rating” serving as the alt text for the image.
This approach can be customized for various applications beyond product ratings, such as service performance evaluations, employee feedback, or any scenario where a visual representation of rating data is beneficial.
It not only makes the data more accessible but also enhances the user experience within Salesforce by providing an immediate, visual summary of key metrics.
Limitations and Restrictions on Formulas in Salesforce
Salesforce formula fields are incredibly powerful, enabling you to automate calculations and logic within your CRM. However, they come with specific limitations and restrictions to ensure optimal system performance:
Character and Size Limits
Character Limit: Formula fields can hold up to 3,900 characters, including spaces, line breaks, and comments. If your formula exceeds this limit, consider dividing it into multiple formula fields and referencing them in a composite formula.
Note: After a formula is evaluated, the maximum number of characters displayed is 1,300.
Save Size Limit: The saved size of a formula field must not exceed 4,000 bytes. Multi-byte characters (such as those in some languages or special characters) may increase the save size beyond the visible character count, requiring careful consideration in formula design.
Compile Size Limit: When compiled, a formula field’s size cannot surpass 15,000 bytes. This compile size includes all fields, values, and referenced formulas. Notably, functions like TEXT, DATEVALUE, DATETIMEVALUE, and DATE can significantly inflate the compile size.
Field References and Usage
Record Type References: Default value formulas can only reference fields from their record type. However, formula fields and formulas used in approval processes or validation rules may reference fields from both their record type and related records via lookup or master-detail relationships. For instance, a validation rule on opportunities might include fields from accounts, campaigns, and opportunities.
Restricted Field Types: Long text area, encrypted fields, and Description fields are not usable in formulas. Additionally, a field cannot be dependent on another formula that refers back to it, avoiding circular dependencies.
Field Deletion: Fields referenced in any formula must be removed from the formula before they can be deleted, ensuring data integrity and preventing errors.
Campaign Statistics: While campaign statistic fields are off-limits for formulas in field updates, workflow rules, or validation rules, they can be utilized in custom formula fields.
HTML Tags: The Salesforce UI automatically escapes HTML tags in formula fields. To incorporate HTML elements, substitute direct HTML with functions like HYPERLINK or IMAGE.
Person Accounts: Custom formula fields from contacts cannot be referenced through person accounts, requiring alternative approaches for these scenarios.
NULL Values in Checkbox Fields: Using NULL as an expression in Checkbox formula fields is not supported, necessitating different logic for these cases.
Navigating Complex Formulas in Salesforce: Challenges and Solutions
As we delve into the intricacies of complex formulas within Salesforce, it’s essential to understand the common challenges that users may encounter and the strategic solutions that can address these hurdles. Complex formulas, while powerful, come with their own set of obstacles, from debugging errors to managing system limitations. Here’s how to navigate these challenges effectively:
1. Debugging Errors
Challenge: One of the most daunting tasks when working with complex formulas is identifying and fixing errors. These can range from simple syntax mistakes to more intricate logical errors that cause the formula not to behave as expected.
Solution: Salesforce provides detailed error messages that can guide you to the source of the problem. Taking a systematic approach to debugging is crucial. Break down the formula into smaller parts, test each segment individually, and use comments within your formula to keep track of complex logic. Additionally, Salesforce’s formula editor and online communities offer valuable resources for troubleshooting.
2. Managing Formula Limits
Challenge: Salesforce imposes limits on formula size and complexity to ensure system performance. These include character count limits for formulas and compiled size limits that can affect how many formulas and custom fields you can have.
Solution: To work within these limits, you might need to simplify your formulas or break down a single complex formula into multiple smaller ones. Consider using helper text fields that store intermediate calculations. This approach not only helps manage formula size but also makes your formulas easier to understand and maintain.
3. Ensuring Formula Efficiency
Challenge: Complex formulas can significantly impact Salesforce’s performance, especially when they are used in reports, dashboards, or are recalculated frequently.
Solution: Optimize your formulas for efficiency by using the most straightforward logic possible and minimizing the number of fields and calculations involved. Evaluate the necessity of each formula and consider alternative solutions, such as Salesfore Flows or Apex triggers, for particularly complex logic that might be more efficiently handled outside of formula fields.
4. Avoiding Circular Dependencies
Challenge: Circular dependencies occur when formulas refer back to themselves either directly or through a chain of references. This can cause errors and prevent your formulas from compiling.
Solution: Carefully plan your formula fields and their dependencies to avoid circular references. Map out the relationships between fields before creating your formulas to ensure a clear hierarchy and flow of data.
5. Maximizing Field References
Challenge: Salesforce also limits the number of other fields a formula can reference. This can be particularly challenging in complex business scenarios where a formula needs to pull data from various sources.
Solution: Strategically use formula fields to aggregate or compute intermediate values, reducing the number of direct field references in any single formula. Additionally, consider leveraging custom code solutions like Apex if the complexity and field reference requirements exceed what is practical within formula constraints.
The Road Ahead
The upcoming enhancements to Salesforce’s formula capabilities with Einstein, spanning from the Spring ’24 to the Winter ’25 releases, mark a significant leap forward in simplifying formula management within the platform. These innovations are designed to empower Salesforce consultants, administrators, and developers by offering a more intuitive, efficient, and powerful way to work with formulas.
1. Formula Explanation (Spring ’24 Release)
Formula Explanation: Soon you will be able to ask Einstein to explain formula used in validation rules, enabling users to quickly grasp the logic behind complex formulas through natural language explanations. This feature aims to demystify existing formulas, making it easier for admins to under and modify them.
2. Formula Debugging (Summer ’24 Release)
Formula Debugging: On the roadmap is formula debugging where Einstein will fix the formula for you rather than just checking the syntax. This enhancement is set to reduce manual debugging efforts, ensuring formulas are both accurate and functional.
3. Formula Creation (Winter ’25 Release)
Formula Creation: Even more impressive, you’ll be able to describe what you want in natural language, and Einstein will craft a new formula based on your description. This innovative feature promises to significantly speed up the formula creation process, making it more accessible to users regardless of their technical expertise in Salesforce formula syntax.
Understanding and utilizing Salesforce formulas effectively is crucial for unlocking the platform’s full potential. The key takeaways emphasize the importance of building a strong foundation in basic concepts before tackling more complex implementations. Efficiency in formula construction is important for maintaining optimal system performance, while knowledge of Salesforce’s formula limits is essential for navigating and overcoming potential challenges.
References & Useful URLs
- Salesforce help article – Formula Data Types
- Salesforce help article – Build a Formula Field
- Salesforce help article – Formula Field Limits and Restrictions
- Salesforce PDF – Tips for Reducing Formula Size
- Blog Post – Salesforce, Developers Examples of Advanced Formula Fields
- Blog Post – Ask Einstein to Explain Formula in Validation Rules
About the Author
This is a guest post by Dorian Sabitov. Dorian is a Salesforce enthusiast currently combining the roles of Salesforce Developer, Administrator, Consultant and Lead Author of SFApps.info portal. He is passionate about the Salesforce ecosystem, enjoys exploring new features inside Salesforce and sharing his knowledge with others in his blog and LinkedIn. You may also also want to check out the following blog post by the author:
