I'm trying to create a validation rule requiring 3 fields to be populated for 3 profiles. Here is what i have so far. I gave up after continued syntax errors. I need both Zone Approver Fields and the Sales Report Label field populated for these 3 profiles. The zone approver fields are type "hierarchy" and the Sales Report Label field is a picklist. Thank you in advance!

AND(OR(ProfileId ="00e30000001iKyY",ProfileId="00e30000001iKzE",ProfileId="00e30000001iL0B"), 
OR(
LEN(Zone_1_Approver__r.Id)=0),
LEN(Zone_2_Approver__r.Id)=0),
Text(Sales_Report_Label__c)= ""))
  • 2
    What were the syntax errors? Other users might have trouble reproducing them exactly – battery.cord Oct 15 at 17:11
  • They kept changing with every update. I received extra "," here: LEN(Zone_2_Approver__r.Id)=0), which i know i need. Then i would remove it and get a different error missing ")". Also received "extra LEN". Basically every time i changed something that errored i would get a different error. – Kelly Oct 15 at 17:17

If your'e trying to check for null values you need to wrap your field with ISBLANK

AND(OR( $Profile.Name ="profile1",$Profile.Name="profile2",$Profile.Name="profile3"), 
   OR(
   ISBLANK(Zone_1_Approver__c),
   ISBLANK(Zone_2_Approver__c),
   ISBLANK(Text(Sales_Report_Label__c))))

As a best practice try using profile name instead of hard coded Id's

  • 1
    Just so you know, you have the option to "accept" this answer to indicate it solved your issue. That gives you and him both a small reputation bump and visually highlights the question as resolved. It's always up to you to accept or not. – David Reed Oct 15 at 19:02

As written, the formula simply has unbalanced parentheses. Treat formulas like algebra, or Excel formulas - you always must balance parentheses and separate formula parameters with commas.

AND(
    OR(ProfileId ="00e30000001iKyY",ProfileId="00e30000001iKzE",ProfileId="00e30000001iL0B"), 
    OR(
        LEN(Zone_1_Approver__r.Id)=0), // <--- extra parenthesis
        LEN(Zone_2_Approver__r.Id)=0), // <--- extra parenthesis
        Text(Sales_Report_Label__c) = ""
    )
)

You should also, however, see @codeyinthecloud's answer. It's best practice to use ISBLANK() on the relationship field itself rather than relying on behavior when you refer across a non-populated relationship. Additionally, it would be best to use a Custom Permission rather than hard-coding profile references.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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