Skip to main content
Liz Parker (Digital Space) 님이 #Formulas에 질문했습니다

I’m trying to implement a validation rule which looks at two fields on an opportunity however, I’m struggling to expand it incorporate all the conditions I need it to.

 

It relates to the Opportunity Stage and a custom picklist field, OCR Stages.

 

If the Opportunity moves to a particular stage and the form is attempted to be saved, then the OCR stage must be at a certain stage.

 

The conditions are:

 

  • If Stagename is “Lead” or “Opportunity Identified” or “Opportunity Qualified” or “Early Negotiation” then OCR Stage cannot blank and OCR Stage must be one of “OCR Sign on Required” or “OCR Sign On Passed, Sign Off Required”,
  • If Stagename is Proposal Submitted or Shortlisted then OCR Stage must be one of “OCR Sign On Passed, Sign Off Required” or “OCR Not Required”,
  • If stagename is Final Negotiation then OCR Stage must be one of “OCR Sign Off Passed” or “OCR Not Required”.

 

I’ve managed to implement the rule for the first bullet point using the below:

 

AND(

 ISCHANGED(StageName),

 

 NOT(ISPICKVAL( StageName , 'Lead')),

 NOT(ISPICKVAL(StageName, 'Opportunity Identified')),

 NOT(ISPICKVAL(StageName, 'Opportunity Qualified')),

 NOT(ISPICKVAL(StageName, 'Early Negotiation')),

 

 OR(

 ISBLANK(TEXT(OCR_Stages__c)),

 ISPICKVAL(OCR_Stages__c, 'OCR Failed'),

 ISPICKVAL(OCR_Stages__c, 'OCR Sign off Passed'),

 ISPICKVAL(OCR_Stages__c, 'OCR Not Required')

)

 )

 

The part I am now struggling with is successfully adding in the last two bullet points and not affect the first part of the rule. I would expect the need for an IF statement however, I just cannot work this out. Has anyone implemented anything similar and able to help?

답변 16개
  1. 2023년 11월 15일 오후 4:03

    I've got it working now:

     

    NOT(OR(

    AND(

    CASE(StageName,"Lead",1,"Opportunity Identified",1,"Opportunity Qualified",1,"Early Negotiation",1,0)=1,

    CASE(OCR_Stages__c, "OCR Sign on Required",1,"OCR Sign On Passed, Sign Off Required",1,0)=1

    ),

    AND(

    CASE(StageName,"Proposal Submitted",1,"Shortlisted",1,0)=1,

    CASE(OCR_Stages__c,"OCR Sign On Passed, Sign Off Required",1,"OCR Not Required",1,0)=1

    ),

    AND(

    CASE(StageName,"Final Negotiation",1,0)=1,

    CASE(OCR_Stages__c, "OCR Sign Off Passed",1,"OCR Not Required",1,0)=1

    )

    ))

     

    Thank you @Eric Praud, @Eric Burté and @Steve Molis for all chipping in to help out on this one. I really appreciate it!

0/9000