+ Start a Discussion
LuckyLucky 

Changing IF Statement to CASE

Hi I want to convert following IF condition to CASE in Formula field. Please Help me out..
IF(( RecordType.Name  = "Business Account") && (BillingCountry  != "CAN"),Text(1000),IF(( RecordType.Name  = "Business Account") && (BillingCountry  = "CAN"),Text(1100),IF(( RecordType.Name  = "Business Account - UK"),Text(1200),IF(( RecordType.Name  = "ETS/WMI") &&   CONTAINS(OwnerId__r.Profile.Name, "ETS"),Text(1500),IF(( RecordType.Name  = "ETS/WMI") &&   CONTAINS(OwnerId__r.Profile.Name, "WMI"),Text(1501),IF(( RecordType.Name  = "Residential Account") && (BillingCountry  != "CAN"),Text(1000),IF(( RecordType.Name  = "Residential Account") && (BillingCountry  = "CAN"),Text(1100),IF(( RecordType.Name  = "Residential Account - UK"),Text(1200),null))))))))
Thanks in Advance.
 
Best Answer chosen by Lucky
Manuel Jiménez GonzálezManuel Jiménez González

I think this is as efficient as you can get:

IF(( RecordType.Name = "Business Account"),IF((BillingCountry  != "CAN"), Text(1000), Text(1100)) ,
IF(( RecordType.Name = "Business Account - UK"),Text(1200),
IF(( RecordType.Name = "ETS/WMI") &&   CONTAINS(OwnerId__r.Profile.Name, "ETS"),Text(1500),
IF(( RecordType.Name = "ETS/WMI") &&   CONTAINS(OwnerId__r.Profile.Name, "WMI"),Text(1501),
IF(( RecordType.Name = "Residential Account"), IF((BillingCountry  != "CAN"), Text(1000), Text(1100)),
IF(( RecordType.Name = "Residential Account - UK"),Text(1200),null))))))))

All Answers

James LoghryJames Loghry
Lucky,

I would focus less on "How do I convert this to a CASE" formula and more on "How can I simplify this formula to use as little logic as possible".  From what you posted, you're checking the BillingCountry = "CAN" or != "CAN" in several places, and can probably benefit from some cleanup in that regard.  I'd recommend searching for truth tables on google, write a truth table out for your formula, and then you should be able to figure out the if statements you need to simplify your formula.

 
Manuel Jiménez GonzálezManuel Jiménez González

I think this is as efficient as you can get:

IF(( RecordType.Name = "Business Account"),IF((BillingCountry  != "CAN"), Text(1000), Text(1100)) ,
IF(( RecordType.Name = "Business Account - UK"),Text(1200),
IF(( RecordType.Name = "ETS/WMI") &&   CONTAINS(OwnerId__r.Profile.Name, "ETS"),Text(1500),
IF(( RecordType.Name = "ETS/WMI") &&   CONTAINS(OwnerId__r.Profile.Name, "WMI"),Text(1501),
IF(( RecordType.Name = "Residential Account"), IF((BillingCountry  != "CAN"), Text(1000), Text(1100)),
IF(( RecordType.Name = "Residential Account - UK"),Text(1200),null))))))))
This was selected as the best answer
SteveMoSteveMo
+1 JamesL 

Give this a try:
IF(
AND(
BillingCountry  <> "CAN",
OR(RecordType.Name = "Business Account",RecordType.Name = "Residential Account"))
"1000",
IF(
AND(
BillingCountry  = "CAN",
OR(RecordType.Name = "Business Account",RecordType.Name = "Residential Account"))
"1100",
IF(
OR( RecordType.Name = "Business Account - UK",RecordType.Name = "Residential Account - UK"),
"1200",
IF(AND(RecordType.Name = "ETS/WMI", CONTAINS(OwnerId__r.Profile.Name, "ETS")),"1500",
IF(AND(RecordType.Name = "ETS/WMI", CONTAINS(OwnerId__r.Profile.Name, "WMI")),"1501",
NULL)))))