[SalesForce] toLabel() in aggregate query

I am using the toLabel function in an aggregate query, but it doesn't return the translation of the picklist value.

I have changed the language of the user to Dutch and executed the following query:

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

The query returns the English value: Other Elevator likes

When I remove the GROUP BY from the query, it returns the translated value:

SELECT toLabel(Equipment__r.technical_Platform__c) 
FROM Maintenance_Schedule__c

Result with the Dutch value: Overige

I am not able to add the toLabel function in the GROUP BY, since only date aggregate functions are allowed as grouping expressions. I checked the documentation of the toLabel function, but it doesn't mention anything about grouping expressions. Documentation

I also found a topic about the same issue on the developer forums of Salesforce, but it didn't get any replies and it is from 2012. Topic

I could execute a 2nd query without the GROUP BY to get the translated values and map it to results of the results of the query with the GROUP BY, but I am wondering if there is a better solution?

Best Answer

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

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] query the translated value and return the key in translation workbench

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.

Best Answer

Related Solutions

[SalesForce] How to Automate Translation

[SalesForce] query the translated value and return the key in translation workbench

Related Topic