Here it is:
IF(ProductCode__c ="E-MTU-E"||ProductCode__c ="E-MTU-B",0,
IF(Opportunity.Account.EU_member_state__c,0,
CASE(Opportunity.Account.BillingCountry,
"United Kingdom",0.2,
"Ireland",0.23,
"South Africa",0.14,
"Korea, North",0.1,
"Korea, South",0.1,
"Canada",
CASE(Opportunity.Account.BillingState,
"Alberta",0.05,
"AB",0.05,
"British Columbia",0.05,
"BC",0.05,
"Manitoba",0.05,
"MB",0.05,
"New Brunswick",0.15,
"NB",0.15,
"Newfoundland and Labrador",0.15,
"NL",0.15,
"Northwest Territories",0.05,
"NT",0.05,
"Nova Scotia",0.15,
"NS",0.15,
"Nunavut",0.05,
"NU",0.05,
"Ontario",0.13,
"ON",0.13,
"Prince Edward Island",0.15,
"PE",0.15,
"Quebec",0.05,
"QC",0.05,
"Saskatchewan",0.05,
"SK",0.05,
"YT",0.05,
"Yukon",0.05,0
),0
)
)
)
I now need to add anover level of complexity, which I can't work out. If the custom lookup field Opportunity.Agent_Organisation__c is blank, the I want the formula to work as above. If it is not blank, I want all the Opportunity.Account.Billing details in the CASE function to refer to the Opportunity.Agent_Organisation__r.Billing fields, like this:
CASE(Opportunity.Agent_Organisation__r.BillingCountry,
"United Kingdom",0.2,
"Ireland",0.23,
"South Africa",0.14,
"Korea, North",0.1,
"Korea, South",0.1,
"Canada",
CASE(Opportunity.Agent_Organisation__r.BillingState,
"Alberta",0.05,
"AB",0.05,
"British Columbia",0.05,
"BC",0.05,
"Manitoba",0.05,
"MB",0.05, etc...
The trouble is, I can't work out how to join the two formulas. I've tried the below but but get Error: Incorrect number of parameters for function 'IF()'. Expected 3, received 2
Where have I gone wrong?
IF(ProductCode__c ="E-MTU-E"||ProductCode__c ="E-MTU-B",0,
IF(Opportunity.Account.EU_member_state__c,0,
IF(Opportunity.Agent_Organisation__c="",
CASE(Opportunity.Account.BillingCountry,
"United Kingdom",0.2,
"Ireland",0.23,
"South Africa",0.14,
"Korea, North",0.1,
"Korea, South",0.1,
"Canada",
CASE(Opportunity.Account.BillingState,
"Alberta",0.05,
"AB",0.05,
"British Columbia",0.05,
"BC",0.05,
"Manitoba",0.05,
"MB",0.05,
"New Brunswick",0.15,
"NB",0.15,
"Newfoundland and Labrador",0.15,
"NL",0.15,
"Northwest Territories",0.05,
"NT",0.05,
"Nova Scotia",0.15,
"NS",0.15,
"Nunavut",0.05,
"NU",0.05,
"Ontario",0.13,
"ON",0.13,
"Prince Edward Island",0.15,
"PE",0.15,
"Quebec",0.05,
"QC",0.05,
"Saskatchewan",0.05,
"SK",0.05,
"YT",0.05,
"Yukon",0.05,
IF(Opportunity.Agent_Organisation__c!="",
CASE(Opportunity.Agent_Organisation__r.BillingCountry,
"United Kingdom",0.2,
"Ireland",0.23,
"South Africa",0.14,
"Korea, North",0.1,
"Korea, South",0.1,
"Canada",
CASE(Opportunity.Agent_Organisation__r.BillingState,
"Alberta",0.05,
"AB",0.05,
"British Columbia",0.05,
"BC",0.05,
"Manitoba",0.05,
"MB",0.05,
"New Brunswick",0.15,
"NB",0.15,
"Newfoundland and Labrador",0.15,
"NL",0.15,
"Northwest Territories",0.05,
"NT",0.05,
"Nova Scotia",0.15,
"NS",0.15,
"Nunavut",0.05,
"NU",0.05,
"Ontario",0.13,
"ON",0.13,
"Prince Edward Island",0.15,
"PE",0.15,
"Quebec",0.05,
"QC",0.05,
"Saskatchewan",0.05,
"SK",0.05,
"YT",0.05,
"Yukon",0.05,0
),0
)
)
)
)
)
))
9 件の回答
I've fixed it. The formula that feeds the EU Member State field has been replaced with a workflow and field update which brings the compile size down.
Thanks for your help everyone.
