I recently received a question from a fellow admin, and I want to share it because this is such a common need, and there is more than one way to handle it.

“I am going to be adding regions to the Accounts in our SFDC instance. I want to be able to make it so that the assignment is done based on the country and so there is no manual intervention. Do you have any ideas how I might be able to accomplish this?”

Definitely! There are a couple of ways that this can be done.

1. Formula field: If you want your “Region” field to always be based on the account’s state or country, then I recommend a formula field. Here is an example of how you could set up US regions by state:

IF(ISBLANK(BillingState), “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”))))))

You could also do this by country:

IF(ISBLANK(BillingCountry), “None”,
IF(CONTAINS(“US:CA:ME”, BillingCountry), “North America”,
IF(CONTAINS(“AR:BE:BO:BR:CH:CO:EC:GU:HO:PE:UR:VE”, BillingCountry), “South America”,
IF(CONTAINS(“UK:FR:ES:PT:FI:DK:SE:NO:NL:CH:DE:BE:AT:IT:GR”, BillingCountry), “Western Europe”,
IF(CONTAINS(“RU:RO:LV:LI:PO:HU:SI:SV:”, BillingCountry), “Eastern Europe”,
IF(CONTAINS(“CN:JP:KO:TH:VT”, BillingCountry), “Asia”, “Other”))))))

Downside: You have to rely on accurate data in your state or country fields. You may want to create a validation rule limiting those fields to two characters – but they still need to be the right two-letter codes.

Something to consider: A formula field can never be edited – this can be good or bad depending on your data requirements. Only use a formula field if there are no exceptions to your criteria!

2. Field Dependency: Another way to do this is to use picklists for state and country values, and then create field dependencies. As an example, I’ve created a field dependency where the controlling field is my custom picklist “US State” and the dependent field is “US Region.”

When you select a state, you are only allowed to select the region that you have assigned to that state. (This could be done for country just as easily)

Downside: You’d be using custom state and/or country fields instead of the standard fields. If you convert leads and map that data to accounts, you would need to create the same fields on the lead object and map them accordingly.

Upside: If the regions for some states will occasionally vary, then this option allows for that.

Another upside? Salesforce is offering state and country picklists in the Summer ‘13 release – this will be in beta, but could prove very useful.

3. Workflow Rules: Those first two options are the best for data integrity – but they do not have the flexibility to allow for occasional random exceptions to your usual criteria. Workflow rules can be used to populate your regions automatically, but still allow a record’s region to be edited if necessary. Here’s how.

• Create a picklist field, listing all of your regions – be sure to include “Other” or “Multi-region” if there are exceptions.
• When you get to security for the field, make it read-only for all profiles except the System Administrator.
• Next, create a workflow rule for each region, based on your state or country criteria. Select “Evaluate the rule when a record is created” – this will allow future editing without firing the workflow rule.

Now, when a user converts a lead or creates a new account, the region will be auto-populated. The user will not be able to edit it – but a system administrator can change it if there is a random exception.

These ideas do not only apply to regions – there are probably many other fields whose data integrity can be improved by formulas, field dependencies, or workflow rules + field-level security. Using those three options can make consistency a reality in your database, so keep that in mind every time you create a field that needs to be accurate or locked down. For you as an admin, it’s data integrity – and for your users, it’s less work. Everyone wins!