+ Start a Discussion
Cynthia DouglassCynthia Douglass 

FORMULA HELP: How do I create a subtotal of a number (# of documents) times price (multiple values in a picklist)?

I want to subtotal the number of documents on an invoice with the price per document, with the price being variable and in a picklist.  My simple mind thinks this simple formula would work:

Number_of_Documents__c * Document_Price__c

However, it seems that Salesforce can't compute formula this when one of the items is in picklist form.

First, why can Salesforce not compute such a simple formula when one of the values is from a picklist?

Second, what do I need to add to make this work?

The Number_of_Documents__c custom field is a number field.

The Document_Price__c is a picklist of numbers: 35, 25, 15, etc.

Finally, how do I express the Subtotal as a value in US dollars?
Best Answer chosen by Cynthia Douglass
Greg RohmanGreg Rohman
No problem.

As I mentioned, picklists are a strange animal in Salesforce. When refering to a picklist field, you cannot simply refer to the field name, as you did in your original formula.

In your scenario we want to obtain what was selected in the picklist, so we first have to convert the selected value to a literal text string using the TEXT function. But since we need an actual numeric and not a textual representation of a number for the math calculation, we then nest that inside the VALUE function to perform that conversion to a number.

Another commonly used function with picklists is the ISPICKVAL function (see https://help.salesforce.com/apex/HTViewHelpDoc?id=customize_functions.htm). This function would be used to compare a value against a known picklist value(s).

Hope that helps a bit.

-Greg
 

All Answers

Greg RohmanGreg Rohman
Hi Cynthia.

Picklists in Salesforce are strange and require some additional coding to extract the selected value. Try this:
 
Number_of_Documents__c * VALUE(TEXT(Document_Price__c))

When creating your formula field, there is an option for the formula to return currency.

-Greg
Cynthia DouglassCynthia Douglass
Thanks, Greg!

Since I'm new to Salesforce and to formulas, can you help me understand what the above formula means, so I can recreate something like it in the future if I need to?

Many thanks,

Cynthia
Greg RohmanGreg Rohman
No problem.

As I mentioned, picklists are a strange animal in Salesforce. When refering to a picklist field, you cannot simply refer to the field name, as you did in your original formula.

In your scenario we want to obtain what was selected in the picklist, so we first have to convert the selected value to a literal text string using the TEXT function. But since we need an actual numeric and not a textual representation of a number for the math calculation, we then nest that inside the VALUE function to perform that conversion to a number.

Another commonly used function with picklists is the ISPICKVAL function (see https://help.salesforce.com/apex/HTViewHelpDoc?id=customize_functions.htm). This function would be used to compare a value against a known picklist value(s).

Hope that helps a bit.

-Greg
 
This was selected as the best answer
Cynthia DouglassCynthia Douglass
Yes, that's a great help, thank you!