function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
KushKush 

Need help with Lead Scoring, formula errors

Hi all,

I am new to SF, especially to formulas.  I created a custom field "Lead Score" in the leads tab and would like to score my leads based on 8-10 criterias including leadsource, customer budget, timeline etc...

I researched online and learned to make one formula work:

if ( ISPICKVAL( LeadSource , "Web"), 4,
if ( ISPICKVAL( LeadSource , "Trade Show"), 6,
if ( ISPICKVAL( LeadSource , "Phone Inquiry"), 8,
if( ISPICKVAL( LeadSource , "Partner"), 10,
if( ISPICKVAL( LeadSource , "Advertisement"), 4,
if( ISPICKVAL( LeadSource , "Employee Referral"), 7,
if( ISPICKVAL( LeadSource , "External Referral"), 6,
if( ISPICKVAL( LeadSource , "Unclassified"), 0, 0))))))))

but now when I try to add a second formula (below) to the same formula field or a seperate one it gives me syntax errors " )".  How can I score my leads based on 8+ criterias that are listed on my Leads field? Any help is greatly appreciated.

if ( ISPICKVAL( {!Budget} , "None"), 0,
if ( ISPICKVAL( {!Budget} , "$0-$999"), 2,
if ( ISPICKVAL( {!Budget} , "$1,000 - $ 2,000"), 4,
if ( ISPICKVAL( {!Budget} , "$2,000 - $4,000"), 5,
if ( ISPICKVAL( {!Budget} , "$5,000 - $ 10,000"),6,
if ( ISPICKVAL( {!Budget} , "$15,000 - $25,000"), 7,
if ( ISPICKVAL( {!Budget} , "$30,000 - $ 45,000"),9,
if ( ISPICKVAL( {!Budget} , "$50,000 - $80,000"), 10,
if( ISPICKVAL( {!Budget} , "$100,000+"), 12, 0))))))))

Best Regards,


Kush

Please note that my ) signs appear to be smilies :(


Message Edited by Kush on 06-09-2008 03:12 PM
Lori_Lori_
You're missing an ending parenthesis on the second statement.  You have eight but you need nine.
KushKush
Thank you that seemed to solve the problem and now I get a message saying that feild "budget" does not exist, even though it does in my leads tab.

Here is the formula

if ( ISPICKVAL( LeadSource , "Web"), 4,
if ( ISPICKVAL( LeadSource , "Trade Show"), 6,
if ( ISPICKVAL( LeadSource , "Phone Inquiry"), 8,
if( ISPICKVAL( LeadSource , "Partner"), 10,
if( ISPICKVAL( LeadSource , "Advertisement"), 4,
if( ISPICKVAL( LeadSource , "Employee Referral"), 7,
if( ISPICKVAL( LeadSource , "External Referral"), 6,
if( ISPICKVAL( LeadSource , "Unclassified"), 0,
if ( ISPICKVAL( Budget , "None"), 0,
if ( ISPICKVAL( Budget , "$0-$999"), 2,
if ( ISPICKVAL( Budget , "$1,000 - $ 2,000"), 4,
if ( ISPICKVAL( Budget , "$2,000 - $4,000"), 5,
if ( ISPICKVAL( Budget , "$5,000 - $ 10,000"),6,
if ( ISPICKVAL( Budget , "$15,000 - $25,000"), 7,
if ( ISPICKVAL( Budget , "$30,000 - $ 45,000"),9,
if ( ISPICKVAL( Budget , "$50,000 - $80,000"), 10,
if( ISPICKVAL( Budget , "$100,000+", 12, 0))))))))))))))))))


Can I enter all of my criteria in one formula?


Message Edited by Kush on 06-10-2008 06:44 AM
TCAdminTCAdmin
Hello Kush,

If it is a custom field it will be ended in a __c value. You should be able to get the actual api name by choosing it in the list at the top of the page.
KushKush
Thank you for your help Chris,  when I try to choose a field from the list at the top of the page only 2 out of 12 of the criterias are listed (Annual Revenue, No. of Employees), how can I fix that issue?

When you wrote the custom filed will be ended in a C___ value, did you mean that everytime I add a line like budget I have to type it as budget__c?

I tried searching more on this subject but with no luck, anyone has a sample formula related to this? 

Thank you.


Kush
TCAdminTCAdmin
Kush,

That is correct. If you look at the top of the editor though you will see the option of Simple Formula or Advance Formula tabs. The Advanced Formula will give you all of the possible fields that can be used in the formula. The simple will only show the numeric fields I believe. And yes, everywhere you use Budget you will have to use the value of Budget__c.
KushKush
Thanks again Chris you are a great help, everything worked just as you menionted.  I wish I could give your reputation points :smileyhappy:

Here is a new formula that is the same as ispickval but saves a whole lot of space....

CASE (LeadSource , "Web", 4, "Trade Show", 6, "Phone Inquiry", 8, "Partner", 10, "Advertisement", 4, "Employee Referral", 7, "External Referral", 6,
CASE (Interest_Level__c , "Low", 4, "Average", 6, "Above Average", 8, "High", 10, "Very High", 12,
CASE (Brand_Centric__c , "Not at all", 4, "Somewhat", 6, "Average", 8, "Above Average", 10, "Very Brand Conscious", 12, 0)))

My problem now is that even though there are no errors in the formula, the lead score field doesn't recognize any other case values but the first one (LeadSource)

1.  Can I have more than 2 CASEs in one formula? In other words can I have up to 10 fields addressed in one formula page? Like I treid with 3 up there.
2.  How can I make all the scores add together? The value of Leadsource 6 + the value of Brand_Centric 4 = My lead score of 10?

BTW LeadSource field is a given field in Salesforce,  but the last two are custom made by me... Could that be a problem?

Thanks again.


Kush

P.S.
Once I'm done with this and make it work I will make sure to write a HOW-TO guide for users like me, so they don't have to struggle.




Message Edited by Kush on 06-10-2008 01:33 PM

Message Edited by Kush on 06-10-2008 01:45 PM
KushKush

 In case someone wonders in the future, here is a response I got from Chris and it works great.

"You will need to close each CASE() statement as if they are individual. You will then place a + between them to add the numeric values together."

 

CASE() +

CASE() +

CASE()