Validation Rule on picklist under opportunities

Question

There are 2 requirements:

1. standard users can’t update the status of a record beyond “open won” (so that only SF Admins or auto processes update “registered” and the stages beyond registration. Validation rule for this:

~

 AND( 

    $Profile.Name <> "System Administrator", 
    ISCHANGED(StageName),

    OR(TEXT(StageName) = "Registered",
    TEXT(StageName) = "Pre-Certified",
    TEXT(StageName) = "Documentation, Submission",
    TEXT(StageName) = "Performance verified",
    TEXT(StageName) = "Appeals",
    TEXT(StageName) = "Certification/ Closed Won")

    )

Requirement 2: Standard users cannot update the status of a record that is already Registered (or post-registered phase). For us, some Opp was already “Registered” but some standard user was able to change the record to “Open Won.”

I am looking for the validation rule of requirement 2 and which should align with first requirement also.

Thanks, Please guide.

Answer 1

There are 2 validation rules.

Under opportunities there is a picklist called STAGE, the standard users (anyone other than Salesforce admin) should not be able to select and save the values in the picklist after the value : "registered"

Ans: You need to mention which particular stageNames standard users will not be allowed to select.

Rule will be like this (put all the stageNames with || condition) and show the error message when following is true:

AND(
    ISCHANGED(StageName), 
    (TEXT(StageName) == 'Registered' || TEXT(StageName) == 'Closed Won'), 
    $Profile.Name <> 'System Administrator'
)

When new opportunity is being created, in first place only it does not allow to select the value "Registered" in the Stage picklist.

Ans: Show the error message when following is true.

ISNEW() && (TEXT(StageName) == 'Registered')

Standard users cannot update the status of a record that is already Registered (or post-registered phase). For us, some Opp was already “Registered” but some standard user was able to change the record to “Open Won.”

Ans: You need to check PRIORVALUE(field) in the OR condition

AND( 

$Profile.Name <> "System Administrator", 
ISCHANGED(StageName), 
OR(
PRIORVALUE(StageName) = "Registered",
TEXT(StageName) = "Registered",
TEXT(StageName) = "Pre-Certified",
TEXT(StageName) = "Documentation, Submission",
TEXT(StageName) = "Performance verified",
TEXT(StageName) = "Appeals",
TEXT(StageName) = "Certification/ Closed Won")

)

Answer 2

Formula below won't let any user beside System Administrator to set Stage value to any from this list ('Registered' , 'Pre-Certified' , 'Documentation, Submission' , 'Performance verified' , 'Appeals' , 'Certification/ Closed Won') and also to change it when it's set already to any from the list:

AND( 
 $Profile.Name <> "System Administrator", 
 OR(
  ISCHANGED(StageName),
  ISNEW()
 ),
 OR(
  CONTAINS("Registered:Pre-Certified:Documentation, Submission:Performance verified:Appeals:Certification/ Closed Won", TEXT(StageName)), 
  CONTAINS("Registered:Pre-Certified:Documentation, Submission:Performance verified:Appeals:Certification/ Closed Won", TEXT(PRIORVALUE(StageName))) 
 )
)

The only downside of it is that any of your other Stages shouldn't have a value that is a part of this validated string - e.g.'Submission' - in this case you have to use CASE instead of CONTAINS:

CASE( PRIORVALUE(StageName),
 'Registered', 1,
 'Pre-Certified', 1,
 'Documentation, Submission', 1,
 'Performance verified', 1,
 'Appeals', 1,
 'Certification/ Closed Won', 1,
0) = 1