Validation Rule formulas: Part 1
With the upcoming Winter’07 release of salesforce there is one of my favourite new features being added. However it comes at a price, that price being the fact that you have to write formulas. The feature I’m talking about is Validation Rules these allow admins to specify formulas which limit what a user can enter into a field in salesforce.
This useful feature also allows admins to specify a custom error message and also where they would like the error to appear. At the top of the screen or at the field itself.
The final useful thing to know about this feature is the order in which it is applied in salesforce along with workflow because this can be exploited to give your users messages. The order is as follows:
- Salesforce Standard Validation (email address in correct format)
- Custom Validation Rules
- Workflow is fired (emails sent, fields updated, etc..)
The formulas you have to write for validation rules are quite specific. I’ll be writing a short guide over the next while on writing formulas for Validation rules.
While writing validation rule formula you are writing for the error meaning if your formula returns a true value the error will appear. Its best to use bolean formulas to do this. Don’t use the IF function as it returns a value not a true or false value.
Here is an example of a formula written to force users to enter a valid state code for the US.
AND ( OR(BillingCountry = "US", BillingCountry="USA", LEN(BillingCountry)=0), OR( LEN(BillingState) < 2, NOT( CONTAINS("AL:AK:AZ:AR:CA:CO:CT:DE:DC: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", BillingState) )))
NOTE: I’ve added in the concatenations to make this more readable these are not essential but won’t effect the formula itself. I’ll leave it at that for today and I’ll keep adding more and more as I go along.
Popularity: 22% [?]
4 Comments, Comment or Ping
The following “State” validation rule will ensure that if a user has selected a country of either “Canada” or “US”, and they supply a state/province, that it is a 2 letter code for the state/province.
IF(AND(LEN(State) > 0, OR(Country=”Canada”, Country=”US”)),
NOT(
AND(
LEN(State) = 2,
OR(
IF(AND(Country=”Canada”, CONTAINS(”AB:BC:MB:NB:NL:NT:NS:NU:ON:PC:QC:SK:YT”, State)), TRUE, FALSE),
IF(AND(Country=”US”, CONTAINS(”AL:AK:AZ:AR:CA:CO:CT:DE:DC: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”, State)), TRUE, FALSE)
)
)
),
FALSE)
The following rule ensures that the country field is supplied as “US” for the United State, “Canada” for Canada, and anything else for any other country.
OR
(
OR(Country=”CAN”, Country=”CANADA”, Country=”CA”, Country=”CAD”, Country=”STATES”, Country=”The States”, Country = “States”, Country=”CDN”, Country = “USA”, Country=”UNITED”, Country=”UNITED STATES”, Country=”USA”, Country=”U.S.A.”, Country=”US A.”, Country=”U S A.”, Country=”U.S.A.”),
AND(LEN(Country)
I would like to know if there is a formula for making sure that the data is selected from the dropdown list and not typed randomly.
Please reply onto my email address:
v_petrosian@hotmail.com
Vardan Petrosian
In what language are these validation rules written?
I would like to start developing my own rules…
sklugman@phreesia.com
Thanks!
Reply to “Validation Rule formulas: Part 1”