1

I'm having trouble with a IF formula that I am asking to search for these specific values to return two types of students but I am receiving an error that I am missing part of my expression. Can anyone explain what I am doing wrong here? I simply want it to meet all these values and produce "international" or "domestic" below:

Student type is a picklist field

Status is a picklist field

Owner is a text/lookup to a user

IF(ISPICKVAL( Student_Type__c ,"First-time Freshman"),
IF(ISPICKVAL( Student_Type__c ,"First-time Freshman with College Work"),
IF(ISPICKVAL( Status__c   ,"Freshman"),
IF(ISPICKVAL( Status__c   ,"Freshman with College Work"),
IF(CONTAINS("Melissa:Holly:Robert",Owner__c), "International","Domestic"))))))
Improve this question
3
  • Do you just want all the conditions to be true? One picklist field cannot concurrently hold two values.
    – Adrian Larson ♦
    Commented Sep 23, 2016 at 23:16
  • Logically it should read out like this :If Type=Firstime freshman or first time freshman with college work and Status = freshman or freshamn with college work, and Owner is Melissa or holly or robert, then International, else Domestic
    – Geo
    Commented Sep 23, 2016 at 23:40
  • Can you post the verbatim compile error?
    – Adrian Larson ♦
    Commented Sep 23, 2016 at 23:45

1 Answer 1

Reset to default
3

Your formula will never return true. One picklist field can never simultaneously have two options selected.

You haven't given the error text, which makes the specific problem harder to identify, but I think the following might more closely represent what you are looking for:

IF(
    AND(
        // all inner clauses must be true for the whole to be true
        OR(
            // either inner clause must be true for the whole to be true
            ISPICKVAL(PicklistField1__c, "Option 1"),
            ISPICKVAL(PicklistField1__c, "Option 2")
        ),
        OR(
            // either inner clause must be true for the whole to be true
            ISPICKVAL(PicklistField2__c, "Option A"),
            ISPICKVAL(PicklistField2__c, "Option B")
        ),
        CONTAINS("X:Y:Z", Owner__c)
    ),
    "International", // value if all the above is true
    "Domestic" // value if all the above is false
)

As I learned from @sfdcfox, you can shave some compile size with some fancy restructuring:

IF(3 =
    CASE(PicklistField1__c, "Option 1", 1, "Option 2", 1, 0) +
    CASE(PicklistField2__c, "Option A", 1, "Option B", 1, 0) +
    IF(CONTAINS("X:Y:Z", Owner__c), 1, 0), "International", "Domestic"
)
Improve this answer
10
  • 1
    ... and I saw your edit right after I submitted what you just wrote.
    – sfdcfox ♦
    Commented Sep 23, 2016 at 23:49
  • 1
    I debated whether to lead with the 3 or not. I think it does look better that way. And I'm glad you taught me that one. You know all the best tricks. :)
    – Adrian Larson ♦
    Commented Sep 23, 2016 at 23:51
  • 1
    I know I don't know all the tricks, but optimization is one of my life's passions. I like to see how something works, then I like to make it work better.
    – sfdcfox ♦
    Commented Sep 23, 2016 at 23:58
  • I dig it. You gonna be at #DF16?
    – Adrian Larson ♦
    Commented Sep 24, 2016 at 0:01
  • Yes, I'm going to be there, and I can't wait!
    – sfdcfox ♦
    Commented Sep 24, 2016 at 0:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .