2

Trying to utilize a picklist value to modify a territory assignment identified by state.

My original formula, very simple:

IF(LEN( {!State})=0, "None",
IF(CONTAINS("AK:AZ:CA:HI:NV:NM:OR:UT:WA", {!State}), "West",
IF(CONTAINS("CO:ID:MT:KS:OK:TX:WY", {!State}), "Central",
IF(CONTAINS("CT:ME:MA:NH:NY:PA:RI:VT", {!State}), "East",
IF(CONTAINS("AL:AR:DC:DE:FL:GA:KY:LA:MD:MS:NC:NJ:SC:TN:VA:WV", {!State}), "South",
IF(CONTAINS("IL:IN:IA:MI:MN:MO:NE:ND:OH:SD:WI", {!State}), "North", "Other"))))))

Now I need to put in a modifier by way of a picklist field that says if the Industry value = Retail then it will utilize a different model for territory assignment, example:

IF(CONTAINS("AK:AZ:CA:HI:NV:NM:OR:UT:WA", {!State}), "West",
IF(CONTAINS("IL:IN:IA:MI:MN:MO:NE:ND:OH:SD:WI:CO:ID:MT:KS:OK:TX:WY:AL:AR:DC:LA:MD:MS", {!State}), "Central",
IF(CONTAINS("CT:ME:MA:NH:NY:PA:RI:VT:DE:FL:GA:KY:NC:NJ:SC:TN:VA:WV", {!State}), "East", "Other")))

I am having trouble adding the picklist modifier due to errors return results. Can Someone offer some assistance?

EDIT : here's the different versions I tried and the errors

Version 1 

AND( ISPICKVAL(Industry,"Retail"),         
CONTAINS(CASE("IL:IN:IA:MI:MN:MO:NE:ND:OH:SD:WI:CO:ID:MT:KS:OK:TX:WY:AL:AR:DC:LA:MD:MS", State), 
"Central", "None"))

Error: Incorrect number of parameters for function 'CASE()'. Expected 2, received

Version 2

 AND( ISPICKVAL(Industry,"Retail"),
 CONTAINS ( TEXT("IL:IN:IA:MI:MN:MO:NE:ND:OH:SD:WI:CO:ID:MT:KS:OK:TX:WY:AL:AR:DC:LA:MD:
 MS", State), "Central", "None"))

Error: Incorrect number of parameters for function 'TEXT()'. Expected 1, received 2

Improve this question
3
  • What is the error message that you're getting? What have you tried so far?
    – Boris Bachovski
    Jul 30, 2015 at 4:05
  • AND( ISPICKVAL(Industry,"Retail"),CONTAINS (CASE("IL:IN:IA:MI:MN:MO:NE:ND:OH:SD:WI:CO:ID:MT:KS:OK:TX:WY:AL:AR:DC:LA:MD:MS", State), "Central", "None")) Error: Incorrect number of parameters for function 'CASE()'. Expected 2, received 2 AND( ISPICKVAL(Industry,"Retail"),CONTAINS (TEXT("IL:IN:IA:MI:MN:MO:NE:ND:OH:SD:WI:CO:ID:MT:KS:OK:TX:WY:AL:AR:DC:LA:MD:MS", State), "Central", "None")) Error: Incorrect number of parameters for function 'TEXT()'. Expected 1, received 2
    – Bermsy
    Jul 30, 2015 at 4:15
  • AND( ISPICKVAL(Industry = Retail),CONTAINS (TEXT("IL:IN:IA:MI:MN:MO:NE:ND:OH:SD:WI:CO:ID:MT:KS:OK:TX:WY:AL:AR:DC:LA:MD:MS", State), "Central", "None")) Error: Field Industry is a picklist field. Picklist fields are only supported in certain functions.
    – Bermsy
    Jul 30, 2015 at 4:24

1 Answer 1

Reset to default
2

Try the following:

IF(ISPICKVAL(Industry, "Retail") && CONTAINS("IL:IN:IA:MI:MN:MO:NE:ND:OH:SD:WI:CO:ID:MT:KS:OK:TX:WY:AL:AR:DC:LA:MD:MS", ShippingState), "Central", "None")

There is no State field on the Account, it's either BillingState or ShippingState.

Improve this answer
6
  • You are correct as far as the fields, this is for the Lead object, I will migrate to the Account and Contact object at a later date. Let me check this out and see if it returns the desired results.
    – Bermsy
    Jul 30, 2015 at 4:37
  • I assumed it's Account :) Also there is no need to wrap the field name into an expression {!} when used within a function
    – Boris Bachovski
    Jul 30, 2015 at 4:38
  • Bachovski, worked brilliantly, I just had to remember that every formula function in SFDC is linear so that item had to be at the top of the list. Thank you very much for the insight. I will update the formula to rid it of the { ! } as well. How do I give you credit for the advice?
    – Bermsy
    Jul 30, 2015 at 4:55
  • I'm glad it helped and you resolved the problem. You can easily extend it to cater for the rest of your cases now. You can upvote and accept this answer in order to give credit and close the question ;)
    – Boris Bachovski
    Jul 30, 2015 at 4:57
  • I do have one final question, what do the ampersands designate as operators in the logic string?
    – Bermsy
    Jul 30, 2015 at 5:00

You must log in to answer this question.

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