0

I'm diving into a new system, and I see reports with a "Amount (converted)" field, from the Opportunity table. I'd like to look at this in SOQL, but there's no field which obviously matches; there aren't even any fields with the characters "Am" and "t". Based on the answers I've received here, it looks like this is a special reporting-only calculated column.

Given the friendly name for a calculated field ("Amount (converted)") how can I get the underlying field names and calculations? Something available through the report builder, REST, or SOAP interfaces would be great. The Schema Builder just lists the underlying names. The LayoutFieldList page lists most custom fields, but not this one.

1
  • 1
    Note that some columns in reports are not fields at all but rather formulas constructed within the report.
    – Adrian Larson
    Jan 19 '18 at 16:18
2

The reporting engine actually provides some additional fields that do not directly exist in the database. This is some special magic specific to reporting. In this specific case, Amount (converted) is translated into the following SOQL:

SELECT convertCurrency(Amount) FROM Opportunity ...

Generally speaking, if you can't directly find a given field, it's possible that it's a "magic" reporting field. In that case, you would need to know enough about SOQL to figure out how to get at that in code if you're interested in querying it.

I don't think there's necessarily a way to figure out a field's underlying SOQL statement from its label, so some deductive reasoning/educated guessing is in order. Approximately 99.9% of fields you'll run in to during reporting should be obvious as to which field they refer, but there are a few that do not actually exist as such in the database.

2
  • I think that's right. Is there no way to find the logic behind a reporting calculated field? Presumably people occasionally change their calculated fields, and need a mechanism to update the logic, unless perhaps calculated fields for reporting are strictly a built-in set, defined by Salesforce. Jan 19 '18 at 16:30
  • @JonofAllTrades All of the "magic" fields are related to standard objects and fields. The only exception to this rule that I'm aware of are currency fields; these fields display "(converted)" in the report, and are always "convertCurrency" conversions. Any custom calculated field would be a formula field, or a reporting formula field, which would be visible in the report builder.
    – sfdcfox
    Jan 19 '18 at 16:37
1

The "friendly name" would be the field's Label. What you are looking for as output is its API Name. You can loop through the fields in the describe and check their label. You might have multiple matches since the label is not unique.

public List<String> findMatchingApiNames(SObjectType sObjectType, String fieldLabel)
{
    List<String> names = new List<String>();
    for (SObjectField field : sObjectType.getDescribe().fields.getMap().values())
    {
        if (field.getDescribe().getLabel == fieldLabel)
        {
            names.add(String.valueOf(field));
        }
    }
    return names;
}

system.debug(findMatchingApiNames(Opportunity.sObjectType, 'Amount (converted)'));
2
  • Thank you! That seems to give me the same list as the LayoutFieldList page, which is missing this particular field. It doesn't even appear in the WSDL (and it's long-established, not a new addition since I pulled the WSDL). Jan 19 '18 at 16:25
  • 1
    @JonofAllTrades It's probably just the Amount field wrapped in a function like convertCurrency(Amount).
    – Adrian Larson
    Jan 19 '18 at 16:26

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.