I am trying to run this query in apex and run into following error.

    AggregateResult[] groupedResults = [select Campaign__r.id cid, Count(opportunity__c) oppcount, FORMAT(SUM(Opportunity__r.Amount)) oppAmt from Campaign_influence__c where opportunity__c!=null AND Campaign__c in :CampaignList group by Campaign__r.id ];

System.QueryException: Invalid field: 'Opportunity__r':

This query does run fine in developer console 'query editor'. It also works if I replace FORMAT(SUM(Opportunity__r.Amount)) with just SUM(Opportunity__r.amount). What am I doing wrong here?

  • Can you confirm Opportunity__c is a lookup field to the Opportunity object?
    – Adrian Larson
    Dec 28, 2016 at 21:04
  • That's correct!
    – shilpa
    Dec 28, 2016 at 21:05
  • Hi @shilpa, which API version you are using in that Apex class? Actually this feature is introduced in Spring'16 (API version 36.0 and later). developer.salesforce.com/releases/release/Spring_%252716/…
    – LakhanP
    Dec 29, 2016 at 5:03
  • yes I am on api version 38
    – shilpa
    Dec 29, 2016 at 17:05

1 Answer 1


The User you are running this query as almost certainly lacks read permission on the Opportunity__c field. Navigate to their Profile configuration (Setup > Manage Users > Profiles) and go to Object Settings > Campaign Influence. Make sure they have Read Access selected for Opportunity.

What you're trying to do should be supported otherwise. The documentation on FORMAT() states it supports aliasing and can be nested with aggregate functions:

The FORMAT function supports aliasing. In addition, aliasing is required when the query includes the same field multiple times. For example:

SELECT Id, LastModifiedDate, FORMAT(LastModifiedDate) formattedDate FROM Account

You can also nest it with aggregate or convertCurrency() functions. For example:

SELECT amount, FORMAT(amount) Amt, convertCurrency(amount) editDate, FORMAT(convertCurrency(amount)) convertedCurrency FROM Opportunity where id = '12345'
SELECT FORMAT(MIN(closedate)) Amt FROM opportunity
  • 2
    I have access to the field. This query runs fine in apex as well when i replace FORMAT(SUM(Opportunity__r.Amount)) with just SUM(Opportunity__r.amount). I guess it has something to do with the currency formatting?
    – shilpa
    Dec 28, 2016 at 21:15
  • Thanks Adrian! I avoided using FORMAT() and achieved what i needed by defining the fields in currency type instead of number fields. I am surprised salesforce failed to support what it has listed here developer.salesforce.com/releases/release/Spring_%252716/…. Thanks again for all your responses.
    – shilpa
    Dec 29, 2016 at 17:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .