You need to sign in to do that

Don't have an account?

Cynthia 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

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?

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?

Greg RohmanNo 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 RohmanHi Cynthia.

Picklists in Salesforce are strange and require some additional coding to extract the selected value. Try this:

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

-Greg

Cynthia DouglassThanks, 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 RohmanNo 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

Cynthia DouglassYes, that's a great help, thank you!