2

Requirement is to use "Status" field in validation rule and "Status" is being used in two validation rule which is blocking each other when we activate both rules at a time.

Requirement - 1 User needs to change "Status" to Deactivated if prior value of "Status" was Draft or Pending Approval or Rejected.

Validation Rule - 1

AND(
    NOT(OR(
    PRIORVALUE(Status__c) = 'Draft',
    PRIORVALUE(Status__c) = 'Pending Approval',
    PRIORVALUE(Status__c) = 'Rejected'
    )),
    TEXT(Status__c) <> 'Deactivated'
  )

Above solution is working perfect only when we have only this rule is Activated.

Requirement - 2 Status, Status Category, Start Date, End Date fields.

User needs to change "Status" to Blocked if prior value of "Status" was Approved and "Status Category" was Request or if prior value of "Status" was Saturated and "Status Category" was Expired and Today's date is falling in between "Start Date" & "End Date".

Validation Rule - 2

AND(
      NOT(AND(
        AND(Start_Date__c <= TODAY(), End_Date__c >= TODAY()),
        OR(
        TEXT(Status_Category__c) = 'Request',
        TEXT(Status_Category__c) = 'Expired'
        ),
        OR(
        PRIORVALUE(Status__c) = 'Approved',
        PRIORVALUE(Status__c) = 'Saturated'     
        )
      )),
    TEXT(Status__c) <> 'Blocked'
  )

Again the above solution is working perfect only when we have only this rule is Activated.

Problem Now the problem arise when we activate both rules in system or else we combine both rules in a single rule.

If Requirement - 1 is satisfied in system then no need to check Requirement - 2. I am looking for a solution like this - If(Requirement -1, true, Requirement - 2).

Below is the Single Validation rule in which i have combined both Requirement - 1 & 2 into single rule.

OR(
  AND(
    NOT(OR(
    PRIORVALUE(Status__c) = 'Draft',
    PRIORVALUE(Status__c) = 'Pending Approval',
    PRIORVALUE(Status__c) = 'Rejected'
    )),
    TEXT(Status__c) <> 'Deactivated'
  ),
  AND(
      NOT(AND(
        AND(Start_Date__c <= TODAY(), End_Date__c >= TODAY()),
        OR(
        TEXT(Status_Category__c) = 'Request',
        TEXT(Status_Category__c) = 'Expired'
        ),
        OR(
        PRIORVALUE(Status__c) = 'Approved',
        PRIORVALUE(Status__c) = 'Saturated'     
        )
      )),
    TEXT(Status__c) <> 'Blocked'
  )
)

In general salesforce doesn't recommend If Else or nest If Else in to be used in validation rule.

Looking for some suggestions.

Improve this question

2 Answers 2

Reset to default
1

I haven't looked into your individual rules or combined one in detail..

but tried to literally map your requirement statements in to the below rule..

IF(
    AND(
        OR(
            PRIORVALUE(Status__c) = 'Draft',
            PRIORVALUE(Status__c) = 'Pending Approval',
            PRIORVALUE(Status__c) = 'Rejected'
        ),
        TEXT(Status__c) != 'Deactivated'
    ), 
    TRUE,
    AND(
        OR( 
            AND(
                PRIORVALUE(Status__c) = 'Approved',
                TEXT(Status_Category__c) = 'Request'
            ),
            AND(
                PRIORVALUE(Status__c) = 'Saturated',     
                TEXT(Status_Category__c) = 'Expired'
            )
        ),
        AND(Start_Date__c <= TODAY(), End_Date__c >= TODAY()),
        TEXT(Status__c) <> 'Blocked'        
    )
)

Simplifying the rule :

IF

prior value of status is one of the 3 and current value is not Deactivated

ELSE

the combo of prior status and current status category along with current date and current status

Improve this answer
3
  • Else is not working If is working but i have added NOT() in surrounding of OR() in if condition.
    – user2423706
    Commented Aug 19, 2015 at 9:05
  • I hv update the OR to IF so if rule 1 happens it returns TRUE and wont evaluate rule 2. if rule 1 is false, then it will get into rule 2.. see if it works
    – Vamsi Krishna Gosu
    Commented Aug 19, 2015 at 9:19
  • I tried to use the updated Solution provided by you but none of the IF & ELSE is working. So, i tried to change IF to be modified to work as NOT(OR(condition)), TEXT(Apttus__Status__c) <> 'Deactivated'. Else condition is still not working. The reason behind this is once we try to evaluate ELSE the IF part is blocking.
    – user2423706
    Commented Aug 19, 2015 at 10:19
0

Updated:

  OR(((TEXT(PRIORVALUE(  Status__c ))='Draft'|| 
    TEXT(PRIORVALUE(  Status__c ))='Pending Approval'|| 
    TEXT(PRIORVALUE(  Status__c ))='Rejected')) 
    &&(TEXT(  Status__c )!='Deactivated') , 

   (((TEXT(PRIORVALUE(Status__c))='Approved'||
   TEXT(PRIORVALUE(Status__c))='Saturated') || 
   (TEXT(Status_Category__c )='Request'||
   TEXT(  Status_Category__c )='Expired') &&    
   (Start_Date__c <=today()&&  End_Date__c >= TODAY()))  && 
   TEXT(  Status__c )!='Blocked'))
Improve this answer
7
  • Thanks for suggestion but this is an error prone and not working!!
    – user2423706
    Commented Aug 19, 2015 at 10:22
  • @user2423706, We are missing the TEXT() in PRIORVALUE(). Try the above updated code above.
    – Anitha J J
    Commented Aug 19, 2015 at 13:29
  • 1st condition is being satisfied and working as expected but 2nd one is causing problem and it's not working as expected.
    – user2423706
    Commented Aug 20, 2015 at 5:12
  • @user2423706, So the 2nd rule is User should select blocked if the prior value of status is approved/saturated or the prior value of status category is expired/request and the date should fall between start and end date..Is this right?
    – Anitha J J
    Commented Aug 20, 2015 at 6:11
  • Yes. You have interpreted right. So, this 2nd one is not working.
    – user2423706
    Commented Aug 20, 2015 at 6:32

You must log in to answer this question.

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