+ Start a Discussion
Jonathan PenaJonathan Pena 

Display field value based on opportunity stage

I'm trying to create a formula field which wwill dsipalay an assigned value based on the opportunity stage: 

For Example: 
Lead/Idea Generation = 02-Noticed
Needs Analysis = 04-Validated
Request for Plan/Proposal = 05-Qualified
Working likely = 05-Qualified
Verbal  = 05-Qualified
I/O In House - Order In = 06-Conditional
Contract Complete  = 07-Won/Implementing
Lead Disqualified  = Lost
Lost  = Lost

Here is the formula field i started:

IF(ISPICKVAL(StageName ,"Lead/Idea Generation"), 02-Noticed),
  If(ISPICKVAL(StageName ,"Needs Analysis"), 04-Validated),
     If(ISPICKVAL(StageName ,"04-Validated"), 100, 0.1),
        If(ISPICKVAL(StageName ,"Request for Plan/Proposal","Working likely","Verbal" ), 05-Qualified),
           If(ISPICKVAL(StageName ,"I/O In House - Order In"), 06-Conditional),
              If(ISPICKVAL(StageName ,"Contract Complete"), 07-Won/Implementing),
                 If(ISPICKVAL(StageName ,"Lead Disqualified", "Lost"), Lost
)
)
)
)
)
)
)
Best Answer chosen by Jonathan Pena
Parker EdelmannParker Edelmann
You may use a code similar to this. It uses CASE() instead of nested IF() statements like you were trying.
CASE(StageName, "Lead/Idea Generation", "02-Noticed",
                "Needs Analysis", "04-Validated",
                "Request for Plan/Proposal", "05-Qualified",
                "Working likely", "05-Qualified",
                "Verbal", "05-Qualified",
                "I/O In House - Order In", "06-Conditional",
                "Contract Complete", "07-Won/Implementing",
                "Lead Disqualified", "Lost"
                "Lost", "Lost",
                null)
With this format, you only reference the Stage once, making this an effecient formula. Because we must use quotes, the stagename must match perfectly with what is inside the quotes in order to work correctly. As a side note, CASE() is one of the relatively few functions you can use with picklist fields. Let me know if this helps or you have any questions.

Thanks,
Parker

All Answers

Pramodh KumarPramodh Kumar
Jonathan Pena,

i wrote formula on opportunity leadsource field.

which is
case(field, condition, result, condition, result, condition, result, else);
 

CASE(text( LeadSource ) , 'Web', 'WW', 'Phone Inquiry', 'PI','Partner Referral','PR','Purchased List','PL','Other','O','0')
Let me know if you have any difficulties

Thanks,
pRAMODH.
 
Parker EdelmannParker Edelmann
You may use a code similar to this. It uses CASE() instead of nested IF() statements like you were trying.
CASE(StageName, "Lead/Idea Generation", "02-Noticed",
                "Needs Analysis", "04-Validated",
                "Request for Plan/Proposal", "05-Qualified",
                "Working likely", "05-Qualified",
                "Verbal", "05-Qualified",
                "I/O In House - Order In", "06-Conditional",
                "Contract Complete", "07-Won/Implementing",
                "Lead Disqualified", "Lost"
                "Lost", "Lost",
                null)
With this format, you only reference the Stage once, making this an effecient formula. Because we must use quotes, the stagename must match perfectly with what is inside the quotes in order to work correctly. As a side note, CASE() is one of the relatively few functions you can use with picklist fields. Let me know if this helps or you have any questions.

Thanks,
Parker
This was selected as the best answer
Laxman1975Laxman1975
Jonathan,

I assume that data type for this formula field is Text so return value should be added in single quotes or double quotes. Also if you are checking stage with multiple picklist values seperate those if conditions like 

If(ISPICKVAL(StageName ,"Request for Plan/Proposal"), "05-Qualified"),

Example :

Commission Amounts
IF(ISPICKVAL(StageName, "Closed Won"), ROUND(Amount *0.02, 2), 0)

Hope this helps

Regards
Laxman
Jonathan PenaJonathan Pena
All, 

Thank you so much for your help on this. Very helpful information which i can use for other business cases as well. 

Once again, thank you for your input. 

Best, 
Jonathan