You need to sign in to do that
Don't have an account?
tan
Formula Field help
Not sure if this is development related but I am trying to work with the formula fields. I have the formulas working but they seem very inefficient. I have also hit the limiation on the formula length (1000 chars)
I am trying to determine 2 field automaticaly based on the country and state input. This way we can determine regional values with out data integrity concerns and adjust as needed with out much effort.
Any sugestions on how to improve this approach would be very welcome.. thanks ! \
Currnet Formula:
IF( OR((ISPICKVAL({!Country__c}, "USA")),(ISPICKVAL({!Country__c}, "US"))),
IF( OR ({!State__c}="MI",{!State__c}="WI",{!State__c}="IL",{!State__c}="OH",{!State__c}="IN",{!State__c}="KY"
,{!State__c}="KS",{!State__c}="IA",{!State__c}="MN"),
"Americas Great Lakes",
IF( OR ({!State__c}="CA",{!State__c}="OR",{!State__c}="WA",{!State__c}="NV",{!State__c}="AZ",{!State__c}="ID"
,{!State__c}="UT",{!State__c}="WY",{!State__c}="MT",{!State__c}="AR",{!State__c}="HI",{!State__c}="CO",{!State__c}="AK"),
"Americas Western",
IF( OR ({!State__c}="NY",{!State__c}="MA",{!State__c}="PA",{!State__c}="CN",{!State__c}="NH",{!State__c}="VA"
,{!State__c}="MD",{!State__c}="ME",{!State__c}="DE"),
"Americas Eastern",
IF( OR ({!State__c}="TX",{!State__c}="NE",{!State__c}="GA",{!State__c}="MS",{!State__c}="FL",{!State__c}="AR",
{!State__c}="AL",{!State__c}="LA"),
"Americas Southern",
IF( OR ({!State__c}="NC",{!State__c}="SC",{!State__c}="VA"),
"Americas Mid Atlantic","USA")
)))),"International"
)
I am trying to determine 2 field automaticaly based on the country and state input. This way we can determine regional values with out data integrity concerns and adjust as needed with out much effort.
Any sugestions on how to improve this approach would be very welcome.. thanks ! \
Currnet Formula:
IF( OR((ISPICKVAL({!Country__c}, "USA")),(ISPICKVAL({!Country__c}, "US"))),
IF( OR ({!State__c}="MI",{!State__c}="WI",{!State__c}="IL",{!State__c}="OH",{!State__c}="IN",{!State__c}="KY"
,{!State__c}="KS",{!State__c}="IA",{!State__c}="MN"),
"Americas Great Lakes",
IF( OR ({!State__c}="CA",{!State__c}="OR",{!State__c}="WA",{!State__c}="NV",{!State__c}="AZ",{!State__c}="ID"
,{!State__c}="UT",{!State__c}="WY",{!State__c}="MT",{!State__c}="AR",{!State__c}="HI",{!State__c}="CO",{!State__c}="AK"),
"Americas Western",
IF( OR ({!State__c}="NY",{!State__c}="MA",{!State__c}="PA",{!State__c}="CN",{!State__c}="NH",{!State__c}="VA"
,{!State__c}="MD",{!State__c}="ME",{!State__c}="DE"),
"Americas Eastern",
IF( OR ({!State__c}="TX",{!State__c}="NE",{!State__c}="GA",{!State__c}="MS",{!State__c}="FL",{!State__c}="AR",
{!State__c}="AL",{!State__c}="LA"),
"Americas Southern",
IF( OR ({!State__c}="NC",{!State__c}="SC",{!State__c}="VA"),
"Americas Mid Atlantic","USA")
)))),"International"
)
Message Edited by tan on 01-12-2006 02:59 PM
One thing you can try is using the CONTAINS function, to see if that helps shorten your formula to under 1000 characters (unfortunately, this didn't help me, the formula was still to big). Another thing to note is that Winter '06 allows for CONTAINS to be used in combination with ISPICKVAL. Looking at the sample you provided, I believe you could use this new functionality to reduce your formula by a few more characters.
Here's an example of using the CONTAINS function for calculating a sales territory by state. Unfortunately for me, this is an incomplete formula (CA, NV, TX, NY, and PA don't appear in my formula because I have to calculate the sales territory in those states based on zip, and that makes the formula too long).
IF({!State}="","Null",
IF(CONTAINS("AK:MT:OR:ID:WA",{!State}),"Northwest",
IF(CONTAINS("AZ:NM",{!State}),"Southwest",
IF(CONTAINS("UT:WY:CO:NE",{!State}),"Mountain",
IF(CONTAINS("ND:SD:MN:IA",{!State}),"Upper Midwest",
IF(CONTAINS("KS:MO:OK:AR",{!State}),"Midwest",
IF(CONTAINS("AL:GA:SC",{!State}),"South",
IF(CONTAINS("FL:PR:VI",{!State}),"Florida",
IF(CONTAINS("WI:IL",{!State}),"IL/WI",
IF(CONTAINS("MI",{!State}),"Michigan",
IF(CONTAINS("OH:IN",{!State}),"OH/IN",
IF(CONTAINS("KY:TN:NC",{!State}),"KY/TN/NC",
IF(CONTAINS("VA:WV:DE:MD:DC",{!State}),"Mid-Atlantic",
IF(CONTAINS("ME",{!State}),"Maine (ICBS)",
IF(CONTAINS("CT",{!State}),"CT",
IF(CONTAINS("HI",{!State}),"Hawaii",
IF(CONTAINS("LA:MS",{!State}),"Gulf States",
IF(CONTAINS("NJ",{!State}),"NJ/East PA",
IF(CONTAINS("MA:NH:RI:VT",{!State}),"New England",
"Need to Use Zip for Territory Calc"
)))))))))))))))))))
If this doesn't help you, keep your eye on the thread I just created (link below). Let's hope someone else has a great sugggestion!
http://forums.sforce.com/sforce/board/message?board.id=general_development&message.id=5962
2 questions.. I did not see the colon syntax for CONTAINS to check multiple test strings on the saleforce site. It seems to work but was not sure how to test.
How do you combine ISPICKVAL and CONTAINS... have not figured that out yet.
copy below... thanks again!
IF( OR((ISPICKVAL({!Country__c}, "USA")),(ISPICKVAL({!Country__c}, "US"))),
IF(CONTAINS("IL:IN:IA:KS:KY:MI:MN:OH:WI:ND",{!State__C}),"Americas Great Lakes",
IF(CONTAINS("CT:DE:ME:MD:MA:NH:NJ:NY:PA:VT",{!State__C}),"Americas East",
IF(CONTAINS("AK:AZ:CA:CO:HI:ID:MY:NV:NM:OR:WA:WY",{!State__C}),"Americas West",
IF(CONTAINS("AL:AR:FL:GA:MS:SC:TN:TX:WV",{!State__C}),"Americas South",
IF(CONTAINS("VA:NC:SC:DC",{!State__C}),"Americas Mid Atlantic",
"USA")
)))),
IF(ISPICKVAL({!Country__C},"Germany"),"EMEA Central",
IF(ISPICKVAL({!Country__C},"France"),"EMEA South",
IF(ISPICKVAL({!Country__C},"United Kingdom"),"EMEA North",
IF(ISPICKVAL({!Country__C},"Norway"),"EMEA Nordics",
IF(ISPICKVAL({!Country__C},"Australia"),"South Asia ANZ",
IF(ISPICKVAL({!Country__C},"Japan"),"Japan",
IF(ISPICKVAL({!Country__C},"China"),"North Asia China",
"International")))))))
)
http://www.crmsuccess.com/browse/content_detail.jsp?id=006300000029tJYAAY
The CASE function enhancement is described in the Winter '06 release notes (p. 43):http://www.crmsuccess.com/browse/content_detail.jsp?id=00630000003QqgI&flid=02n300000000OEuAAM&slid=&tlid=02o300000000uGnAAI