0

I have query in visualforce javascript function.It is throwing the following error "Name field cannot be grouped in query call".Can someone point me why it is giving that error.I saw this error in browser javascript console

Not: FE_Controller__r.Name field is autonumber field

var result = sforce.connection.query("SELECT id ID,FE_Controller__r.Name CONTROLLER," + 
        "sum(Budget_Expenses__c) BUDGET, Sum(Amount__c) AMOUNT, "  +
        "RecordType.Name TYPE, Funding_Level__c LEVEL, Fiscal_Year__c FY" +
        "FROM FEvent__c " +
        "where status__c !='Cancelled'" +
        "group by id,RecordType.Name, Funding_Level__c," +
        "Fiscal_Year__c,FE_Controller__r.Name ");
  • Your query is weird, why do you GROUP BY Id? Do you need all the columns? – eyescream Jan 17 '14 at 19:34
  • yes I need all columns.when I remove FE_Controller__r.Name from query then it is working fine. – sfdc Jan 17 '14 at 19:36
3
SELECT id ID, FE_Controller__r.Name CONTROLLER,
sum(Budget_Expenses__c) BUDGET, Sum(Amount__c) AMOUNT,
RecordType.Name TYPE, Funding_Level__c LEVEL, Fiscal_Year__c FY,
FROM FEvent__c
where status__c !='Cancelled'
group by id,RecordType.Name, Funding_Level__c, Fiscal_Year__c,FE_Controller__r.Name

This shouldn't even compile because you have comma (,) before FROM (not to mention unclosed quote)... Probably you cut out some fields to post here?

I'd say that with GROUP BY Id the whole idea if aggregation becomes useless. Every row will be unique because Id is unique. Same with autonumber fields!

Try with straightforward query?

SELECT id, FE_Controller__r.Name,
Budget_Expenses__c, Amount__c,
RecordType.Name, Funding_Level__c, Fiscal_Year__c
FROM FEvent__c
where status__c !='Cancelled'

(you can't use column aliases then but normal result.records[0].FE_Controller__r.Name should work)


Autonumber and formula fields aren't "groupable":

System.debug(Schema.sObjectType.Case.fields.CaseNumber.isGroupable()); // false

If you really want aggregation by Controller Name and there's only Name field you could make-do with either workflow that'd be writing the generated Name to helper text field. Or split the query into 2:

SELECT FE_Controller__c, SUM(Budget_Expenses__c), SUM(Amount__c)
FROM FEvent__c
GROUP BY FE_Controller__c
  • For testing purpose I have updated the query with another field (FE_Controller_r.Amount__c) on same object and its giving me similar error."Amount__c cannot be grouped in query call" – sfdc Jan 17 '14 at 20:10
  • And is it a normal field (Number, Currency) or a formula field? (I've never tried to group by rollup summary field). Maybe try to explain what are you trying to achieve, what kind of table of data you'd like to have as a result... Controllers with info of all Events? Aggregated or detailed? – eyescream Jan 17 '14 at 20:13
  • It is Number field – sfdc Jan 17 '14 at 20:16
  • You can't group on __r fields; see my comment on @JimRae's answer. – Mike Chale Jan 17 '14 at 21:12
  • @MikeChale , I swear this works in my org: SELECT SUM(Value__c), Order__c, MAX(Order__r.Name) FROM Order_Line_Item__c GROUP BY Order__c LIMIT 5. Call it dirty hack if you wish. Technically the docs are correct ;) – eyescream Jan 17 '14 at 21:23
2

According to the documentation, you can't use child relationship expressions that use the __r syntax in a query that uses a GROUP BY clause.

If you really need to group this way, you are going to need to refactor your code. I would suggest that you consider using Javascript Remoting, and leveraging a "Wrapper Class" with a map to group the data the way you need.

  • He's going "up" not "down" (parent relationship). And I'd swear stuff like that worked for me in the past. – eyescream Jan 17 '14 at 19:42
  • He said he removed FE_Controller__r.Name and it worked. Isn't that "down" – JimRae Jan 17 '14 at 19:46
  • Can you provide a link to that documentation and quote the relevant excerpt? – Mike Chale Jan 17 '14 at 19:48
  • "Down" would be a subquery, something that looks like a related list. SELECT Id, Name, (SELECT Email FROM Contacts) FROM Account is "down". "Up" is via lookup, SELECT COUNT(Id), Account.Name FROM Contact GROUP BY Account.Name works fine – eyescream Jan 17 '14 at 19:49
  • I am going UP ..there is lookup relationship – sfdc Jan 17 '14 at 19:50

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.