Performing calculations on Picklist values within Formula Fields

A little knowledge of the Operations and Functions available to formula fields in the Force.com platform goes a long way. In the case of simple object I created to assist in judging ASU Force.com business apps, the outcome was a helpful little formula which may just help you out of a bind.

Last week I had the pleasure of visiting Arizona State University. The reason was to visit an innovative class where students were tasked with developing a business application built on the Force.com platform. My role, and that of my colleague, Indrajit Sen was to judge the outcome of this capstone assignment. Final judging has been completed and I will blog the results soon, but I wanted to share a little tip which saved me from some manual labor when calculating final scores.

To assist in the scoring, I put together a really quick object (after all, if the students had to build a business application on the Force.com platform , the least I could do was use the platform to collect scores and feedback) to capture judging scores. At a high level, the judging was based on three criteria: Technical Innovation, Business Presentation, and overall Creativity and Visual Appeal of the application. For my Scoring object, I implemented these fields as picklists with a value of 1-10.

Unfortunately, my choice of field types caused an issue I did not think about until after I had entered all the judging results. My initial goal, aside from gathering feedback, and judging scores, was to sum the values of the three criteria. The highest total score would identify which of the applications I thought was the winner. The problem I faced was when creating a formula field—Overall Score—was that I could not use any of the math functions on picklist values. Thankfully, with a little bit of formula magic I came up with a solution.

Firstly, I converted each picklist value to text using the TEXT() function. Then I wrapped each TEXT() function with a VALUE() function to convert text to a number. After that, I could easily add the individual values together using a simple addition (+) operator:


Lesson learned from this experience is: 1.) take a little more time upfront to consider your object model 2.) reality is that  1. is not always possible, but the Force.com platform gives you creative ways of handling this.  

I make a habit of keeping bookmarks to important sections of the Salesforce.com Users Guide. One of these bookmarks links directly to the Operations & Functions section; And I am glad it did.

Leave your comments...

Performing calculations on Picklist values within Formula Fields