Views:

Scenario Overview

In this scenario you need to validate that the user enters in a valid US state in its 2 character format. In previous versions of CRM you would normally just add a picklist with all the valid values but the new composite control in CRM 2013 does not allow this so this validation formula is very useful. Note that as we are running this formula server-side this validation rule holds true for data imports, custom API code that may be inserting or update the account record or any workflows, dialogs or actions that may run.

N52 Formula Manager Solution

The Formula Manager solution works like this,

  • A server-side validation rule is created to enforce data quality 
  • The value in the address1_state field is checked to see if it is in a list of values
  • If it is not in the list of values then it stops the create or update of the account record & returns an error message

N52 Formula Manager Steps

The following set of steps outline how to create this Formula

  • Create a new formula of type 'Validation'
  • Set the Source Entity to 'Account' & set the Source Property to 'Address1: State/Province'
  • Copy & paste the formula below into the formula 
  • Click save & test

Formula

if( In( [account.address1_stateorprovince], 'AL', 'AK', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DE', 'FL','GA','HI','ID','IL','IN','IA','KS','KY','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY'), 
           'NoOp', 'You have entered an invalid state, please correct.')