SOQL Address Country not localized / translated

addresscountry-and-statesoqltranslation

I'm querying an Account Address and want the country to be translated into the language of the requesting user.

Sample query:

SELECT BillingAddress FROM Account

I maintained the translation of the country in the translation workbench and already tried to use "toLabel(BillingAddress)" without success.

When referencing the BillingCountry directly I get the same result. Prefixing with "toLabel" results in an error:

MALFORMED_QUERY: only picklist fields, field history, or platform entity fields (or Record Type Name, Description in the SELECT list) support translation

Within the UI the Label is translated:

Other values in the query are getting translated as well.

Can country and state values be retrieved in the local language using SOQL?

What am I missing?

Is the Country/State picklist not treated as a picklist as referenced in the documentation?

Best Answer

As the error hints, what you're pulling (BillingCountry) is not a picklist. If you take a look at the Address Compound Fields documentation, you'll see the following noted:

BillingCountry is a String
BillingCountryCode is a Picklist

You need to reference BillingCountryCode to pull your translated values:

SELECT toLabel(BillingCountryCode) FROM Account

Related Solutions

[SalesForce] How to Automate Translation

Translations sound suspiciously close to Metadata API which means it's not accessible from Apex (at least not without some callout-like hacks). Can't you just use standard Translation Workbench? Export to Excel, let users translate, import back... sure beats crafting XML files.

Besides even if you had such possibility to modify translations from Apex - how do you plan to write a "metadata change sniffer" that'd "fire" every time a new picklist is added or existing one modified? Or that would detect the fact a new language was enabled in the system?

I believe "you're doing it wrong" ;) I think it makes more sense to leverage the fact Salesforce kept picklist name & value identical (so no way to configure system out of the box to have <select>...<option value="Y">Yes</option>...</select>). If you need this Yes/No somewhere else as Y/N - consider making a formula field?

EDIT

Translations metadata missing in Eclipse - check your package.xml. Mine contains (among others)

<types>
    <members>*</members>
    <name>CustomObjectTranslation</name>
</types>
<types>
    <members>*</members>
    <name>Translations</name>
</types>

("Translations" is for apps, custom labels etc) and my Opportunity-es.objectTranslation has correct data in it:

<?xml version="1.0" encoding="UTF-8"?>
<CustomObjectTranslation xmlns="http://soap.sforce.com/2006/04/metadata">
...
<fields>
    <help>Si es Indirecto, debe rellenar el campo de mayorista</help>
    <label>Directo / Indirecto</label>
    <name>Direct_Indirect__c</name>
    <picklistValues>
        <masterLabel>Direct</masterLabel>
        <translation>Directo</translation>
    </picklistValues>
    <picklistValues>
        <masterLabel>Indirect</masterLabel>
        <translation>Indirecto</translation>
    </picklistValues>
</fields>
...
<fields>
    <name>LeadSource</name>
    <picklistValues>
        <masterLabel>Advertisement</masterLabel>
        <translation><!-- Advertisement --></translation>
    </picklistValues>
    <picklistValues>
        <masterLabel>Customer Event</masterLabel>
        <translation><!-- Customer Event --></translation>
    </picklistValues>
...

[SalesForce] toLabel() in aggregate query

SELECT toLabel(Equipment__r.technical_Platform__c) aliasTECPROB FROM Maintenance_Schedule__c GROUP BY Equipment__r.technical_Platform__c

Use Alias it will work out

Best Answer

Related Solutions

[SalesForce] How to Automate Translation

[SalesForce] toLabel() in aggregate query

Related Topic