Print this page

How do I create a formula field to set the region based on state ?

Knowledge Article Number 000005450
Description

How do I create a formula field to set the region based on state ?

Resolution

To create a formula field called "Region" (e.g. on Accounts) and set its value depending on the value of the "State" field.

Custom formulas are limited to a 1000 character limit including spaces, white space characters, carriage returns etc. per formula. Therefore due to the number of values to be checked in this example you will need to use special characters to separate the disparate values in the formula in order to stay under the size limit.

For example:

IF(LEN({!BillingState})=0, "None",
IF(CONTAINS("AK:AZ:CA:CO:HA:ID:KS:MT:NV:NM:OK:OR:TX:UT:WA:WY", {!BillingState}), "West",
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", "Unknown")))))

The first line in this formula will check for a blank value in the Billing State field. You can modify this formula to match your regional breakdown and display the proper region name.





promote demote