Stat Tracker

Sunday, January 19, 2014

Using Formula Fields to Translate Picklist Values into System Codes

When implementing multiple integrations with a Salesforce.com org it is a common use case to translate picklist values into system code values for legacy systems. For example, if you have a Quote object you may have a picklist called "Quote Payment Type" with valid values of "Cash", "Credit", or "Debit". These are the values that are displayed in Salesforce.com UI and the values persisted in the Quote_Payment_Type__c field on your Quote object.

However, sometimes we need to translate the picklist values into system codes for an integration. If we are integrating our Quote object with a payment system for example via a web service the legacy system may expect a system code instead of the string values displayed. In this case let's say our legacy system is a AS400 mainframe and expects short bytes for the values (Cash == C, Credit == CR, Debit == D). How should we do this?

We could do this in Apex Code and translate these values prior to making a HTTP Callout. However, that assumes that we will only integrate with this system via a HTTP Callout in Apex code. We wouldn't be translating this for Data Loader integrations, or other 3rd party tool integrations that may simply pull the SObject and pass it to the legacy system. Also if we build this translation in the Apex code layer, everytime we add a new translation or change an existing translation we would need to do a code deployment and update our unit test coverage. Ouch!

A much cleaner way to implement this is to use a Formula Field on the object.

In this case we create a new Formula Field called "Quote Type INT".

Formula Field Definition:

IF(ISPICKVAL(Contract_Type__c, 'Cash') , 'C', 
IF(ISPICKVAL(Contract_Type__c, 'Finance'), 'L', 
IF(ISPICKVAL(Contract_Type__c, 'Lease'), 'LE','NA')))

And now the translation is always done in the declaritive layer which is SFDC best practice. Here is what we get in our UI if you chose to display the system code value:



Its a simple pattern that every developer should have in their toolbox. Always remember to use "Clicks not Code" whenever possible to solve your development or integration needs. With this pattern if we add new translations I can simply update the Formula Field and Picklist values without doing a full code deployment!

One thing to consider: If you have hundreds of values you may exceed the Formula Field size. In that case you may need to use a Custom Setting or other mechanism (Static Resources, etc) to hold the translation values. But please don't hard code them!