+ Start a Discussion
beener3beener3 

Problems with Aggregate function

Hi.

 

I have a solution that can be resolved using two SOQL queries instead of one. though I think It can be done with one. if you are open to the challange, please continue...

 

I think I possibly found a bug in Apex. or is this by design? would be happy to be told I'm wrong and have a solution to my problem :smileyhappy:. If I am correct, the Bug is in my 5th attempted solution.

 

My object "Timesheet__c" is a detail object of Contact (which is master). Each contact has a field called "Employee_Reference__c" Which is a Formula(Number, Roll-up Summery from a 3rd object called "Payroll Record").

 

I am attempting to generate a table that would show some aggregate values from "Timesheet__c" along with the employee's reference.

This will give me a table with the following Columns [Employee Reference, Sum1, Sum2, Sum3] etc.

The problem is that Employee_Reference__c is not a groupable field, and thus a SOQL group by statement does not allow this field to be used.

 

I've attempted different solutions and failed:

 

  1. query from the parent (Contact), doesn't work since salesforce doesn't support Aggregate functions in the sub query, Agg. Functions are only  in the parent query)

  2. Query the field from the master object via the child table. I was hoping the grouping would work as the grouped field implies a unique order. No success.
Select Worker__r.Employee_Reference__c from Timesheet__c Group by Worker__c

 3. Using a formula field in the child  (Which looks up the parent Employee_Reference__c value) object and querying that. Failed miserably.

4. Copying the formula field to a non-formula field. Fails as it would need cross object workflow rule (the field update would need to be done for the parent object), which is not supported.

 

5. I've decided to disguise the Employee_Reference__c as an aggregate object. this Looks at the parent object and takes the MAX(). I hoped this would work as there is only one value to per line, MAX() should always return the value I want.

The query complies and runs, and looks good on the Eclipse Schema Editoer query window. However, when attempting to address this field after the query, I get the error "Save error: Invalid field MAX_Employee_Reference for SObject AggregateResult" .

 

Here is the SOQL Query: 

select Worker__c Worker, SUM(Standard_Pay_Taxed__c) SUM_Standard_Pay_Taxed,    														MAX(Worker__r.Employee_Reference__c) MAX_Employee_Reference,    														SUM(Holiday_Pay__c) SUM_Holiday_Pay,  															SUM(Taxed_Expenses__c ) SUM_Taxed_Expenses,     														SUM(Taxed_Deductions__c) SUM_Taxed_Deductions,															SUM(Non_Taxed_Expenses__c ) SUM_Non_Taxed_Expenses,     														SUM(Non_Taxed_Deductions__c) SUM_Non_Taxed_Deductions    														from timesheet__c where Week__r.Week_Ending__c = LAST_N_DAYS:7
Group by Worker__c

6. My solution will be to use a second SOQL, and to then use a map to align the Employee_Reference__c values. I don't like it though...

 

Thank you for your time.

sandersensandersen

In the docs: 

http://www.salesforce.com/us/developer/docs/api/index_Left.htm#StartTopic=Content/sforce_api_calls_soql_select_agg_functions_field_types.htm?SearchType=Stem

 

It indicates that some field types are not groupable. It may be that rollup summaries of your type aren't groupable. You can be sure by doing a describe and checking the "groupable" property of your field.

 

If your problems go away if Employee Reference is groupable, you might want to invest in workflow or a trigger to calculate that yourself, and turn it into a groupable field.

 

 

Hope that's helpful,
Steve 

 

beener3beener3

Hi Steve,

 

I do thank you for the reply. I don't want to seem cheeky, I don't see that you've said anything that I didn't. I've already noted that the field is NOT groupable. and that workflows didn't solve it as it was a change in the detail (Child) object that should trigger the master field change. This is to the best of my ability still not supported.

 

For this reason I've used a 2nd Select statement and taken the values from there. seems a waste to me.

 

Thanks anyway.

 

Ben

sandersensandersen

By trigger I meant a trigger on the child that updates a field on the parent which would be groupable.

 

It seems like you've got the best solution, though.

Farhat MirzaFarhat Mirza

I am facing the problem with aggregate functions  when packaged the code.


Below is my lines of code with comments.

Any feed will be of great help to us.

       List<aggregateResult> results = [SELECT Date__c, MeasureID__c,SUM(MeasureValue__c) totalMeasureValue
        FROM UserAdoption__c  GROUP BY  Date__c,MeasureID__c ORDER BY Date__c];
      
            Date measureDate;
            String measureId;
            String measureName;
            Integer totalMeasureValue;
           for (AggregateResult ar : results){
        //Works:Below line works fine with out namespace
               measureDate= Date.valueOf(ar.get('Date__C'));
       //Not Working:But when i packaged it and give some namespace('XYZ') the above line fails
           // error:invalid field Date__C
   
       //Works:If prefix with namesapce the above line works   

           measureDate= Date.valueOf(ar.get('XYZ__Date__C'));   
             
                      
          }