Assume, I've got a standard picklist field Rating on Account with values Hot, Cold. Using enabled Translation Workbench I've translated these values into German. When the language of a user is set to English, and the user performs a SOQL query

SELECT Id, Rating FROM Account WHERE Rating LIKE 'Cold'

it returns N records. But if we change the language of the user to German, the same SOQL query returns 0 records. However, when it changes to

SELECT Id, Rating FROM Account WHERE Rating LIKE 'Kalt'

it returns N records as well as for the very first scenario. At the same time, the returned values of the Rating field remain untranslated ('Cold'). This means that the LIKE operator implicitly translates the values of picklists before comparison. I've tested IN, NOT IN, = SOQL Comparison Operators and they don't behave like LIKE, they perform a comparison based on picklist value API Name not Label. There is no such information about it in Salesforce Docs. So finally my question is:

Why does the LIKE SOQL operator implicitly translate picklist values before comparison and how to perform a comparison based on picklist value API Names using the LIKE operator?


This is what I think is the reason for the LIKE operator to automatically translate picklist values: The LIKE operator is used to match strings. The picklist value is translated, probably because of situations when the end-user searches for text in his own language.

Consider the following situation: a custom search component for a multinational company that supports many languages. The requirement is to enable search in picklist values that are being added/deactivated every month or so. If the user wants to search for a string, he won't type the API name of the field, but the translated version of the value he wants to get.

cheese translation picklists

| improve this answer | |
  • Great explanation. But if this behavior is expected it should be mentioned in Docs. – Oleh Berehovskyi Nov 18 '19 at 10:03
  • 1
    The part where I said the LIKE is used to match strings is written on the documentation. The rest is conjecture. – Renato Oliveira Nov 18 '19 at 10:05

I don't have a why for you, but if you have a scenario where LIKE needs to match the API name, you could create a formula field targeting the same picklist. FFs always return API name not label.

| improve this answer | |

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.