How can I find the exact field names and calculations, from a reporting field's friendly name?

Question

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.

Answer 1

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.

Answer 2

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)'));