0

I'm receiving an error for a validation rule. Any help would be great! Essentially I'm trying to create a validation on the opportunity record to say that If the stage= Closed Won or Sent Membership Agreement and the Profile = System Admin then we should require a Yes/No value for a field

  1. Make three fields required if stage = Closed Won or Sent Membership Agreement

  2. Only accept a Yes/No Value from one field

  3. Only work for people with a System Admin profile

Formula

AND(
    ISPICKVAL( StageName ,'Closed Won'),
    ISPICKVAL(StageName, "Sent Membership Agreement"),
    ,ISBLANK(Existing_Aetna_Optum_Cigna_Clients__c),
    ISBLANK(Accepts_Insurance_before_ABC__c),
    ISBLANK(Looking_For_Practice_Growth__c), 
    AND(
        $Profile.Name <> "System Administrator", 
        AND Accepts_Insurance_before_ABC__c <> Yes,No
    )
1
  • 1
    You have syntax errors - double commas, <> Yes,No is invalid, missing final paren for the initial AND, last AND is invalid syntax.
    – cropredy
    Commented Feb 21, 2022 at 18:22

1 Answer 1

0

Formatting your validation rule as if it were code (i.e. splitting individual components onto separate lines, and using indentation) is a simple and helpful way to work out a wide range of syntax issues.

By applying that to your formula, I can see

  • You have an extra comma between the second ISPICKVAL() and the first ISBLANK()
  • You have an extra AND in your nested AND()
  • You're trying to compare multiple values with a single operator (Accepts_Insurance_before_ABC__c <> Yes,No)
  • Your outermost AND() is missing a closing parenthesis

There are also some semantic (logical) issues

  • You're looking for Stagename = closed won and Stagename = Sent Membership Agreement (instead of if the Stagename is one or the other)
  • You're excluding the system administrator profile instead of enforcing this rule for that profile

When writing validation rules, it helps to remember that they're actually invalidation rules. They tell you when data is invalid. With that in mind, you're looking for something closer to this:

AND(
    /* A single field can only take a single value, so you need OR */
    OR(
        ISPICKVAL(StageName, 'Closed Won'),
        ISPICKVAL(StageName, 'Sent Membership Agreement')
    ),
    /* Only enforce if the user is a sysadmin, need to use '=' here */
    $Profile.Name = 'System Administrator',
    /* We should complain if _either_ of the first two fields are blank,*/
    /*   if the final field is blank, or if it isn't blank but not yes/no */
    OR(
        ISBLANK(Existing_Aetna_Optum_Cigna_Clients__c),
        ISBLANK(Looking_For_Practice_Growth__c),
        ISBLANK(Accepts_Insurance_before_ABC__c),
        AND(
            Accepts_Insurance_before_ABC__c <> 'yes',
            Accepts_Insurance_before_ABC__c <> 'no'
        )
    )
)
2
  • Thanks. Still running into some issues. Am I missing something? AND(OR( ISPICKVAL(StageName, 'Closed Won'), ISPICKVAL(StageName, 'Sent Membership Agreement'), $Profile.Name = 'System Administrator', OR( ISBLANK(Existing_Aetna_Optum_Cigna_Clients__c), ISBLANK(Looking_For_Practice_Growth__c), ISBLANK(Accepts_Insurance_before_ABC__c), AND( Accepts_Insurance_before_ABC__c <> 'yes', Accepts_Insurance_before_ABC__c <> 'no' ) ) ) Commented Feb 21, 2022 at 19:06
  • @MonicaMickel You should copy/paste that formula into a text editor and break things onto individual lines (and indent) similar to what I have done. Then compare that to the example I've provided to see if there are any differences (and where they are). I can tell you right now that you're missing a close paren somewhere.
    – Derek F
    Commented Feb 21, 2022 at 19:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .