Ask Search:
Jim GentileJim Gentile 

Phone Validation Rule

Hello,

Can I get help modifying the following validation rules so they are joined together as one rule and,

1.     LEFT(Phone, 1) <> "+"
Is only activated when the BillingState IS NOT equal to 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


2.     NOT(REGEX(Phone, "\\D*?(\\d\\D*?){10}"))
Is only active when the BillingState IS equal to 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

Thanks for the help.
Aaron DeRanAaron DeRan
You will need to replicate my BillingState cases for all of your State abbreviations but this should work once you do that:

AND(         
          AND(
                    LEFT(Phone, 1) <> "+",
                    AND(BillingState <> "AL",
                             BillingState <> "AK",
                             BillingState <> "AZ",
                             BillingState <> "AR"
                            )
                   ),
          AND(
                    NOT(REGEX(Phone, "\\D*?(\\d\\D*?){10}")),
                    OR(BillingState = "AL",
                            BillingState ="AK",
                            BillingState ="AZ",
                            BillingState ="AR"
                           )
                   )
)
Jim GentileJim Gentile
Following is the validation rule I used.  It is not requiring the formating of the number.  Did I miss something?

AND(         
          AND(
                    LEFT(Phone, 1) <> "+",
                    AND(BillingState <> "
AL",
BillingState <> "AK",
BillingState <> "AZ",
BillingState <> "AR",
BillingState <> "CA",
BillingState <> "CO",
BillingState <> "CT",
BillingState <> "DE",
BillingState <> "DC",
BillingState <> "FL",
BillingState <> "GA",
BillingState <> "HI",
BillingState <> "ID",
BillingState <> "IL",
BillingState <> "IN",
BillingState <> "IA",
BillingState <> "KS",
BillingState <> "KY",
BillingState <> "LA",
BillingState <> "ME",
BillingState <> "MD",
BillingState <> "MA",
BillingState <> "MI",
BillingState <> "MN",
BillingState <> "MS",
BillingState <> "MO",
BillingState <> "MT",
BillingState <> "NE",
BillingState <> "NV",
BillingState <> "NH",
BillingState <> "NJ",
BillingState <> "NM",
BillingState <> "NY",
BillingState <> "NC",
BillingState <> "ND",
BillingState <> "OH",
BillingState <> "OK",
BillingState <> "OR",
BillingState <> "PA",
BillingState <> "RI",
BillingState <> "SC",
BillingState <> "SD",
BillingState <> "TN",
BillingState <> "TX",
BillingState <> "UT",
BillingState <> "VT",
BillingState <> "VA",
BillingState <> "WA",
BillingState <> "WV",
BillingState <> "WI",
BillingState <> "WY"
                            )
                   ),
          AND(
                    NOT(REGEX(Phone, "\\D*?(\\d\\D*?){10}")),
                    OR(BillingState <> "
AL",
BillingState <> "AK",
BillingState <> "AZ",
BillingState <> "AR",
BillingState <> "CA",
BillingState <> "CO",
BillingState <> "CT",
BillingState <> "DE",
BillingState <> "DC",
BillingState <> "FL",
BillingState <> "GA",
BillingState <> "HI",
BillingState <> "ID",
BillingState <> "IL",
BillingState <> "IN",
BillingState <> "IA",
BillingState <> "KS",
BillingState <> "KY",
BillingState <> "LA",
BillingState <> "ME",
BillingState <> "MD",
BillingState <> "MA",
BillingState <> "MI",
BillingState <> "MN",
BillingState <> "MS",
BillingState <> "MO",
BillingState <> "MT",
BillingState <> "NE",
BillingState <> "NV",
BillingState <> "NH",
BillingState <> "NJ",
BillingState <> "NM",
BillingState <> "NY",
BillingState <> "NC",
BillingState <> "ND",
BillingState <> "OH",
BillingState <> "OK",
BillingState <> "OR",
BillingState <> "PA",
BillingState <> "RI",
BillingState <> "SC",
BillingState <> "SD",
BillingState <> "TN",
BillingState <> "TX",
BillingState <> "UT",
BillingState <> "VT",
BillingState <> "VA",
BillingState <> "WA",
BillingState <> "WV",
BillingState <> "WI",
BillingState <> "WY"
                           )
                   )
)

Aaron DeRanAaron DeRan
Your points above say that one is when the Billing State equals and the other is when it does not equal. Your rule has <> for both points.

You need to swap out <> for = in your second point.

