Data Management: Scenario 1 Solution

[ScenarioSolutionIntro]

[RCNotLoggedInNotAdmin]

[/RCNotLoggedInNotAdmin]

[RCAdmin]

[sc:ScenarioSolution ]

We will create a validation rule to ensure that opportunities cannot be saved when the following is true: a) the opportunity stage is changed, b) the opportunity stage is closed won, and c) the close date is less than 30 days prior to today’s date.

[sc:ScenarioSolutionSteps ]

  1. Navigate to Setup –> Customize –> Opportunities –> Validation Rules
  2. Click New.
  3. Populate the validation rule with the following formula:
    Note: You may need to manually type this formula in; copy/paste will likely cause an error in the formula editor.
    AND(
    ISCHANGED( StageName ),
    IsWon == TRUE,
    CloseDate < (TODAY() – 30))1-30-2013 2-09-22 PM
  4. Click Save.

Formula Explained

In order to prevent validation from triggering in the future (e.g. the opportunity is edited a year after it is won), add the logic “ISCHANGED(StageName)”.

Instead of using TEXT() or ISPICKVAL() to match the stage name of the opportunity, I used the field IsWon, which is TRUE if the associated opportunity stage is won.  This way if the opportunity stage is renamed from “Closed Won” to “Sold”, my validation rule still works.

Date math works is calculated in number of days.  Therefore calculating the proper date range is as simple as subtracting Close Date – 30.

Finally, the AND() function joins all of formula criteria together.

[/RCAdmin]

38 thoughts on “Data Management: Scenario 1 Solution”

  1. Before I remembered the “ISCHANGED” function, I was trying this logic (just for the part of defining whether the value of the stage has changed) – PRIORVALUE(StageName) TEXT(StageName)

  2. John,
    I have used the following formula for validation rule,
    IF(ISPICKVAL( StageName , “Closed Won”) , CloseDate < (TODAY() – 30), false)
    It works, do you think it is advisable to use this?
    BTW, could you please let me know where to find the commands like the function call (TODAY()) for example….

    BTW, I do understand why you are using IsWon.

    Thanks

    1. Take a look at the formula section for more info on the syntax, etc.

      You don’t need the IF statement in the validation rule… I’ve never actually used a false statement like that to “exit” the validation rule. But if it works.. it works 🙂

      Ultimately so long as the logic executes correctly, that’s the most important thing. Technically, the validation rule might be more “efficient” without the IF statement, but it won’t really make a difference. “Closed Won” versus IsWon would if you have multiple stages that are “won”.

  3. I’m not understanding what this validation rule is supposed to be doing.

    “Must be within the last 30 days or in the future” sounds like it can be anytime.

      1. So I played around with this, and I just want to make sure I’m following the logic. This prevents users from backdating the status of an opportunity to Closed/Won? What is the business logic of this? Sorry I’m having a rough time understanding why this would be put it play. Thank you.

  4. My only comment is about the field IsWon missing from the org, the validation will not work unless the field has an updated to tie to the StageName. I think it would be a better exercise to add this field into mix. So the validation is complete.

  5. Perhaps worth pointing out that if creating a new opportunity and setting to Closed Won straight away with a Close Date +30 days in the past, the validation rule in its current design doesn’t run. It only runs and displays the error when the Stage has changed to Closed Won from a previous open stage, and the Close Date is +30 days in the past.

  6. Will the admin test ask evaluation of specific formulas or syntax as correct or incorrect in the admin test? The formatting is still Greek to me!

    1. Thanks for the heads up – it appears to be a formatting issue. When you copy the “-” from the website it causes an error. If you remove the “-” and type it in manually it worked for me. Try typing the formula in manually if that doesn’t work – the logic is correct. I added a note on the site to address this problem.

  7. John –

    I’ve found that IsWon returns true or false without the need for a comparison operator. Here’s my formula, and it works the same as yours:

    AND( ISCHANGED( StageName ), IsWon , CloseDate < TODAY()-30 )

    That is to say "==TRUE" is redundant in this case.

    -Paul

  8. Hi John,

    Is there is any way to identify what portion of formula has throwing error. There is a syntax error When i tried to save validation rule with below formula.

    AND(ISCHANGED( StageName ),IsWon == TRUE,
    CloseDate (TODAY() + 30))

    Regards,
    Sreekanth

  9. Hi John,
    I think you got carried away as your initial expression is sufficient for the requirement of the scenario, i.e., “the close date must be within 30 days in the past, or a date in the future”. The trigger here should be any date older than 30 days from today – only. A date in the future is already newer so no need to include that in the formula.
    My only problem is the “IsWon==True” portion. Is this a global variable? I can’t find it under the Opportunity fields.
    Thanks.

    1. Hi Alhaji,

      If your validation rule was ONLY close date older than 30 days in the past, then you would trigger this validation rule if you were updating the opportunity in the future (e.g. changing the description a year after it is won).

      IsWon is field on the opportunity object (although it is not listed under the fields section within object management).

      Cheers,

      John

      1. Hi John,

        Just not clear on the “IsWon” field. If I can’t see it, how would I know it was available to use? Where do you look to find this? No sure if I should have already been aware of this.

        -jim

  10. Hi John,

    Question – I am not able to tell if your formula is covering the success criteria of when the stage is closed won and the date is blank. Shouldn’t it also have Or(ISBLANK(CloseDate),CloseDate<(TODAY() – 30)) ?

    Thanks,

  11. Hi John
    I’m not good with formula and wondered does this formula also account for when the stage is changed to Won and has a date in the future? Sorry – couldn’t see mention of it in your formula. Or is it not required?
    Thanks

    1. Hi Sue- no this does not give an error the close date is in the future. That’s definitely a good idea to build in to the rule. I think this would do it (although I haven’t tested it):

      AND(
      ISCHANGED( StageName ),
      IsWon == TRUE,
      CloseDate < (TODAY() – 30) || CloseDate > (TODAY() + 30)
      )

      1. I put that in just to see what would happen, and I get “error — syntax error”. Nothing more specific. I tried putting parentheses around the two halves of the OR and still got the syntax error. I’ve no idea what’s wrong with the syntax. Any ideas?

        1. Never mind. I didn’t notice the highlighting. It’s that you have a double dash instead of a single for the minus sign. Once I fixed that, I got no errors found. Now I’ll go test to see if it works as expected! This was a fun module (I’ve already taken and aced the quiz). Thanks!

      2. Unless I am reading the success criteria incorrectly, it appears that any future date from today should not cause an error. It says “within 30 days in the past, or a date in the future” with no limit.

Leave a Reply