0

I am trying to add some editing limits of opptys in 2 ways (this is on top of other validations in the oppty today):

  1. Sales team can edit the oppty while they are in the same fiscal quarter
  2. Region Managers can edit the oppty for this and the previous quarter.

The following Validation rule is on the oppty, however, I can't manage to get the 2nd rule to apply. I don't know how to make it happen:

  • There are other rules in the process, that prevents errors if NOT system admin, and/or oppty is in closed won stage.

  • The fourth condition in the rule is the one I need to add validtion for the certain profile that can do the change.

Q: The field checked: PO_Processed_date is a field that gets it's value only when the oppty is updated to: "PO Processed" Stage - Until than it's an empty field: -- Do I need to add a validation for that within the rule?

OR (

AND(  OR ( ISPICKVAL(PRIORVALUE(StageName),"Closed Won"),
             Order_Processed__c is checked),
        NOT ($User.Id="00e20000xxxxxxG" ))
  ,
AND ( $Profile.Id = "00e20000xxxxxxt",  ///PARTNER
      ISPICKVAL(PRIORVALUE(StageName),"Closed Won")))

  ,
AND ( ISPICKVAL(PRIORVALUE(StageName),"PO Processed")),
      AND(
  NOT( FLOOR((MONTH(Today())-1)/3) + 1 = FLOOR((MONTH(CloseDate)-1)/3) + 1) ,
       Year(Today()) == Year (CloseDate)),
  )
  ,

**// Add validation for current and previous quarter for Profile XXXXX only**
AND ( ISPICKVAL(PRIORVALUE(StageName),"PO Processed")),
      AND(
  NOT( FLOOR((MONTH(Today())-1)/3) + 1 = FLOOR((MONTH(CloseDate)-1)/3) + 1) ,
       Year(Today()) == Year (CloseDate)),
  )

1 Answer 1

2

First of all, I'd consider breaking these into separate rules. You can have a few hundred rules, so don't feel like you have to cram it all into a single validation formula. Users may receive multiple error messages, but at least you'd simplify the logic.

As for the task at hand, you might find it easier to convert the month and year into a "number of quarters since 0 A.D." in order to make it easier to calculate the value.

Current Fiscal Quarter:

FLOOR(YEAR(CloseDate)*4+(MONTH(CloseDate)-1)/3) -
FLOOR(YEAR(TODAY())  *4+(MONTH(TODAY())  -1)/3) < 0

For example, Q1 2014 is Q 8056. If the close date is 06/2014, and today is in 01/2014, the formula evaluates as this:

FLOOR(2014*4 + (6-1) /3) - FLOOR(2014*4 + (1-1) /3) < 0
FLOOR(8056   + 5     /3) - FLOOR(8056   + 0     /3) < 0
FLOOR(8056   + 1.666667) - FLOOR(8056   + 0       ) < 0
FLOOR(8057.666667      ) - FLOOR(8056   + 0       ) < 0
8057                     - 8056                     < 0
                                                  1 < 0

Since 1 is not less than 0, the validation rule will not trigger.

Similarly, for the current or previous fiscal quarter, you can use:

FLOOR(YEAR(CloseDate)*4+(MONTH(CloseDate)-1)/3) -
FLOOR(YEAR(TODAY())  *4+(MONTH(TODAY())  -1)/3) < -1

This approach is doubly useful for the second scenario, because it cuts out an ugly if-then you have to use to determine if a date is Q 4 of last year versus Q 1 of this year.

You can combine the two validations into a single snippet, like this:

FLOOR(YEAR(CloseDate)*4+(MONTH(CloseDate)-1)/3) -
FLOOR(YEAR(TODAY())  *4+(MONTH(TODAY())  -1)/3) <
IF($Profile.Id="Privileged-Profile-Id", -1, 0)

Your parenthesis are mismatched, so I'll leave that as an exercise for you to put this code into the correct place.

1
  • followed your suggestions. Like a Xmas present :-)
    – Saariko
    Commented Dec 31, 2013 at 8:16

You must log in to answer this question.

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