Jim GentileJim Gentile
Really do appreciate the help.  I made the change and yielding the sme results.  The validation is not being fored.  Here is the rule I have in:

AND(         
          AND(
                    LEFT(Phone, 1) <> "+",
                    AND(BillingState <> "AL",
BillingState <> "AK",
BillingState <> "AZ",
BillingState <> "AR",
BillingState <> "CA",
BillingState <> "CO",
BillingState <> "CT",
BillingState <> "DE",
BillingState <> "DC",
BillingState <> "FL",
BillingState <> "GA",
BillingState <> "HI",
BillingState <> "ID",
BillingState <> "IL",
BillingState <> "IN",
BillingState <> "IA",
BillingState <> "KS",
BillingState <> "KY",
BillingState <> "LA",
BillingState <> "ME",
BillingState <> "MD",
BillingState <> "MA",
BillingState <> "MI",
BillingState <> "MN",
BillingState <> "MS",
BillingState <> "MO",
BillingState <> "MT",
BillingState <> "NE",
BillingState <> "NV",
BillingState <> "NH",
BillingState <> "NJ",
BillingState <> "NM",
BillingState <> "NY",
BillingState <> "NC",
BillingState <> "ND",
BillingState <> "OH",
BillingState <> "OK",
BillingState <> "OR",
BillingState <> "PA",
BillingState <> "RI",
BillingState <> "SC",
BillingState <> "SD",
BillingState <> "TN",
BillingState <> "TX",
BillingState <> "UT",
BillingState <> "VT",
BillingState <> "VA",
BillingState <> "WA",
BillingState <> "WV",
BillingState <> "WI",
BillingState <> "WY"
                            )
                   ),
          AND(
                    NOT(REGEX(Phone, "\\D*?(\\d\\D*?){10}")),
                    OR(BillingState <> "AL",
BillingState = "AK",
BillingState = "AZ",
BillingState = "AR",
BillingState = "CA",
BillingState = "CO",
BillingState = "CT",
BillingState = "DE",
BillingState = "DC",
BillingState = "FL",
BillingState = "GA",
BillingState = "HI",
BillingState = "ID",
BillingState = "IL",
BillingState = "IN",
BillingState = "IA",
BillingState = "KS",
BillingState = "KY",
BillingState = "LA",
BillingState = "ME",
BillingState = "MD",
BillingState = "MA",
BillingState = "MI",
BillingState = "MN",
BillingState = "MS",
BillingState = "MO",
BillingState = "MT",
BillingState = "NE",
BillingState = "NV",
BillingState = "NH",
BillingState = "NJ",
BillingState = "NM",
BillingState = "NY",
BillingState = "NC",
BillingState = "ND",
BillingState = "OH",
BillingState = "OK",
BillingState = "OR",
BillingState = "PA",
BillingState = "RI",
BillingState = "SC",
BillingState = "SD",
BillingState = "TN",
BillingState = "TX",
BillingState = "UT",
BillingState = "VT",
BillingState = "VA",
BillingState = "WA",
BillingState = "WV",
BillingState = "WI",
BillingState = "WY"
                           )
                   )
)
Aaron DeRanAaron DeRan
You still have <> next to AL in the second point. When you tested it you didnt happen to do it on a record with the Billing State AL did you?
Jim GentileJim Gentile
I corrected the code.  I was testing on a VA address.  The validation just won't work.  I can't seem to figure out why.
Aaron DeRanAaron DeRan
My best guess is that this line is wrong:

NOT(REGEX(Phone, "\\D*?(\\d\\D*?){10}")),

But to be honest, I have no idea what you are trying to do with "\\D*?(\\d\\D*?){10}".


Jim GentileJim Gentile
NOT(REGEX(Phone, "\\D*?(\\d\\D*?){10}")) is the validation rule provided by Salesforce.com for formating phone numbers.  Here's the link:
http://login.salesforce.com/help/doc/en/fields_useful_field_validation_formulas.htm

I
f you know of a better way, I'm open to suggestions.
Austin NordmanAustin Nordman
Can you not use country?  By my count that is every state in the US.  What exactly are you trying to get it to do, especially the second part with the REGEX, what format are you looking for?
Jim GentileJim Gentile
The ultimate goal is to validate the phone number based on the state code.  If it the state is listed as one of the US states then the 10 digit format will be required.

If the state does not match a US state then the International format will be required.

I'm not using country because we have workflow that populates the country based on the state selection.
Austin NordmanAustin Nordman
Try changing the first AND to an OR