Salesforce Formula Fields & Picklist Fields/Values
It’s always “refreshing” to find something so innocuous in Salesforce that kind of throws you for a loop. Especially when this “innocuous” item is a little counter-intuitive to the Salesforce mantra of “clicks not code”. Plus, it appears to have impacted people for quite a while. Warning: This post may be (i.e., is) a little pic heavy.
Anyways, I’ve been working with a client on their Salesforce roll-out for a few months. It’s not a big roll-out but one that’s big enough to necessitate some custom integration. Within their org they have a picklist field on Leads, Account, and Opportunities to easily identify and report by corporate division for ownership purposes. The Label Value of the picklist values is a text string with a company number identifier and name concatenated together, e.g. ‘## TEXT OF NAME’. The integration team only wants to use the company number value (‘##’) for incoming and outgoing communication with external systems.
So, to accommodate this we updated the API Values for the picklist values to just be the numerical values at the start of the Label Value (e.g., Label Value ’01 US Division’ and API Value ’01’).
However, this is where a loop is thrown…
This company identifier picklist is an Account field but when we attempt to reference that value on, say, an Opportunity via a formula field (e.g., TEXT(Account.Company_Number__c). Unfortunately, the value returned is NOT the Label Value but is the API Value. That is, I expected to get back ’01 US Division’ but only received ’01’.
I can kind-of, sort-of understand why this would be the case. The API Value is used in Apex and all the front-end configuration is essentially creating Apex without really coding. BUT, from a practical standpoint it doesn’t make sense since I’m referencing a field from the front-end in a formula field and expecting that same front-end display value back but, instead, am given the back-end API Value. Boo, Salesforce. Boo!
There are a few options:
1) Custom Apex – No thanks, Salesforce. “Clicks, not code”, right?!?
2) Update Formula Field with IF Statement and Hard-Code of UI Values – I opted to go this route. I very much dislike it since we’re hard-coding a map of values from the returned picklist API Values to recreated Label Values. In some cases this could be problematic as picklist values have the potential to change causing issues with the formula field. Thankfully, this customer’s values have been very stable and unlikely to change. Still less than ideal in this case and, potentially, a pain in the butt for other clients.
Here’s the updated formula for the field with hard-coded values (YUCK!):
And, here’s the final result on the Opportunity:
In the Success Community We Trust…
The one thing I do love about the Salesforce community is that nearly all issues you run across have been found and reported to Salesforce previously (sometimes years ago). And, this issue is no different. This specific Idea is “under review”. So, there’s still hope!