Introduction
Formulas are one of Salesforce’s most powerful tools for manipulating, understanding, and exploring your data. From automating routine calculations to enforcing data best-practices, formulas are a key component of making your org work the way you want it to. But what do you do when one of those pesky errors stands between you and your vision?. In today’s post, we’ll walkthrough troubleshooting a formula error from start to finish, so you can learn what to ask, and how to react, when you’re not getting the results you expect.
Let’s dive in!
The Problem
Today’s example comes straight from the Trailblazer community, that wonderful resource where you can ask all of your Salesforce-related head-scratchers and get the solutions you need from the community of engaged experts.
When you have an idea for a formula, think about it in everyday language before you pull up the Advanced Formula Editor. Start by asking yourself what your goal is and describing it conversationally.
For example: Say that your goal is to ensure that if anyone other than a System Administrator marks an opportunity as “Closed Lost,” they have to provide an explanation.
For example, we assume that you already have the custom picklist field, Loss Reason, populated with reasons for losing an opportunity. If you don’t have that field, take a moment to create it. Because no one should be able to save a record without a reason, we use a validation rule formula to accomplish our goal.
In plain language, your formula is:
Show an error when the Stage of an opportunity is changed to “Closed Lost” by someone who isn’t a System Administrator, and Loss Reason is blank.
After you’ve written out your formula, look for keywords like “and,” “or,” and “if” that correspond to the functions used throughout this module. Meanwhile, these functions are the building blocks of larger, more complicated formulas. Recognizing them in your use cases helps you implement such formulas.
Advanced Formulas
AT first, to check that the user is not a System Administrator, insert $Profile.Name, a merge field that represents the profile making edits to the record. To check if StageName has been updated, use ISCHANGED(). ISCHANGED() returns true if the current value of a field differs from the previous value.
Also, check the rest of your requirements with ISPICKVAL(). For more information on ISPICKVAL(), see Using Picklists in Formulas.
- In Setup, use the quick find box to find the Object Manager.
- Click Opportunity | Validation Rules.
- Click New.
- In Rule Name, enter Loss Reason Required.
- In Error Condition Formula, enter the following formula:
- In Error Message, enter Opportunities can’t be marked Closed Lost without providing a Loss Reason.
- For error location, select Field and Loss Reason.
- Click Save.
Now, whenever a user who isn’t a System Administrator marks an opportunity as Closed Lost, an error message displays.
Use Clear Spacing and Formatting
Formulas are easier to read and understand when you use proper spacing and formatting. When we worked on Text formulas, a formula that dynamically assigns a rating to a lead based on the country, revenue, and source.
Although this formula is syntactically correct, it’s almost impossible to tell what it does or how the logic works. Because white space and returns don’t matter in formulas, it’s easy to make your formula more readable with spaces. Here’s the same formula, but cleaned up with indentations and returns.
Although there are no hard and fast rules for formatting formulas, we recommend with two spaces each time you nest a logical statement. When you indent properly and consistently, it’s easier to see which function you’re working in and avoid mismatched parentheses.
Another way to make logical statements easier to read is by using logical operators instead of functions. When you use && instead of AND(), or || instead of OR(), it’s easier for someone reading your formula to follow the logical flow. Here’s the same formula one more time, with && and || instead of AND() and OR().
Troubleshoot Formula Errors
Syntax Errors
Even advanced formula gurus run into problems with their formulas occasionally. The most common formula errors are syntax errors. To check the syntax of your formula at any time, click Check Syntax.
Missing or Extra Parentheses
It’s easy to accidentally type an extra parenthesis in your formula or leave one out. This error is especially common when nesting logical statements such as IF(), AND(), and OR().
This formula, for example, is missing a close parenthesis at the end.
Field Does Not Exist
When it comes to syntax errors, the Advanced Formula Editor is your friend. It’s almost impossible to misspell the name of a field or a function when you’re using the Insert Operator or Insert Field button.
Here we meant to reference the field Principal__c, not Principle__c. You also get this error if you forget to put quotation marks around a Text string.
Incorrect Number of Parameters
If you use the incorrect number of parameters for a certain function, you run into syntax errors. Be extra careful with functions that take a variable number of parameters, like CASE().
This formula was missing the last argument, the fall-through case, in its CASE() statement. The Formula Editor assumed that we meant to check only four cases, not five. So it looked for 10 arguments in total, when we really meant to give it 12.
Unknown Function
You also encounter errors when you misspell a function name or try to use a function that doesn’t exist.
This formula attempts to reference the function MINIMUM(), which doesn’t exist. We meant to use MIN(), which takes a list of numbers and returns the minimum.
Working with Different Data Types
When creating a formula, think of what type of data you want it to return before you write your formula. If your formula returns a data type other than the one you selected, you can’t save it.
It’s easy to mix up data types when converting a value between types or using similar data types, like Date and Date/Time or Number and Currency. This formula, for example, is written to return a Date value when the selected formula return type is Date/Time.
To correct this error, you can change the return type of the formula field to Date. Or you can replace TODAY() with NOW() to produce a Date/Time value instead of a Date.
Compile Size and Formula Length Errors
Formula fields are powerful, but restricted in size. Formulas are limited to 3,900 characters or 4,000 bytes, including spaces, return characters, and comments, and can’t exceed 5,000 bytes when compiled. It’s important to understand the differences between these size restrictions and how to work around or within the constraints.
Length Limit
Formulas are limited to 3,900 characters. You can shorten long formulas in several ways. Replacing AND() with &&, for example, saves a few characters with each use, as does replacing nested IF() statements with a CASE() statement. Shorter field names and comments also make a small but significant difference in the length of your formula.
If your formula field is significantly longer than 3,900 characters, use a helper formula field.
Compile Size Limit
A formula that is less than 3,900 characters can still exceed the 5,000-byte compile size limit. When a formula is over the compile size limit, creating helper fields and shortening field names or comments doesn’t make a difference. When you reference a helper field, its compile size is added to the compile size of the formula that references it. One way to reduce a formula’s compile size is by minimizing references to other formula fields.
Some methods for reducing formula length, such as replacing nested IF() statements with a CASE() statement, also reduce a formula’s compile size.
To Conclude
In the ever-evolving world of Salesforce, staying ahead of the curve is essential. Embark on a journey to enhance your Salesforce proficiency as we explore the power of advanced formulas and the art of troubleshooting formula errors. With a firm grasp on these invaluable skills and knowledge, you are now equipped to elevate your Salesforce expertise and unleash the full potential of this powerful platform. Whether it’s streamlining complex calculations or rectifying formula discrepancies, mastering advanced formulas and effectively troubleshooting errors will undoubtedly empower you to navigate Salesforce with confidence and efficiency, making a significant impact on your productivity and success. Embrace the possibilities that await you as you level up with advanced formulas and sharpen your ability to handle formula challenges in the dynamic world of Salesforce.
FAQ s
- What is the limitation of formulas in Salesforce?
Note The maximum number of displayed characters after an evaluation of a formula expression is 1,300. Compile size limit —Formula fields can’t exceed 15,000 bytes when compiled. The compile size is the size of the formula (in bytes) including all of the fields, values, and formulas it references.
2. How do I test a formula field in Salesforce?
- Define field.
- Write formula.
- Add field to layout.
- Create records to determine if the formula is functioning properly.
- Tweak formula if necessary.
- Modify records to test formula.
3.Can we use formula fields in validation rules?
Noticeably a validation rule can contain a formula or expression, which evaluates the data in single or multiple fields and returns a value of “True” or “False”. In case of an invalid value, Validation rules include an error message to display to the user.
For More Blogs; Click Here
Author Bio
