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 Salesforce.com Certified Administrator Study Guide. Please Login or purchase the study guide.
I have done it this way
AND (ISPICKVAL( StageName , “Closed Won”), TODAY() – CloseDate <= 30)
Yep that should work
Im confused, for the validation rule to fire, shouldn’t TODAY() – CloseDate be greater than 30 days?
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
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)
Priorvalue(field) <> field should have the same result as ISCHANGED
Just a slightly longer formula
Hi John,
In the Opportunity object, I don’t see the field “IsWon”
Please advise.
Thanks
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.
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
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”.
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.
Can’t be more than 30 days in past. That might be a more direct error message 🙂
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.
I guess for reporting purposes?
Exactly – this would be to prevent a user from backdating a closed/won opportunity.
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.
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.
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.
That’s a good point Stuart – you could definitely modify the rule to add that logic.
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!
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.
Hello John,
The formula causes syntax error:
AND(
ISCHANGED( StageName ),
IsWon == TRUE,
CloseDate < (TODAY() – 30))
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.
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
Interesting didn’t know that was the case. Good to know!
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
You have no operator between close date and today (e.g. > < ==)
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.
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
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
You would see it in the list of fields for reporting or via the API – not sure how else you would know that it was there; it appears to be undocumented. https://help.salesforce.com/HTViewHelpDoc?id=opp_fields.htm&language=en_US
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,
Close date is a required field (in all orgs) and can’t be blank – otherwise good catch!
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
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)
)
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?
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!
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.