Can I query the translated value and return the key in translation workbench?

Question

Here is a breakdown of the problem I am trying to solve.

In a web to lead form we are updating a custom picklist field on the record based on inputs from the form. The value is coming back in the local language not the master value to then be translated through the work bench. i.e. this value now appears as an inactive option on the picklist.

I would like to create a trigger to update this value to the correct master value of the picklist option in the translation workbench.

Example: Picklist__c has options of Yes; No. It is updated through web to lead from a spanish form and the value that is on the record is now "Si".

This value is in the translation file for this specific picklist.

I would like to query the translation file pair and return the master language key which is english. I'm assuming the translation files are maps. Map<String,List<String>>.

So in my example the map would be [Yes, (Si, Qui)] and I could query Translation.values and return the key.

Is this even possible?

Answer 1

There is currently no supported Custom Labels / Translations API from Apex, the Web Services API, or SOQL that you could use to achieve what you're after (the Map<String,List<String>> with the master language value as the key). Click here to get this on the Product Management top priorities list!!! (NOTE: there IS a very much insane way that you might be able to achieve this through Apex by doing a PageReference.getContent() and using a Visualforce Page as a utility to return the desired translated values, but I do not recommend this route at all).

However, I think that you can avoid the need through this by addressing the root cause of your problem: your Web-to-Lead form sending back values in the local language of the user. If you can get the Web-to-Lead form to always send back values to your database in a unified master language, while presenting these options to the user visually in their local language, then your problem is solved.

So, the goal is for your Web-to-Lead form's generated <select> tag corresponding to the Salesforce Picklist field in question to have child <option> nodes whose value attributes are in the Master Language, but whose content / text is in the local language.

Example:

You have a Picklist Field "Do you need a Home Mortgage?" in Salesforce with entries "Yes","No","Very Much". You have translated these into, say, Spanish, with values "Sí","No","Muchíssimo". Now, if your Web-to-Lead form looks like this:

<select id="do-you-need-a-home-mortgage">
    <option value="Yes">Sí</option>
    <option value="No">No</option>
    <option value="Very Much">Muchíssimo</option>
</select>

then you're golden --- your database will be populated appropriately.

So, I'm assuming you're currently populating the text/visible portion of these fields appropriately, so I'm assuming you have access to both the PicklistEntry values and public-facing, translated labels. BUT, if you don't, you can get these from the Web Services API or from within Apex through the DescribeFieldResult for this Picklist field, and then doing a getPicklistValues() call.