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"))))))
3
  • Do you just want all the conditions to be true? One picklist field cannot concurrently hold two values. – Adrian Larson Sep 23 '16 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 Sep 23 '16 at 23:40
  • Can you post the verbatim compile error? – Adrian Larson Sep 23 '16 at 23:45
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"
)
10
  • 1
    ... and I saw your edit right after I submitted what you just wrote. – sfdcfox Sep 23 '16 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 Sep 23 '16 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 Sep 23 '16 at 23:58
  • I dig it. You gonna be at #DF16? – Adrian Larson Sep 24 '16 at 0:01
  • Yes, I'm going to be there, and I can't wait! – sfdcfox Sep 24 '16 at 0:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.