You are here:
CASE
Checks a given expression against a series of values. If the expression is equal to a
value, returns the corresponding result. If it isn't equal to any of the values, returns the
else_result.
Use
CASE(expression,value1,
result1, value2, result2,...,
else_result) and replace expression with the
field or value you want compared to each specified value. Replace each value and result with the
value that must be equivalent to return the result entry. Replace else_result
with the value you want returned when the expression doesn't equal any values.
Tips
- Be sure your value1, value2... expressions are the same data type.
- Be sure your result1, result2... expressions are the same data type.
- CASE functions can’t contain functions that return true or false. Instead, make true or
false expressions return numbers such
as:
CASE(1, IF(ISPICKVAL (Term__c, "12"), 1, 0), 12 * Monthly_Commit__c, IF(ISPICKVAL(Term__c, "24"), 1, 0), 24 * Monthly_Commit__c, 0)In this formula, Term is a picklist field that is multiplied by the Monthly Commit whenever it contains the value 1 for true.
- The else_result value is required.
- CASE functions return an error whenever any of the expressions return an error, regardless
of which one must be returned. For example,
CASE(Field__c,"Partner", "P", "Customer", "C", LEFT(Field__c, -5))returns an error even if the value of the field is “Partner” or “Customer” because the last statement is illogical. - If the field in your CASE function is blank, it returns your else_result
value. For example, this formula:
CASE(Days_Open__c, 3, "Reassign", 2, "Assign Task", "Maintain")displays Maintain if the Days Open field is blank, 0, or any value other than 2 or 3. - Use CASE functions to determine if a picklist value is equal to a particular value. For
example the formula
CASE(Term__c, "12", 12 * Monthly_Commit__c, "24", 24 * Monthly_Commit__c, 0)multiplies the Monthly Commit amount by 12 whenever the Term is 12 or multiplies the Monthly Commit amount by 24 whenever the Term is 24. Otherwise, the result is zero.
Use this example of a custom formula field called Days Open to display different text depending on the number of days a case has been open:
CASE(Days_Open__c, 3,
"Reassign", 2, "Assign Task", "Maintain")This text is displayed.
- “Reassign” for any case open three days.
- “Assign Task” for any case open two days.
- “Maintain” for all other cases.
This formula field displays the month of the last activity or None if there are no activities.
CASE(MONTH(LastActivityDate),
1, "January",
2, "February",
3, "March",
4, "April",
5, "May",
6, "June",
7, "July",
8, "August",
9, "September",
10, "October",
11, "November",
12, "December",
"None")Use this default value formula to insert a different discount rate on an opportunity based on the department of the person creating the opportunity.
CASE(User.Department, "IT", 0.25, "Field", 0.15, 0)In this example, the formula inserts a discount rate of 25% on any opportunity created by a user in the IT department or 15% on any opportunity created by someone in the Field department. A zero is applied if the creator doesn't belong to either of these departments. This is a custom percent field on opportunities that uses the standard user field Department.
You want to associate a product with its language so that your users know the type of documentation or adapter to include. Use this default value formula to automatically set the language of a product based on the country of the user creating the product. In this example, the default value is Japanese if the user's country is Japan and English if the user's country is US. If neither is true, the default value unknown is inserted into the Product Language field.
CASE($User.Country , "Japan", "Japanese", "US", "English","unknown")