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
Heng WangHeng Wang 

Convert text field to number based on if text field is empty

I want to create a report or a query to accomplish this:
Let's say we have field called color. If the color is green, it is assigned as 3 points. Yellow is 2 points. Red is 1 point. Now I want to get a list of contacts with their names and the points based on the color they picked. Something looks like:
Name     Points
xxx          2
xxx          3

How to achieve this?
Thanks advance for any help!
Malhar Patel PatelMalhar Patel Patel
you can go multiple ways for doing this.  Obviously you need Color field with picklist values for each color, then use IF or Case.  

I recommend Case.
Example, CASE(Color, "Green", 3, "Yellow", 2, "Red", 1, "Other", 0, 0)
Heng WangHeng Wang
Thanks for your reply. I am new to Salesforce. I can't use IF or Case in SOQL query, right? Are you suggesting programming in APEX?
Malhar Patel PatelMalhar Patel Patel
Heng, you have a long way to go.  If you want to create report meaning see the actual results in Reports format within Salesforce then you'd need to add picklist field on your contacts object for color.  Then, you'd create report for contacts and add the formula for points during report creation(yes you can do that in salesforce).  Good Luck.