I am trying to create a field that translates the values stored in another field. However, I run into a problem if the source holds different values that translate to the same destination value. For example, source field "Name__c" has two records, one record has a value of "Ireland" another record has a value of "USA" the destination field should be able to put "English" in each record. However, do I have to write my formula as CASE(Language__c ,​​ "Ireland", "English", "USA", "English","Other" rather than

CASE(Language__c ,​​ ("Ireland", "USA"), "English","Other"

or something similar?

  • 1
    Dang, if only we could use REGEX in a formula field! – Ray Dehler May 30 '14 at 18:16

CASE does not support multiple values, REGEX, CONTAINS, etc. If the number of cases is small, you might use nested if-contains.

If(contains("Ireland;USA", language__c), "English", if(..., "Spanish", if(..., "Japanese", "Other")))

You'll run into limits around thirty languages or so (if I recall correctly). Case is usually more efficient than this design by a wide margin, even using the verbose syntax.

If that's not enough for you, you may want to create the field as a text field, then update the value with a workflow field update, which can accept much larger formulas.

As a last resort, you can use Apex Code to populate the values as part of a record create or update.


You could do something like this (hybrid approach) - assuming name__c contains the country name

Note that running into formula size limits is likely if you are mapping all countries/languages

      CASE(Name__c,   /* All the 1;1 mappings of country to language */

I don't know what you're use case is, but this sounds like something that is best solved with picklists which can be translated. The returned from the database will be the translation matching the current user's language, otherwise the default language for the org.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.