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.

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

  • Else is not working If is working but i have added NOT() in surrounding of OR() in if condition. – user2423706 Aug 19 '15 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 Aug 19 '15 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 Aug 19 '15 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'))
  • Thanks for suggestion but this is an error prone and not working!! – user2423706 Aug 19 '15 at 10:22
  • @user2423706, We are missing the TEXT() in PRIORVALUE(). Try the above updated code above. – Anitha J J Aug 19 '15 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 Aug 20 '15 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 Aug 20 '15 at 6:11
  • Yes. You have interpreted right. So, this 2nd one is not working. – user2423706 Aug 20 '15 at 6:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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