Data Management: Scenario 1 Solution

The solution section provides an overview of how to solve this scenario, and why that solution was chosen.  The Solution Steps section immediately following outlines the exact steps used to replicate the solution.

To view the remainder of this content, you must purchase the Certified Administrator Study Guide.  Please Login or purchase the study guide.

38 Responses to “Data Management: Scenario 1 Solution”

  1. kadersh November 24, 2016 at 2:13 pm #

    I have done it this way
    AND (ISPICKVAL( StageName , “Closed Won”), TODAY() – CloseDate <= 30)

    • JohnCoppedge November 28, 2016 at 7:18 pm #

      Yep that should work

      • swati.c.840 November 30, 2016 at 8:02 am #

        Im confused, for the validation rule to fire, shouldn’t TODAY() – CloseDate be greater than 30 days?

        • JohnCoppedge November 30, 2016 at 3:40 pm #

          That would prevent the record from being saved if the close date was more than 30 days in the future… validation rule fires when the rule criteria evaluates to true

  2. RomanPer July 30, 2016 at 12:00 am #

    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)

    • JohnCoppedge November 28, 2016 at 7:16 pm #

      Priorvalue(field) <> field should have the same result as ISCHANGED

      Just a slightly longer formula

  3. jkashi April 5, 2016 at 7:26 pm #

    Hi John,
    In the Opportunity object, I don’t see the field “IsWon”
    Please advise.

    • JohnCoppedge April 10, 2016 at 10:22 pm #

      IsWon is published in the list of fields on the setup page for opportunities for some reason- but I promise, its there. You can see it when building a formula or through the API.

  4. jkashi April 5, 2016 at 6:19 pm #

    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.


    • JohnCoppedge April 10, 2016 at 10:25 pm #

      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”.

  5. dgallegos1975 March 5, 2016 at 10:24 pm #

    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.

    • JohnCoppedge March 7, 2016 at 3:26 am #

      Can’t be more than 30 days in past. That might be a more direct error message 🙂

      • dgallegos1975 March 7, 2016 at 2:38 pm #

        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.

        • dgallegos1975 March 7, 2016 at 3:02 pm #

          I guess for reporting purposes?

          • JohnCoppedge March 7, 2016 at 5:46 pm #

            Exactly – this would be to prevent a user from backdating a closed/won opportunity.

  6. October 20, 2015 at 9:57 pm #

    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.

    • JohnCoppedge October 22, 2015 at 2:58 am #

      The field ‘isWon’ is there although not listed in the field list. The label is “Won” when you are selecting the field in the validation rule.

  7. Stewart Dickison June 10, 2015 at 7:26 am #

    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.

    • JohnCoppedge June 10, 2015 at 11:21 pm #

      That’s a good point Stuart – you could definitely modify the rule to add that logic.

  8. Maura McNulty April 15, 2015 at 12:53 am #

    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!

    • JohnCoppedge April 15, 2015 at 2:14 am #

      You should be familiar with basic syntax. I am not sure if there will be any questions, but it wouldn’t surprise me if there were.

  9. moshtagh Foroohar Pak December 3, 2014 at 8:17 pm #

    Hello John,
    The formula causes syntax error:
    ISCHANGED( StageName ),
    IsWon == TRUE,
    CloseDate < (TODAY() – 30))

    • JohnCoppedge December 5, 2014 at 3:48 am #

      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.

  10. Paul Temple November 11, 2014 at 4:16 am #

    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.


  11. Sreekanth G September 30, 2014 at 11:51 am #

    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))


  12. Alhaji Kamara August 11, 2014 at 12:37 am #

    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.

    • JohnCoppedge August 11, 2014 at 1:13 am #

      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).



  13. Sreedurga Prabhala March 1, 2014 at 10:58 pm #

    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)) ?


    • JohnCoppedge March 8, 2014 at 6:52 pm #

      Close date is a required field (in all orgs) and can’t be blank – otherwise good catch!

  14. Sue Monk February 3, 2014 at 7:01 am #

    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?

    • JohnCoppedge February 10, 2014 at 4:23 am #

      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):

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

      • Jeanne Busch October 23, 2014 at 10:58 pm #

        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?

        • Jeanne Busch October 23, 2014 at 11:02 pm #

          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!

      • March 30, 2016 at 11:37 pm #

        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