Loading

Convert text picklist to numbers for calculations in Formulas

Publiseringsdato: Apr 24, 2023
Beskrivelse
Calculating the Expected Total Contract Value is an essential task for any business that relies on contracts. It helps organizations project their revenue streams, which is important for budgeting, planning, and forecasting.

In this article, we will explore how to calculate the Expected Total Contract Value based on the Contract Term (picklist) and Estimated Annual Revenue fields (currency), using a formula.
Løsning

To calculate the Expected Total Contract Value we need to perform a mathematical calculation of the Contract Term multiplied by the Estimated Annual Revenue. The Contract Term (picklist) field has values of One Year, Two Years, or Three Years.

Example: 

Expected Total Contract Value = Contract Term * Estimated Annual Revenue

Expected Total Contract Value = Two Years * $300,000

Desired outcome: Expected Total Contract Value = $600,000


When working with picklist fields, it is important to keep in mind that they are text fields and cannot be used in mathematical operations without first converting them into numerical values. This is where the CASE function comes in handy. 


Step 1. From any page in Lightning, click on the Gear icon in the top right corner
Step 2. Select Setup and navigate to the Object Manager tab
Step 3. To select the object that you will be working on, simply scroll down from the list of available objects until you find it. Once you see it, click on it to select it, in this example, we will choose Contract
Step 4. From the sidebar, click Fields & Relationships
Step 5. Click New to create a custom field
Step 6. Next, choose Formula as your field Data Type and click on the Next button
Step 7. In Field Label, type Expected Total Contract Value. The Field Name populates automatically
Step 8. Select the type of data you expect your formula to return. For this example, we want to write a formula that calculates the Expected Total Contract Value based on Contract Term (picklist field) and Estimated Annual Revenue (currency field), you select Currency
Step 9. Click the Next button, then enter the Formula below (See Sample Formula) into the Formula Editor
Step 10. Click the Check Syntax button to ensure you have no errors 
Step 11. Optionally, enter a description of the formula in the Description box and Help Text box
Step 12. In the Blank Field Handling section, select Treat blank fields as blanks. Click on the Next button
Step 13. Set the Field-level security to determine whether the field should be visible for specific profiles, and click Next
Step 14. Choose the page layouts that should display the field 
Step 15. Click Save to finish

Sample Formula: 
/* Convert Picklist value to a number */
CASE(Contract_Term__c,
    "One Year",1,
    "Two Years",2,
    "Three Years",3,
    0)
/* then multiply by the Estimated Annual Revenue */
* Estimated_Annual_Revenue__c

Note: Consider adding comments to your formula, especially if it is complicated. Comments must begin with a forward slash followed by an asterisk (/*), and conclude with an asterisk followed by a forward slash (*/). Comments are useful for explaining specific parts of a formula to anyone viewing the formula definition.

Now that the picklist values have been converted into numbers, the calculation looks as follows:

Expected Total Contract Value = Contract Term * Estimated Annual Revenue
Expected Total Contract Value = Two Years * $300,000
Expected Total Contract Value = 2 * $300,000
Expected Total Contract Value = $600,000

Contract Details.PNG

You're done!
_________________________________________

Written by: Steven Trumble | Salesforce Answers Leader
Steven Trumble is a Salesforce Consultant at Blue Engine Solutions in Washington D.C. Steven was first introduced to Salesforce in June 2021 and hasn’t looked back since. Steven is actively involved in the Trailblazer Answers and Salesforce Military (Veteran’s Force) communities, demonstrating his commitment to sharing his knowledge and supporting his peers. 

Submissions reflect only the opinions of the user who made available the Submission and not the opinions of Salesforce, regardless of whether the user is affiliated with Salesforce, and may contain or constitute products, services, information, data, content, and other materials made available by or on behalf of third parties ("Third Party Materials). Salesforce neither controls nor endorses, nor is Salesforce responsible for, any Third  Party Materials, including their accuracy, validity, timeliness, completeness, reliability, integrity, quality, legality,  usefulness or safety, or any applicable intellectual property rights. Any Submissions made available through any message board or forum in response to posted questions, or that otherwise purport to answer any questions, including any questions about Salesforce or Programs, are made available for your general knowledge only and should never be relied upon as answers to your specific questions (even if an answer is marked as a “best” answer or with any similar qualifications). You should always contact Salesforce support for answers to your specific questions. Salesforce has no control over Submissions and is not responsible for any use or misuse (including any distribution) by any third party of Submissions.

If you have questions, tap into the wisdom of our entire Trailblazer Community here: https://trailhead.salesforce.com/trailblazer-community/feed
 
Knowledge-artikkelnummer

000395190

 
Laster
Salesforce Help | Article