function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
tantan 

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"
)

Message Edited by tan on 01-12-2006 02:59 PM

Jeff TalbotJeff Talbot
I have a very similar (or possibly the same) problem trying to calculate sales territories. I simply have too many territories. I have 26 territores to calculate based on state and zip.

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
tantan
Thanks! a great help.

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")))))))
)
Jeff TalbotJeff Talbot
The CASE function is described in the Summer '05 release notes:
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