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
Chris McCoy 6Chris McCoy 6 

If statement with two variables and Case function.

I'm new to Salesforce and I am trying to create a formula that will look at two variables and determine a cost percentage.

Field 1 is a status (picklist) and Field2 is either null or Available(picklist). For Statuses PROC & UNCO the second variable isn't needed - only for the EXIS status.  The cost percentage is then based on either the one variable or the combination and I used CASE function to determine the percentage that goes into Field3. 
The formula works great for anything in PROC or UNCO status - but nothing happens if it is in EXIS status. Any suggestions will be greatly appreciated.
IF( ISPICKVAL(Account.Field1_Status_NA__c, "PROC") ,
CASE(Account.Field3_NA__c,"A", 15/100,"B",20/100,"C",20/100,"D",20/100,"E",25/100, "F",25/100,"G", 30/100,0),

IF(ISPICKVAL(Account.Field1_Status_NA__c, "UNCO") ,
CASE(Account.Field3_NA__c,"A", 15/100,"B",20/100,"C",20/100,"D",20/100,"E",25/100, "F",25/100,"G", 30/100,0),

IF(AND(ISPICKVAL(Account.Field1_Status_NA__c, "EXIS") , ISPICKVAL( Account.Field2__c, " ")) ,
CASE(Account.Field3_NA__c,"A", 10/100 ,"B",15/100, "C",15/100,"D",15/100,"E",25/100, "F",25/100,"G", 0,0),

IF(AND(ISPICKVAL(Account.Field1_Status_NA__c,"EXIS"), ISPICKVAL( Account.Field2__c, "Available" )),
CASE(Account.Field3_NA__c,"A", 5/100,"B",10/100,"C",10/100,"D",10/100,"E",15/100, "F",15/100,"G",0, 0), Null) ) ) )
 
Best Answer chosen by Chris McCoy 6
Parker EdelmannParker Edelmann
IF(OR(ISPICKVAL(Account.Field1_Status_NA__c, "UNCO"), ISPICKVAL(Account.Field1_Status_NA__c, "PROC") ),
CASE(Account.Field3_NA__c,"A", 15/100,"B",20/100,"C",20/100,"D",20/100,"E",25/100, "F",25/100,"G", 30/100,0),

IF(ISPICKVAL(Account.Field1_Status_NA__c, "EXIS") ,IF(ISPICKVAL( Account.Field2__c, "Available") ,CASE(Account.Field3_NA__c,"A", 5/100,"B",10/100,"C",10/100,"D",10/100,"E",15/100, "F",15/100,"G",0, 0),

CASE(Account.Field3_NA__c,"A", 10/100 ,"B",15/100, "C",15/100,"D",15/100,"E",25/100, "F",25/100,"G", 0,0)), null))

All Answers

JeffreyStevensJeffreyStevens
When you're checking for the Account.Field2__c value - have you tried "" instead of " "?  
But that doesn't explain why the Available doesn't work. 
Parker EdelmannParker Edelmann
Try this:

IF(OR(ISPICKVAL(Account.Field1_Status_NA__c, "UNCO"), ISPICKVAL(Account.Field1_Status_NA__c, "PROC") ),
CASE(Account.Field3_NA__c,"A", 15/100,"B",20/100,"C",20/100,"D",20/100,"E",25/100, "F",25/100,"G", 30/100,0),

IF(AND(ISPICKVAL(Account.Field1_Status_NA__c, "EXIS") , ISBLANK( Account.Field2__c)) ,
CASE(Account.Field3_NA__c,"A", 10/100 ,"B",15/100, "C",15/100,"D",15/100,"E",25/100, "F",25/100,"G", 0,0),

IF(AND(ISPICKVAL(Account.Field1_Status_NA__c,"EXIS"), ISPICKVAL( Account.Field2__c, "Available" )),
CASE(Account.Field3_NA__c,"A", 5/100,"B",10/100,"C",10/100,"D",10/100,"E",15/100, "F",15/100,"G",0, 0), Null) ) ) )

Use ISBLANK() istead of ISPICKVAL, that's what you're looking for, unless you have a picklist option whose value is one space.
Also, the values inside the case function were the same if your picklist was UNCO or PROC, so I shortened the formula a little for you.
Hope this helps,
Parker

 
Chris McCoy 6Chris McCoy 6
I have tried that - I also entered another value (Unavailable) instead of " " - but it doesn't seem to pick up if I have a second criteria.
Parker EdelmannParker Edelmann
Okay, that's odd. Did you make "Unavailable" a picklist value before you tried that? Just for grins, can you try where I bolded in the formula, !ISPICKVAL( Account.Field2__c) Note the exclamation point.
Chris McCoy 6Chris McCoy 6
If I substitute ISBLANK for IS PICKVAL I get an error telling me this field is a picklist field and they are only supported in certain functions. I had tried Unavailable earlier - before I submitted this question.
Parker EdelmannParker Edelmann
IF(OR(ISPICKVAL(Account.Field1_Status_NA__c, "UNCO"), ISPICKVAL(Account.Field1_Status_NA__c, "PROC") ),
CASE(Account.Field3_NA__c,"A", 15/100,"B",20/100,"C",20/100,"D",20/100,"E",25/100, "F",25/100,"G", 30/100,0),

IF(ISPICKVAL(Account.Field1_Status_NA__c, "EXIS") ,IF(ISPICKVAL( Account.Field2__c, "Available") ,CASE(Account.Field3_NA__c,"A", 5/100,"B",10/100,"C",10/100,"D",10/100,"E",15/100, "F",15/100,"G",0, 0),

CASE(Account.Field3_NA__c,"A", 10/100 ,"B",15/100, "C",15/100,"D",15/100,"E",25/100, "F",25/100,"G", 0,0)), null))
This was selected as the best answer
Parker EdelmannParker Edelmann
Does that work for you?
Chris McCoy 6Chris McCoy 6
YES! Thank you so much - I had too many ) ) and had to go back through to make sure I had them correct, but I now get the results that I expected.
Parker EdelmannParker Edelmann
Glad that helped! I was hoping I got the parentheses right, I was on a time crunch and didn't have time to make a final check.

Thanks for posting your question,
Parker