0

I have two fields Quarter(picklist with the value Q1,Q2,Q3 and Q4) and Year(text field).

I'm creating a Validation rule to ensure the values entered are within the current/following quarter.

For example if the current quarter is Q3 then it should allow Q3 2022 and Q4 2022 (and no other values) and if the current quarter is Q4 then allow Q4 2022 and Q1 2023.

I made the following validation rule but it allows too many values, i.e. Q3 2022 , Q4 2022, Q1 2023, Q2 2023, Q3 2033

OR (
AND (
VALUE( Submitted_Year__c ) <> YEAR( TODAY() ),
VALUE( Submitted_Year__c ) <> YEAR( TODAY() ) + 1
),

AND (
VALUE( Submitted_Year__c ) = YEAR( TODAY() ),
VALUE( RIGHT( TEXT( Quarter__c ), 1 ) ) <> CEILING( MONTH( TODAY() )/3 ),
VALUE( RIGHT( TEXT( Quarter__c ), 1 ) ) <> CEILING( MONTH( TODAY() )/3 + 1 )
)
) 
1
  • Welcome to Salesforce Stack Exchange (SFSE)! I see background and code (formula), which is good; but I do not see a specific issue, error, or question. What results are you getting with the VR that you tried? A syntax or other error? Unexpected results? Please edit your question to add more/better detail as to what you are getting and how that is different from what you are expecting.
    – Moonpie
    Sep 21, 2022 at 14:24

1 Answer 1

0

I like to approach validation rules like this one by putting what IS allowed in the validation rule, and then putting a 'NOT' around it. In this case there are basically 3 scenarios we should support:

  1. same year and same quarter
  2. same year and current quarter + 1
  3. next year and current quarter - 3

In this case it would result in something like this:

NOT(
    OR(
        AND(
            VALUE( year__c ) = YEAR( TODAY() )
        ,
            OR(
                VALUE( RIGHT( TEXT( quarter__c ), 1 ) ) = CEILING( MONTH( TODAY() )/3 )
                ,
                VALUE( RIGHT( TEXT( quarter__c ), 1 ) ) = CEILING( MONTH( TODAY() )/3 + 1 )
            )
        )
    ,
        AND(
            VALUE( year__c ) = YEAR( TODAY() + 1 )  
            ,
            VALUE( RIGHT( TEXT( quarter__c ), 1 ) ) = CEILING( MONTH( TODAY() )/3 -3 )
        )
    )
)

Note that in order to test scenario 3, you'll need to wait 11 days until it's q4, or test it with a custom date field and replace TODAY() by that date field until you know it's working correctly, and then put TODAY() back.

1
  • Thanks for the solution and explaining it well.
    – user122269
    Sep 21, 2022 at 19:18

You must log in to answer this question.

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