Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

Our enterprise data systems maintain a list of 2-character country codes that I want to enforce inside Salesforce. I'm having trouble writing a validation rule that successfully does this.

The rule on Lead.Country is:

AND( 
 !ISBLANK(Country), 
 LEN(Country) <> 2, 
 NOT(CONTAINS("00:AD:AE:AF:AG:AI:AL:AM:AN:AO:AQ:AR:AS:AT:AU:AW:AX:AZ:BA:"&
    "BB:BD:BE:BF:BG:BH:BI:BJ:BL:BM:BN:BO:BR:BS:BT:BV:BW:BY:BZ:"&
    "CA:CC:CD:CF:CG:CH:CI:CK:CL:CM:CN:CO:CR:CU:CV:CX:CY:CZ:"&
    "DE:DJ:DK:DM:DO:DZ:EC:EE:EG:EH:ER:ES:ET:FI:FJ:FK:FM:FO:FR:"&
    "GA:GB:GD:GE:GF:GH:GI:GL:GM:GN:GP:GQ:GR:GS:GT:GU:GW:GY:HK:HM:HN:"& 
    "HR:HT:HU:ID:IE:IL:IM:IN:IO:IQ:IR:IS:IT:JE:JM:JO:JP:KE:KG:KH:KI:KM:KN:KP:"& 
    "KR:KW:KY:KZ:LA:LB:LC:LI:LK:LR:LS:LT:LU:LV:LY:MA:MC:MD:ME:MF:MG:MH:MK:ML:"& 
    "MM:MN:MO:MP:MQ:MR:MS:MT:MU:MV:MW:MX:MY:MZ:NA:NC:NE:NF:NG:NI:NL:NO:NP:NR:"& 
    "NU:NZ:OM:PA:PE:PF:PG:PH:PK:PL:PM:PN:PR:PS:PT:PW:PY:QA:RE:RO:RS:RU:RW:SA:"& 
    "SB:SC:SD:SE:SG:SH:SI:SJ:SK:SL:SM:SN:SO:SR:ST:SV:SY:SZ:TC:TD:TF:TG:TH:TJ:"& 
    "TK:TL:TM:TN:TO:TR:TT:TV:TW:TZ:UA:UG:UM:US:UY:UZ:VA:VC:VE:VG:VI:VN:VU:WF:"& 
    "WS:XK:YE:YT:YU:ZA:ZM:ZW", Country)) 
  )

Here are some sample results:

  • Fails validation
    • "1"
    • "ABC"
  • Passes validation
    • "A"
    • "AB"

It seems that since every letter of the alphabet is in the Contains block and single or two-character combination passes.

share|improve this question

3 Answers 3

Why not trying using a VLookup. you can store all of your values in an object and then use the VLOOKUP() formula to see if it exists in the list. That way you avoid running into the size limit on formula fields and you can more easily maintain the list of valid country codes.

https://help.salesforce.com/apex/HTViewHelpDoc?id=customize_functions.htm&language=en#VLOOKUP

share|improve this answer
    
I will look into that. –  Mike Chale Aug 8 '12 at 17:51
    
While this did not directly answer my question, it is definitely a more elegant solution. –  Mike Chale Aug 9 '12 at 19:27
up vote 2 down vote accepted

The problem turned out to be in the AND clause. I re-examined the Salesforce example for US states and realized I need to OR together the LEN and CONTAINS functions.

AND( 
 !ISBLANK(Country), 
 OR(
  LEN(Country) <> 2, 
  NOT(CONTAINS("00:AD:AE:AF:AG:AI:AL:AM:AN:AO:AQ:AR:AS:AT:AU:AW:AX:AZ:BA:"&
    "BB:BD:BE:BF:B <snip>)
    )
 )
share|improve this answer
1  
I find it more natural to use && for AND and || for OR. I feel it makes it more readable: !ISBLANK(Country) && (LEN(Country) <> 2 || !CONTAINS("00:AD:AE:AF", Country)) –  Daniel Hoechst Aug 8 '12 at 14:40
    
I didn't even realize that was an option! All of the examples I've ever seen just use AND & OR. Thank you! –  Mike Chale Aug 8 '12 at 14:58
    
Isn't it redundant to check ISBLANK(Country) and LEN(Country) <> 2? A valid code is one where the length is 2, AND it's contained in the list of valid codes. –  tomlogic Aug 27 '12 at 23:39
    
No, because this allows for blank (unknown) data. If it didn't have the IsBlank the field would essentially be required. This way, if the field is not blank, the data must be 2 characters long. –  Mike Chale Aug 28 '12 at 1:28

This exists in the Formula's help documentation:

OR(
LEN(BillingCountry) = 1,
NOT(
CONTAINS(
"AF:AX:AL:DZ:AS:AD:AO:AI:AQ:AG:AR:AM:" &
"AW:AU:AZ:BS:BH:BD:BB:BY:BE:BZ:BJ:BM:BT:BO:" &
"BA:BW:BV:BR:IO:BN:BG:BF:BI:KH:CM:CA:CV:KY:" &
"CF:TD:CL:CN:CX:CC:CO:KM:CG:CD:CK:CR:CI:HR:" &
"CU:CY:CZ:DK:DJ:DM:DO:EC:EG:SV:GQ:ER:EE:ET:FK:" &
"FO:FJ:FI:FR:GF:PF:TF:GA:GM:GE:DE:GH:GI:GR:GL:" &
"GD:GP:GU:GT:GG:GN:GW:GY:HT:HM:VA:HN:HK:HU:" &
"IS:IN:ID:IR:IQ:IE:IM:IL:IT:JM:JP:JE:JO:KZ:KE:KI:" &
"KP:KR:KW:KG:LA:LV:LB:LS:LR:LY:LI:LT:LU:MO:MK:" &
"MG:MW:MY:MV:ML:MT:MH:MQ:MR:MU:YT:MX:FM:MD:MC:" &
"MC:MN:ME:MS:MA:MZ:MM:MA:NR:NP:NL:AN:NC:NZ:NI:" &
"NE:NG:NU:NF:MP:NO:OM:PK:PW:PS:PA:PG:PY:PE:PH:" &
"PN:PL:PT:PR:QA:RE:RO:RU:RW:SH:KN:LC:PM:VC:WS:" &
"SM:ST:SA:SN:RS:SC:SL:SG:SK:SI:SB:SO:ZA:GS:ES:" &
"LK:SD:SR:SJ:SZ:SE:CH:SY:TW:TJ:TZ:TH:TL:TG:TK:" &
"TO:TT:TN:TR:TM:TC:TV:UG:UA:AE:GB:US:UM:UY:UZ:" &
"VU:VE:VN:VG:VI:WF:EH:YE:ZM:ZW",
BillingCountry)))
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.