Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
thusker

# More formula help -- Validation rule based on Amount and Stage

Yes . . . it's me again with another formula question.

Can anyone point me in the right direction to create a formula for a validation rule that would require a field to be filled out based on Opportunity Amount and Stage?  Example:  Our managers want to require "Win/Loss Reason" picklist field to be filled out if an opportunity is Closed and has an Amount of \$100k or more.  How would I create a formula that can look at the value of the Stage picklist and also the Amount?

AMartin

Hi

And(
IsClosed = True,
Amount >= 100,000,
IsPickVal(Loss Reason,"")
)

Aiden
thusker

Thanks, Aiden . . . I'll try it!

Someday I won't be so lame with formulas, hopefully.  Hard stuff for an English major . . . it's like a totally different language.

rockchick322004
Do you find the sample formulas to be useful?  https://na1.salesforce.com/help/doc/en/salesforce_useful_formula_fields.pdf
For me, I like to be able to copy and paste a working sample and then tweak it to meet my use case.
AMartin
You're talking to a Poli Sci major so I know how you feel.

I was just told in another post that using "IsClosed" or "IsWon" is not a good idea for validation rules. So we need to reference the actual Stage field.  Give this a try.

And(
Or (IsPickVal(StageName,"Closed-Sold"),
IsPickVal(StageName,"Closed-NoSale")
),
Amount >= 100000,
IsPickVal(Loss_Reason__c,"")
)

Aiden
thusker
Yes, I try checking out the sample formulas and have had a little success with some simpler ones.  Seems like I just struggle a ton with anything where I am trying to do more than one thing at a time in the formulas.  I'll check out that pdf file . . . THANKS!!!!!
thusker

Thanks, Aiden.  I'll try that as well.  I gave your first one a shot and it does seem to work.  I was hoping to be able to use the validation rule to require a multi-select list to be populated, but it looks like I cannot choose that as a field to have the error warning above (and can't choose that field for use inside the validation rule formula either).  So I set up a test in a developer org using just a regular picklist and it works.  Will try your second suggestion right now.  It would be better if I could get the multiselect list to work, but if need be I can switch to a picklist or two to get what we really need.

Thanks again for the help!

AMartin
Formulas and validation rules don't work on multi-select listboxes.

Aiden
thusker
That's what I was afraid of . . . but what I think I'll do is change to 2 separate picklists.  One for "Primary Win/Loss Reason" and one for "Secondary Reason".   That way I can force getting the first one at the very least.  People seldom have more than 2 anyway and anything beyond the biggest and second biggest reason for winning/losing probably isn't wildly relevant.

So this was still amazingly helpful!!!!!!!!!!!!!!!!!!!!!!!!
jisaac
I set up a Dependent Dropdown for Lost or Dropped reasons that is a required field, but it only has items selected for the Lost or Dropped stages.

That way, when one of our reps marks a deal as Closed - Dropped, the field shows up as required and they are presented with the choices for why it was dropped. Same thing for when they mark a deal as Closed Lost - just different reasons. For all other stages, the field is greyed out.

Works like a charm!

Jane