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

  • What is the error message that you're getting? What have you tried so far? – Boris Bachovski Jul 30 '15 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 '15 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 '15 at 4:24
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.

  • 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 '15 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 '15 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 '15 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 '15 at 4:57
  • I do have one final question, what do the ampersands designate as operators in the logic string? – Bermsy Jul 30 '15 at 5:00

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.