function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Sarah MuirSarah Muir 

Help With a Nested If and Case Formula

Hi everyone, 

I'm trying to set up a formula field to return specific text and I'm hoping it's possible to use nested if statements with multiple case functions.

So, if the value in a specific field is one value, use the first case function and, if it's another, use the other case function. 

Here's what I'm trying to use but can't seem to get the syntax to work. 

IF(North_American_Parent_Account__c = TRUE,
CASE(Corresponding_User_Role__c,"Corporate","Corporate (In North America)","Student","Student (In North America)","Educator","Educator (In North America)","Bookstore","Bookstore (In North America)",
IF(North_American_Parent_Account__c = FALSE,
CASE(Corresponding_User_Role__c,"Corporate","Corporate (Outside North America)","Student","Student (Outside North America)","Educator","Educator (Outside North America)","Bookstore","Bookstore (Outside North America)"
,""
))))

Any help would be much appreciated!

Best Answer chosen by Sarah Muir
ryanschierholzryanschierholz
Well, the first thing, is that you don't need the second IF statement. By design, the third argument in an if statement handles the 'else' condition. IF(condition, result if true, result if false). furthermore, for a boolean statement, you don't need to write out "= true" and last, the CASE statement needs an 'else' line, if none of the items are matched. I used "" below.

Try: 
IF(North_American_Parent_Account__c,
CASE(Corresponding_User_Role__c,
"Corporate","Corporate (In North America)",
"Student","Student (In North America)",
"Educator","Educator (In North America)",
"Bookstore","Bookstore (In North America)",
""),
CASE(Corresponding_User_Role__c,"Corporate",
"Corporate (Outside North America)",
"Student","Student (Outside North America)",
"Educator","Educator (Outside North America)",
"Bookstore","Bookstore (Outside North America)",
""))

 

All Answers

ryanschierholzryanschierholz
Well, the first thing, is that you don't need the second IF statement. By design, the third argument in an if statement handles the 'else' condition. IF(condition, result if true, result if false). furthermore, for a boolean statement, you don't need to write out "= true" and last, the CASE statement needs an 'else' line, if none of the items are matched. I used "" below.

Try: 
IF(North_American_Parent_Account__c,
CASE(Corresponding_User_Role__c,
"Corporate","Corporate (In North America)",
"Student","Student (In North America)",
"Educator","Educator (In North America)",
"Bookstore","Bookstore (In North America)",
""),
CASE(Corresponding_User_Role__c,"Corporate",
"Corporate (Outside North America)",
"Student","Student (Outside North America)",
"Educator","Educator (Outside North America)",
"Bookstore","Bookstore (Outside North America)",
""))

 
This was selected as the best answer
Sarah MuirSarah Muir
Worked perfectly! Thank you so much for the quick response!
Unai Garcia Ramos 9Unai Garcia Ramos 9
That formula seems awfully complex for what you're trying to achieve though. Since your only difference here is depending on "North American Parent Account" say In or Outside, you could do something like:
Corresponding_User_Role__c + " (" + IF(North_American_Parent_Account__c,"In","Outside")+" North America)"