Using a Custom Formula to Calculate Account Region
Jul 20, 2005
"Territory management" means something different to every sales organization, and the requirements can get quite complex.
However, sometimes a few simple rules are sufficient to get the job done. For example, if you just need to categorize your accounts into regions, based on state or province.
The Region formula below returns a text value of North, South, East, West, or Central based on the Billing State of the account.
Formula:
IF(LEN( {!BillingState})=0, "None",
IF(CONTAINS("AK:AZ:CA:HI:NV:NM:OR:UT:WA", {!BillingState}), "West",
IF(CONTAINS("CO:ID:MT:KS:OK:TX:WY", {!BillingState}), "Central",
IF(CONTAINS("CT:ME:MA:NH:NY:PA:RI:VT", {!BillingState}), "East",
IF(CONTAINS("AL:AR:DC:DE:FL:GA:KY:LA:MD:MS:NC:NJ:SC:TN:VA:WV", {!BillingState}), "South",
IF(CONTAINS("IL:IN:IA:MI:MN:MO:NE:ND:OH:SD:WI", {!BillingState}), "North", "Other"))))))
Remarks:
- This formula uses the CONTAINS function to perform a lookup of the Billing State in a string of state values. The function returns TRUE if the Billing State is found in the string. Otherwise it returns FALSE.
- The colon (":") delimiter between state abbreviations is necessary to separate valid values. There is no special meaning to the colon. Any character that does not appear in Billing State could be used.
- This example is U.S. centric, but it could easily be adapted to apply in international scenarios.
Note that using the CONTAINS function in this way is useful in a variety of scenarios where a lookup against a list of static values is needed to calculate a result. This is far more efficient than building a big CASE statement or nested IF statement to check for each value individually.
Thanks to Doug Chasman for suggesting this solution.
Bookmark
By George I think there's a bit of gold here in this blog! Thanks Doug.
Posted by: Fifedog | July 22, 2005 at 09:02 AM
I believe the state code for Hawaii is HI, not HA (in your West region).
I am including a master list of all common state abbreviations below to help other developers. Otherwise this code is great and we are going to make good use of it.
Alabama AL
Alaska AK
American Samoa AS
Arizona AZ
Arkansas AR
California CA
Colorado CO
Connecticut CT
Delaware DE
District of Columbia DC
Federated States of Micronesia FM
Florida FL
Georgia GA
Guam GU
Hawaii HI
Idaho ID
Illinois IL
Indiana IN
Iowa IA
Kansas KS
Kentucky KY
Louisiana LA
Maine ME
Marshall Islands MH
Maryland MD
Massachusetts MA
Michigan MI
Minnesota MN
Mississippi MS
Missouri MO
Montana MT
Nebraska NE
Nevada NV
New Hampshire NH
New Jersey NJ
New Mexico NM
New York NY
North Carolina NC
North Dakota ND
Northern Mariana Islands MP
Ohio OH
Oklahoma OK
Oregon OR
Palau PW
Pennsylvania PA
Puerto Rico PR
Rhode Island RI
South Carolina SC
South Dakota SD
Tennessee TN
Texas TX
Utah UT
Vermont VT
Virgin Islands VI
Virginia VA
Washington WA
West Virginia WV
Wisconsin WI
Wyoming WY
--------------------
Armed Forces Africa AE
Armed Forces Americas AA
Armed Forces Canada AE
Armed Forces Europe AE
Armed Forces Middle East AE
Armed Forces Pacific AP
Posted by: Jeff Alderson | August 18, 2006 at 11:15 AM