function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
dolphindolphin 

IF-AND-OR FUNCTION FOR ISPICKVAL

Hello Everybody,
 
I wonder if someone ever seen this situation. I am relatively new to SF, hope somebody can help.
 
I have different selling services (26) and the fees are changing according to the billing cycle (yearly or monthly) and their types.
 
Therefore, I want to create a rule that is going to relate: the ispickval (Service) and the BC (Y or M)
 
I tried with the functions IF and AND,
 
IF(
AND(ISPICKVAL(A),ISPICKVAL(Y),Fees1),
AND(ISPICKVAL (A),ISPICKVAL(M),Fees2),
AND(ISPICKVAL(B),ISPICKVAL(y), Fees3),
AND...,
null)
 
I have a report that something is missing, but I can not find what and how I should do that.
 
If someone can help!
 
Thanks in advance. <Dolphin
Best Answer chosen by Admin (Salesforce Developers) 
Kent ManningKent Manning
Hi Dolphin,
You need a series of nested if statements.  Also the ISPICKVALUE function needs a picklist field and a literal text string to function properly.

So your formula need to be something like:

IF(AND(ISPICKVALUE(service_field__c, "A"), ISPICKVALUE(BillingCycle__c, "Y"), Fees1,
   IF(AND(ISPICKVALUE(service_field__c, "A"), ISPICKVALUE(BillingCycle__c, "M"), Fees2,
       IF(AND(ISPICKVALUE(service_field__c, "B"), ISPICKVALUE(BillingCycle__c, "Y"), Fees3,null)))

You may run into formula length limitations if you try to put all 26 of these into one formula so you're going to have to be creative in how you accomplish the task.  You might also try the CASE function as it's a bit cleaner and easier to write.