You need to sign in to do that
Don't have an account?
3rd time Posting - Formula just doesn't want to work
Earlier we fixed the beginning part of my formula and it is now working for commercial channels (not listed). When I run it for Domestic or Government channels it just assigns all of them to LE West - can anyone tell why from what is listed below?
Thanks so much for your help with this.
IF (ISPICKVAL(Channel__c , "Domestic") || ISPICKVAL(Channel__c , "Government") && CONTAINS ("AK:AZ:CA:CO:HI:ID:KS:MT:NV:NM:OR:UT:WA:WY", ShippingState), "LE West",
IF (ISPICKVAL(Channel__c , "Domestic") || ISPICKVAL(Channel__c , "Government") && CONTAINS ("CT:DC:DE:FL:GA:ME:MA:MD:NH:NJ:NY:NC:PA:RI:SC:VT:VA:WV:PR", ShippingState), "LE East",
IF (ISPICKVAL(Channel__c , "Domestic") || ISPICKVAL(Channel__c , "Government") && CONTAINS ("AL:AR:IL:IN:IA:KS:KY:LA:MI:MN:MS:MO:NE:ND:OH:OK:SD:TN:TX:WI", ShippingState), "LE Central", NULL))))))))))))
I copied this formula and used it in a new formula field.
It worked fine with out any issues.
Check your pick list values whether they are same or not.
All Answers
Just try this formula.
I just modified the operator "||" to "OR" it worked.
IF(OR (ISPICKVAL(Channel__c , "Domestic"),ISPICKVAL(Channel__c , "Government"))&& CONTAINS ("AK:AZ:CA:CO:HI:ID:KS:MT:NV:NM:OR:UT:WA:WY", ShippingState), "LE West",
IF(OR (ISPICKVAL(Channel__c , "Domestic"),ISPICKVAL(Channel__c , "Government"))&& CONTAINS ("CT:DC:DE:FL:GA:ME:MA:MD:NH:NJ:NY:NC:PA:RI:SC:VT:VA:WV:PR", ShippingState), "LE East",
IF(OR (ISPICKVAL(Channel__c , "Domestic"),ISPICKVAL(Channel__c , "Government"))&& CONTAINS ("AL:AR:IL:IN:IA:KS:KY:LA:MI:MN:MS:MO:NE:ND:OH:OK:SD:TN:TX:WI", ShippingState), "LE Central", NULL)))
Hi, I updated the formula and it still returns LE West.
Here is the entire formula
IF (ISPICKVAL(Channel__c , "Commercial") && CONTAINS ("AK:AZ:CA:CO:HI:ID:MT:NV:NM:OR:UT:WA:WY", ShippingState), "Commercial Central/West",
IF (ISPICKVAL(Channel__c , "Commercial") && CONTAINS ("ND:SD:MN:WI:MO", ShippingState), "Commercial North Central",
IF(ISPICKVAL(Channel__c, "Commercial") && CONTAINS("TX,AR,LA,OK", ShippingState), "Commercial TALO",
IF (ISPICKVAL(Channel__c , "Commercial") && CONTAINS ("NE:KY:KS:MI:IA:IL:IN:OH", ShippingState), "Commercial Midwest",
IF (ISPICKVAL(Channel__c , "Commercial") && CONTAINS ("ME:CT:DE:FL:MD:MA:WV:NH:NJ:NY:PA:RI:VT:VA:DC", ShippingState), "Commercial East",
IF (ISPICKVAL(Channel__c , "Commercial") && CONTAINS ("NC:SC", ShippingState), "Commercial NC/SC",
IF (ISPICKVAL(Channel__c , "Commercial") && CONTAINS ("TN:GA:AL:MS", ShippingState), "Commercial South East",
IF(ISPICKVAL(Channel__c, "Forensics") && CONTAINS("AK:AZ:CA:CO:HI:ID:MT:NV:NM:OR:UT:WA:WY:ND:SD:NE:KS:OK:TX:MN:IA:MO:AR:IL:WI:MI", ShippingState), "Forensics - West",
IF(ISPICKVAL(Channel__c, "Forensics") && CONTAINS("LA:MS:TN:KY:IN:OH:AL:CT:DC:DE:FL:GA:ME:MA:MD:NH:NJ:NY:NC:PA:RI:SC:VT:VA:WV:PR", ShippingState), "Forensics - East",
IF(OR (ISPICKVAL(Channel__c , "Domestic"),ISPICKVAL(Channel__c , "Government"))&& CONTAINS ("AK:AZ:CA:CO:HI:ID:KS:MT:NV:NM:OR:UT:WA:WY", ShippingState), "LE West",
IF(OR (ISPICKVAL(Channel__c , "Domestic"),ISPICKVAL(Channel__c , "Government"))&& CONTAINS ("CT:DC:DE:FL:GA:ME:MA:MD:NH:NJ:NY:NC:PA:RI:SC:VT:VA:WV:PR", ShippingState), "LE East",
IF(OR (ISPICKVAL(Channel__c , "Domestic"),ISPICKVAL(Channel__c , "Government"))&& CONTAINS ("AL:AR:IL:IN:IA:KS:KY:LA:MI:MN:MS:MO:NE:ND:OH:OK:SD:TN:TX:WI", ShippingState), "LE Central", NULL))))))))))))
I copied this formula and used it in a new formula field.
It worked fine with out any issues.
Check your pick list values whether they are same or not.
Thanks so much - I really appreciate it :) There was a disconnect in my picklist
Shoot - When I went to update all my records - the ones without states (which should return a null value) are showing LE West - do you know why it would do that?
Hi
Just add this Condition in the formula for all the conditions.
IF(OR (ISPICKVAL(Channel__c , "Domestic"),ISPICKVAL(Channel__c , "Government"))&& ShippingState<>NULL&&CONTAINS ("CT:DC:DE:FL:GA:ME:MA:MD:NH:NJ:NY:NC:PA:RI:SC:VT:VA:WV:PR", ShippingState), "LE East",
YEA - Thanks it finally works!!!! I'm so excited. Thank you, Thank you, Thank you :)