Validation Rules That Actually Work: Preventing Bad Data at Entry
Stop cleaning up data and start preventing bad data from entering in the first place
The admin before me had a philosophy: "Let users enter data however they want, we'll clean it up later."
Later never came. Or when it did, the cleanup took weeks and had to be repeated every quarter.
Validation rules flip this approach. Instead of fixing bad data after it exists, you prevent bad data from being created. It's faster, cheaper, and doesn't require quarterly cleanup projects.
Here's how to build validation rules that protect data quality without driving users crazy.
Validation Rule Fundamentals
A validation rule has two parts:
1. Error Condition Formula: Returns TRUE when the data is invalid
2. Error Message: What the user sees when they try to save invalid data
The rule fires when the formula returns TRUE, blocking the save and displaying the message.
Key Concept: The formula defines what's WRONG, not what's right. If the formula evaluates to TRUE, the save is blocked.
The Validation Rule Framework
Before writing rules, establish principles:
Principle 1: Block Entry Errors, Not Business Decisions
Good: Blocking invalid email formats
Bad: Blocking opportunities under $10,000
Validation rules prevent data entry errors. They shouldn't enforce business policies that have legitimate exceptions.
Principle 2: Explain the Fix, Not Just the Problem
Bad message: "Invalid phone number"
Good message: "Phone number must be exactly 10 digits (e.g., 5551234567)"
Users should know exactly how to fix the error.
Principle 3: Don't Over-Validate
Every validation rule is friction. Only validate what matters:
• Fields that break automation
• Fields that affect reporting
• Fields that impact customer communications
Don't validate fields that are nice-to-have or rarely used.
Principle 4: Provide Bypass When Necessary
Some rules need admin override. Create a checkbox field (Bypass_Validation__c) and include it in rules:
AND( NOT(Bypass_Validation__c), [your validation logic])
Only admins can check the bypass field. Use sparingly.
Essential Validation Rules
Email Format
What it prevents: Invalid emails that bounce, breaking automation
Formula:
AND( NOT(ISBLANK(Email)), NOT(REGEX(Email, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$")))
Error Message: "Please enter a valid email address (e.g., name@company.com)"
Phone Number Format
What it prevents: Inconsistent phone data that's hard to use
Formula (US 10-digit):
AND( NOT(ISBLANK(Phone)), NOT(REGEX(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Phone, "(", ""), ")", ""), "-", ""), " ", ""), "^[0-9]{10}$")))
This strips common formatting before checking for 10 digits.
Error Message: "Phone number must contain exactly 10 digits"
State Abbreviation
What it prevents: Inconsistent state data that breaks segmentation
Formula (after converting to picklist, validate text if needed):
AND( NOT(ISBLANK(BillingState)), NOT(CONTAINS("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,DC", BillingState)))
Better approach: Use a picklist instead of validation.
Close Date in the Future (New Opportunities)
What it prevents: Opportunities with past close dates that skew pipeline
Formula:
AND( ISNEW(), CloseDate < TODAY())
Error Message: "Close Date must be today or in the future for new opportunities"
Required Fields Based on Stage
What it prevents: Opportunities advancing without necessary data
Formula:
AND( ISPICKVAL(StageName, "Closed Won"), OR( ISBLANK(Amount), ISBLANK(CloseDate), ISBLANK(Primary_Contact__c) ))
Error Message: "Closed Won opportunities require Amount, Close Date, and Primary Contact"
Prevent Backdating
What it prevents: Users changing historical data they shouldn't modify
Formula:
AND( ISCHANGED(Amount), PRIORVALUE(CloseDate) < TODAY() - 30)
Error Message: "Cannot change Amount on opportunities closed more than 30 days ago"
Validation Rules for Data Quality
No HTML in Text Fields
What it prevents: Junk data from form spam
Formula:
OR( CONTAINS(Description, "<script"), CONTAINS(Description, "<iframe"), CONTAINS(Description, "<a href"))
Error Message: "HTML code is not allowed in this field"
Prevent All-Caps Entry
What it prevents: SCREAMING DATA THAT LOOKS UNPROFESSIONAL
Formula:
AND( NOT(ISBLANK(LastName)), LastName = UPPER(LastName), LEN(LastName) > 1)
Error Message: "Please use standard capitalization (e.g., Smith, not SMITH)"
Website URL Format
What it prevents: Invalid URLs that break links
Formula:
AND( NOT(ISBLANK(Website)), NOT(OR( LEFT(Website, 7) = "http://", LEFT(Website, 8) = "https://" )))
Error Message: "Website must start with http:// or https://"
Prevent Duplicative Data
What it prevents: Same value in fields that should differ
Formula:
AND( NOT(ISBLANK(Phone)), NOT(ISBLANK(Fax)), Phone = Fax)
Error Message: "Phone and Fax cannot be the same number"
Advanced Patterns
Cross-Object Validation
Validate based on related record data:
Formula (Contact level validation based on Account):
AND( NOT(ISBLANK(Email)), Account.Do_Not_Contact__c = TRUE)
Error Message: "Cannot add email to contacts for Do Not Contact accounts"
Conditional Required Fields
Require fields only when another field has specific value:
Formula:
AND( ISPICKVAL(Type, "Partner"), ISBLANK(Partner_Agreement_Date__c))
Error Message: "Partner Agreement Date is required for Partner accounts"
Time-Based Validation
Prevent edits after a certain period:
Formula:
AND( NOT(ISNEW()), ISCHANGED(Amount), CreatedDate < TODAY() - 90)
Error Message: "Cannot modify Amount on records older than 90 days"
Profile-Based Exceptions
Allow certain profiles to bypass validation:
Formula:
AND( NOT($Profile.Name = "System Administrator"), [your validation logic])
Use sparingly. Better to use the bypass checkbox approach.
Testing Validation Rules
Before activating rules:
Test in Sandbox
1. Create the rule inactive
2. Test scenarios that should pass (no error)
3. Test scenarios that should fail (error displayed)
4. Verify error message is clear
5. Activate the rule
Document Test Cases
Scenario | Input | Expected Result
Valid email | test@company.com | Save succeeds
Missing @ | testcompany.com | Error displayed
No domain | test@ | Error displayed
Valid with + | test+tag@company.com | Save succeeds
Watch for Edge Cases
• What happens with blank fields?
• What about international formats?
• Are there legitimate exceptions?
When Validation Rules Cause Problems
Problem: Users Complain Rules Are Too Strict
Solution: Review whether the rule is preventing errors or preventing legitimate use cases. Adjust if needed.
Problem: Data Loads Fail
Solution: Either clean data before loading, or temporarily deactivate rules during loads (with proper oversight).
Problem: Integration Errors
Solution: Add bypass logic for integration users, or ensure integrations send valid data.
Problem: Too Many Rules Firing at Once
Solution: Users see multiple errors and get confused. Prioritize the most important validations.
Validation Rule Maintenance
Monthly Review
• Which rules are triggering most often? (Check error logs)
• Are there legitimate use cases being blocked?
• Have business processes changed, making rules obsolete?
When to Retire Rules
• The business process the rule protected has changed
• A Flow or Apex trigger now handles the validation better
• The rule is causing more problems than it prevents
Documentation
Maintain a list of all validation rules:
Rule Name | Object | Purpose | Owner | Last Reviewed
Email_Format | Contact | Prevent invalid emails | Sales Ops | 2026-03
Phone_Digits | Contact | Ensure 10-digit phones | Sales Ops | 2026-03
Review quarterly.
The Validation Rule Checklist
Before deploying a new validation rule:
• [ ] Does this prevent a data entry error (not a business decision)?
• [ ] Is the error message clear and actionable?
• [ ] Have I tested both passing and failing scenarios?
• [ ] Have I considered edge cases and exceptions?
• [ ] Is there a bypass mechanism for legitimate exceptions?
• [ ] Have I documented the rule?
• [ ] Have I communicated the rule to affected users?
Next Steps
1. Audit existing validation rules. Are they documented? Still relevant?
2. Identify your biggest data quality pain points
3. Implement validation rules addressing the top 3 issues
4. Monitor for user complaints and edge cases
5. Refine rules based on real-world usage
If you need help designing validation rules that balance data quality with user experience, Clear Concise Consulting offers Salesforce configuration services. We build validation strategies that prevent bad data without creating friction.
Jeremy Carmona is a 13x certified Salesforce Architect who has implemented validation frameworks for nonprofits, healthcare systems, and enterprises. His approach: prevent errors at entry rather than cleaning them up later.
