Table of Contents
A genius at formulas or not, all of us will eventually face an error. And most likely, it’s going to be a Syntax Error. And that’s okay as long as we figure out what’s happening, rectify it, and get started on the next formula!
So, what are syntax errors? It can be a missed parenthesis, an extra comma, or a spelling or punctuation error, to name a few. To ensure the syntax of your formula is correct, click on Check Syntax right below the text area where you write your formula.
So, we’ve got an error. How do we figure out what’s wrong? I can help you with plausible scenarios. Let’s get started!
1. Missing or Extra Parentheses
You’ll likely face this error while working with logical statements such as IF, AND, OR, etc. Let’s take a look at a few examples.
- This checkbox formula determines if an Opportunity is a Large Opportunity.
Although the error says Extra ‘.’, it’s the extra closing parenthesis after the first argument that’s causing the error.
- This is the same formula as the previous example. But the extra parenthesis at the end of the expression is causing this error.
- We’re working on the same formula, but the closing parenthesis of the IF function is missing here, leading to an error.
2. Field Does Not Exist
This error can often occur, especially if you’re not using the Insert Field function in the Formula Editor.
- For example, the following error says that the field does not exist. We were meant to reference the field, Height__c, and not Hieght__c.
3. Incorrect Number of Parameters
An incorrect number of parameters for a function will lead to an error. If you’re unsure how many parameters a function requires, refer to this documentation on Salesforce Help.
- In this example, we use a CASE function to calculate a number field based on the Priority of a Case. This function is missing the else_result, i.e., the final argument, the case that’s processed if all other cases fail.
4. Unknown Function
It’s easy to enter an incorrect function if you’re typing it out instead of using Insert Selected Function. You could either misspell a function or enter a function that does not exist.
- Here, we’re calculating the age of a case, but instead of using the function DATEVALUE(), we misspelled the function and entered DATEVAL().
5. Working with Different Data Types
Before writing your formula, determine the data type of the expected result. If the formula returns a Date value and you write a formula that returns a Date/Time value, you will face this error. Make the appropriate conversions when necessary when working with conflicting data types.
- In this example, the Formula Return Type was set to Date, but adding or subtracting two Date values resulted in a whole number and not a Date, which naturally led to an error.
6. Length Limit
Formulas have a limit of 3900 characters ( including spaces, return characters, and comments). You can shorten formulas using the pointers mentioned below, but if that doesn’t help, use Helper Fields to trim your code.
- Use the AND operator ( && ) instead of the AND() function. The same goes for OR() functions too.
- Use the CASE() function instead of a nested IF().
- Shorten field names and comments.
7. Compile Size Limit
Formula fields cannot surpass 5,000 bytes when compiled. So what does that mean? The compile size is equal to the size of the formula in bytes, and it includes all the fields, values, and formulas it references. A few functions can significantly increase the compile size, TEXT, DATE, DATETIMEVALUE, and DATEVALUE, to name a few.
Referencing formulas or helper fields does not make much of a difference here as the compile size includes the compile size of those referenced fields too. Minimizing those references can help reduce the compile size.
If your code exceeds the compile size limit, the error would be similar to this 🡪 “Compiled formula is too big to execute (14,844 characters). Maximum size is 5,000 characters.”
There are some ways to work around this problem:
- Reduce the number of references to other fields and formulas
- Minimize the number of times formula fields are referenced
- Revise your picklist fields
- Look at the solution from a different perspective, preferably a shorter one.
- If all options fail, use a workflow update instead.