Ask Search:
Travis IcenogleTravis Icenogle 

Multiple AND OR Statements Nested Validations

Thoughts?

Trying to state the following:

If Primary Coverage equals "ACA"
Require additional fields.
And If Member Status equals "ACTIVE"
Require even more fields.

Getting syntax errors. 
AND(  
   ISPICKVAL(X2020_Primary_Coverage__c, "ACA"),
 OR( 
   ISBLANK(Birthdate), 
   ISBLANK(County__c), 
   ISBLANK(Email), 
   ISBLANK(TEXT(Gender__c)), 
   ISBLANK(MailingStreet), 
   ISBLANK(MailingCity),
   ISBLANK(MailingState),
   ISBLANK(MailingPostalCode),
   ISBLANK(MailingCountry),
   ISBLANK(Phone),  
   ISBLANK(TEXT(Statement_of_Understanding__c)),
   ISBLANK(TEXT(X2020_Member_Status__c)),
   ISBLANK(TEXT(Tax_Rate__c)),
   ISPICKVAL(req_ver_docs_count__c,""),
   ISBLANK(TEXT(Verified__c)),

AND(
   ISPICKVAL(X2020_Member_Status__c, “ACTIVE”),
OR(
ISBLANK(Expected_AGI_2020__c),
   ISBLANK(X2020_Health_Plan_Cost__c), 
   ISBLANK(X2020_Health_Plan_Name__c), 
   ISBLANK(TEXT(Housing_Allowance__c)),
   ISBLANK(TEXT(Marriage_status__c)),
   ISBLANK(TEXT(member_income__c)),
   ISBLANK(Member_SSN__c), 
   ISBLANK(Plan_Choice_Reasoning__c), 
   ISBLANK(Pre_existing_conditions__c), 
   ISBLANK(TEXT(Receiving_Tax_Credits__c)), 
   ISBLANK(TEXT(SEP__c)), 
   ISBLANK(TEXT(Tobacco__c)), 
   ISBLANK(TEXT(X2020_Initial_Payment__c)),
   ISBLANK(TEXT(X2020_Payment_Type__c)), 
   ISBLANK(TEXT(X2020_Advisor__c))
))))

Please?
Best Answer chosen by Travis Icenogle
Narender SinghNarender Singh
I would sugges you to split this into two different VRs. This gives you the flexibility to have tailored error messages.
VR 1:
AND(  
   ISPICKVAL(X2020_Primary_Coverage__c, "ACA"),
 OR( 
   ISBLANK(Birthdate), 
   ISBLANK(County__c), 
   ISBLANK(Email), 
   ISBLANK(TEXT(Gender__c)), 
   ISBLANK(MailingStreet), 
   ISBLANK(MailingCity),
   ISBLANK(MailingState),
   ISBLANK(MailingPostalCode),
   ISBLANK(MailingCountry),
   ISBLANK(Phone),  
   ISBLANK(TEXT(Statement_of_Understanding__c)),
   ISBLANK(TEXT(X2020_Member_Status__c)),
   ISBLANK(TEXT(Tax_Rate__c)),
   ISPICKVAL(req_ver_docs_count__c,""),
   ISBLANK(TEXT(Verified__c))
  )
)
VR 2:
 
AND(
   ISPICKVAL(X2020_Member_Status__c, “ACTIVE”),
OR(
ISBLANK(Expected_AGI_2020__c),
   ISBLANK(X2020_Health_Plan_Cost__c), 
   ISBLANK(X2020_Health_Plan_Name__c), 
   ISBLANK(TEXT(Housing_Allowance__c)),
   ISBLANK(TEXT(Marriage_status__c)),
   ISBLANK(TEXT(member_income__c)),
   ISBLANK(Member_SSN__c), 
   ISBLANK(Plan_Choice_Reasoning__c), 
   ISBLANK(Pre_existing_conditions__c), 
   ISBLANK(TEXT(Receiving_Tax_Credits__c)), 
   ISBLANK(TEXT(SEP__c)), 
   ISBLANK(TEXT(Tobacco__c)), 
   ISBLANK(TEXT(X2020_Initial_Payment__c)),
   ISBLANK(TEXT(X2020_Payment_Type__c)), 
   ISBLANK(TEXT(X2020_Advisor__c))
  )
)

 

All Answers

Narender SinghNarender Singh
I would sugges you to split this into two different VRs. This gives you the flexibility to have tailored error messages.
VR 1:
AND(  
   ISPICKVAL(X2020_Primary_Coverage__c, "ACA"),
 OR( 
   ISBLANK(Birthdate), 
   ISBLANK(County__c), 
   ISBLANK(Email), 
   ISBLANK(TEXT(Gender__c)), 
   ISBLANK(MailingStreet), 
   ISBLANK(MailingCity),
   ISBLANK(MailingState),
   ISBLANK(MailingPostalCode),
   ISBLANK(MailingCountry),
   ISBLANK(Phone),  
   ISBLANK(TEXT(Statement_of_Understanding__c)),
   ISBLANK(TEXT(X2020_Member_Status__c)),
   ISBLANK(TEXT(Tax_Rate__c)),
   ISPICKVAL(req_ver_docs_count__c,""),
   ISBLANK(TEXT(Verified__c))
  )
)
VR 2:
 
AND(
   ISPICKVAL(X2020_Member_Status__c, “ACTIVE”),
OR(
ISBLANK(Expected_AGI_2020__c),
   ISBLANK(X2020_Health_Plan_Cost__c), 
   ISBLANK(X2020_Health_Plan_Name__c), 
   ISBLANK(TEXT(Housing_Allowance__c)),
   ISBLANK(TEXT(Marriage_status__c)),
   ISBLANK(TEXT(member_income__c)),
   ISBLANK(Member_SSN__c), 
   ISBLANK(Plan_Choice_Reasoning__c), 
   ISBLANK(Pre_existing_conditions__c), 
   ISBLANK(TEXT(Receiving_Tax_Credits__c)), 
   ISBLANK(TEXT(SEP__c)), 
   ISBLANK(TEXT(Tobacco__c)), 
   ISBLANK(TEXT(X2020_Initial_Payment__c)),
   ISBLANK(TEXT(X2020_Payment_Type__c)), 
   ISBLANK(TEXT(X2020_Advisor__c))
  )
)

 
This was selected as the best answer
Coby PressCoby Press
Try this bad boy
AND(
AND(  
   ISPICKVAL(X2020_Primary_Coverage__c, "ACA"),
 OR( 
   ISBLANK(Birthdate), 
   ISBLANK(County__c), 
   ISBLANK(Email), 
   ISBLANK(TEXT(Gender__c)), 
   ISBLANK(MailingStreet), 
   ISBLANK(MailingCity),
   ISBLANK(MailingState),
   ISBLANK(MailingPostalCode),
   ISBLANK(MailingCountry),
   ISBLANK(Phone),  
   ISBLANK(TEXT(Statement_of_Understanding__c)),
   ISBLANK(TEXT(X2020_Member_Status__c)),
   ISBLANK(TEXT(Tax_Rate__c)),
   ISPICKVAL(req_ver_docs_count__c,""),
   ISBLANK(TEXT(Verified__c))
)
),

AND(
   ISPICKVAL(X2020_Member_Status__c, “ACTIVE”),
OR(
ISBLANK(Expected_AGI_2020__c),
   ISBLANK(X2020_Health_Plan_Cost__c), 
   ISBLANK(X2020_Health_Plan_Name__c), 
   ISBLANK(TEXT(Housing_Allowance__c)),
   ISBLANK(TEXT(Marriage_status__c)),
   ISBLANK(TEXT(member_income__c)),
   ISBLANK(Member_SSN__c), 
   ISBLANK(Plan_Choice_Reasoning__c), 
   ISBLANK(Pre_existing_conditions__c), 
   ISBLANK(TEXT(Receiving_Tax_Credits__c)), 
   ISBLANK(TEXT(SEP__c)), 
   ISBLANK(TEXT(Tobacco__c)), 
   ISBLANK(TEXT(X2020_Initial_Payment__c)),
   ISBLANK(TEXT(X2020_Payment_Type__c)), 
   ISBLANK(TEXT(X2020_Advisor__c))
)))

Thanks a lot,
Cobes :D

Send us a connection invite & leave some feedback on my LinkedIn!
https://www.linkedin.com/in/coby-press-572ba5177/
Coby PressCoby Press
You might also want to change that ISPICKVAL(req_ver_docs_count__c,""), to an ISBLANK

Cheers
Steve MolisSteve Molis
Personally I try to avoid writing "One Size Fits All" Validation Rules.  It makes covering every scenario and exemption, and loophole more difficult.  Also you can only have 1 error message per rule, so you end up having to create a generic error message. 
Steve MolisSteve Molis
I would write this as 2 Rules
 
AND(  
TEXT(X2020_Primary_Coverage__c) = "ACA",
	OR( 
   ISBLANK(Birthdate), 
   ISBLANK(County__c), 
   ISBLANK(Email), 
   ISBLANK(TEXT(Gender__c)), 
   ISBLANK(MailingStreet), 
   ISBLANK(MailingCity),
   ISBLANK(MailingState),
   ISBLANK(MailingPostalCode),
   ISBLANK(MailingCountry),
   ISBLANK(Phone),  
   ISBLANK(TEXT(Statement_of_Understanding__c)),
   ISBLANK(TEXT(X2020_Member_Status__c)),
   ISBLANK(TEXT(Tax_Rate__c)),
   ISBLANK(TEXT(req_ver_docs_count__c)),
   ISBLANK(TEXT(Verified__c))
   )
)

and
 
AND(
TEXT(X2020_Member_Status__c) = "ACTIVE",
	OR(
	ISBLANK(Expected_AGI_2020__c),   
	ISBLANK(X2020_Health_Plan_Cost__c), 
	ISBLANK(X2020_Health_Plan_Name__c), 
	ISBLANK(TEXT(Housing_Allowance__c)),
	ISBLANK(TEXT(Marriage_status__c)),
	ISBLANK(TEXT(member_income__c)),
	ISBLANK(Member_SSN__c), 
	ISBLANK(Plan_Choice_Reasoning__c), 
	ISBLANK(Pre_existing_conditions__c), 
	ISBLANK(TEXT(Receiving_Tax_Credits__c)), 
	ISBLANK(TEXT(SEP__c)), 
	ISBLANK(TEXT(Tobacco__c)), 
	ISBLANK(TEXT(X2020_Initial_Payment__c)),
	ISBLANK(TEXT(X2020_Payment_Type__c)), 
	ISBLANK(TEXT(X2020_Advisor__c))
	)
)

​​​​​​​
Travis IcenogleTravis Icenogle
Thank you all.  I broke it up into multiple VR's as suggested.  

Travis