I have a formula field that orders a particular field values. Example: A field for employees profession, for each profession there is an equivalent number value like, "IT = 1, Doctor = 2". I'm using this to sort the objects records by Profession Order.

Here is the code we are using:

Map<Id,Map<Id,decimal>> personTotalHoursMap = new Map<Id,Map<Id,decimal>>();
    List<AggregateResult> timeSheetsAggResult
    = [SELECT SUM(Hours__c) TotalHours, Person_Object__c, Opportunity__c
        FROM Time_Sheets__c
        GROUP BY Opportunity__c, Person_Object__c];
    for(AggregateResult personAggResult : timeSheetsAggResult)
        Id oppId = (Id)personAggResult .get('Opportunity__c');
        Id pOId= (Id)personAggResult .get('Person_Object__c');
        decimal totalHours = 0;
        if(personAggResult.get('Hours') != null)
            totalHours = (decimal)personAggResult.get('TotalHours');

        //Omitted some codes...


I want to sort this using the Person_Object__r.Profession_Order__c. How do I do that?


ORDER BY Clause can be used to sort by field. But the same field needs to be aggregated as well. Like below:

    [SELECT SUM(Hours__c) TotalHours, Person_Object__c, Opportunity__c, Person_Object__r.Profession_Order__c
            FROM Time_Sheets__c
                 GROUP BY Opportunity__c, Person_Object__c, Person_Object__r.Profession_Order__c 
                          ORDER BY Person_Object__r.Profession_Order__c ASC]
  • I tried this but there was an error when I loaded the page: System.UnexpectedException: field 'Profession_Order__c' can not be grouped in a query call – rpm07 Nov 19 '15 at 2:25
  • whats the data type of Profession_Order__c? – Mohith Shrivastava Nov 19 '15 at 3:00
  • it is a formula field and we are using CASE function to return what number is the string for profession. – rpm07 Nov 19 '15 at 3:12
  • thats the problem .Formula field cannot be used in GROUP BY .... – Mohith Shrivastava Nov 19 '15 at 3:14
  • How many options do you have in Profession_Order__c? if the number of options is less, you can use a map and a for loop. The query needs to be outside the for loop so you dont hit SOQL limits. – Shruthi Nov 19 '15 at 4:05

The problem here is you are using formula field in GROUPBY clause which causes the error .

Create a workflow to populate the same result on your record and use that field in aggregation instead .

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.