Validation Rule on New State / Country pick list - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Eric BlaxtonEric Blaxton 

Validation Rule on New State / Country pick list

Requirement: IF the country code is either United States, Canada, Mexico then require a Billing State Code.  For United Kingdom, do not require a State.

BTW I am recently turned on the new State and Country picklists

I wrote this and it is requiring a state for ALL countries not just those listed.

OR(ISPICKVAL(BillingCountryCode,"United States"), ISPICKVAL(BillingCountryCode,"Canada"),
ISPICKVAL(BillingCountryCode, "Mexico"),
AND(ISPICKVAL(BillingStateCode, "")))

Thanks for your help.
Eric
Best Answer chosen by Eric Blaxton
Jeff MayJeff May

Your current Rule checks to see if the country is any of the 3 values OR if the State is blank:

OR(
ISPICKVAL(BillingCountryCode,"United States"),
ISPICKVAL(BillingCountryCode,"Canada"),
ISPICKVAL(BillingCountryCode, "Mexico"),
AND(ISPICKVAL(BillingStateCode, ""))
)

I think what you are looking for is if the country is any of the 3 values AND the State is blank:

AND(
OR(
ISPICKVAL(BillingCountryCode,"United States"),
ISPICKVAL(BillingCountryCode,"Canada"),
ISPICKVAL(BillingCountryCode, "Mexico")
),
ISPICKVAL(BillingStateCode, "")
)

All Answers

Jeff MayJeff May

Your current Rule checks to see if the country is any of the 3 values OR if the State is blank:

OR(
ISPICKVAL(BillingCountryCode,"United States"),
ISPICKVAL(BillingCountryCode,"Canada"),
ISPICKVAL(BillingCountryCode, "Mexico"),
AND(ISPICKVAL(BillingStateCode, ""))
)

I think what you are looking for is if the country is any of the 3 values AND the State is blank:

AND(
OR(
ISPICKVAL(BillingCountryCode,"United States"),
ISPICKVAL(BillingCountryCode,"Canada"),
ISPICKVAL(BillingCountryCode, "Mexico")
),
ISPICKVAL(BillingStateCode, "")
)

This was selected as the best answer
Eric BlaxtonEric Blaxton
Hi Jeff and thanks for your reply.

I made sure the VR is active and your code does not work.  No errors are being thrown at all.  I actually had something similiar to this last night and no error was being caught.  

Eric
Jeff MayJeff May
The VR formula I gave you will be "True" if the country is any of those 3 countries, and if the State is ''.

The best way to troubleshoot is to create a formula checkbox field with the same formula.  Then add the BillingCountryCode and BillingStateCode fields, plus the formula field to a report.  That will let you see the value of all three fields for each record.
Eric BlaxtonEric Blaxton
I can see the values of each record in the report I created.  They are all as expected.

The VR is not catching anything.

Eric
Steve MolisSteve Molis
Can you post a screenshot of the report that shows all of the fields side-by-side?  @JeffMay's VR formula looks good, and if you don't get any Syntax errors is usually means a mismatch between the values in your data and the values in your formula.  
Jeff MayJeff May
You might try using ISBLANK(TEXT(BillingStateCode)) to see if that's the problem.
Eric BlaxtonEric Blaxton
I tried using the new ISBlank line and still nothing is being caught.  

I went through and verified the fields were correct. 

 If it makes any difference, I had a VR working before the State and Country text fields were converted to pick lists.

Regards,
Eric


Steve MolisSteve Molis
Can you post screenshots of the Report results please?
Eric BlaxtonEric Blaxton
User-added image
Here is the report.
Eric BlaxtonEric Blaxton
Steve,

Were you able to glean any information from my report?

Eric
Steve MolisSteve Molis
A few things I would double-check;

Make sure the VR is "Active".

Make sure there are no exemptions to the VR for your Profile.  

Make sure there are no blank spaces in the picklist values.

After checking all of those things manually create and edit a record (and old school click the Edit button) that violates the VR.  

Also, you said something similar happened last night, how was that resolved?  

Eric BlaxtonEric Blaxton
I went back and did some more reading about the State and Country picklists.  The values in the country picklist are displayed as the full name, i.e. United States, Canada, Mexico, but the codes are US, CA, MX respecitvely.  

Be sure to use the code value and not the display value.

Thanks for your help Jeff and Steve.  

Eric