+ Start a Discussion
meet.sivameet.siva 

Using Aggregate function in apex class.

Hi, im new to force platform.i have to clarify some doubts.so please help me.

I have two custom objects Opportunity_CRM__c and test__c. Lets consider Opportunity_CRM__c takes the following values,

 

opportunity ID          Employee ID       Amount

        1                                 1                        920

        2                                 1                        400

        3                                 1                        300

        4                                 2                        600

 

 


I have two fields in test__c namely Emploee_Id__c and Totot_count_opportunities__c. I have to populate the custom object test__c with following values,

 

Employee ID              Total Count Opportunities

         1                                     3

         2                                     1

 

i.e. i have to find the total number of opportunities with respect to each employee.

 

My Apex class,

global class opportunity 
{
    global static void totaloppcnt(Opportunity_CRM__c[] op)
    {
            try{
            String eid;
            Integer tcount;
            test__c var = new test__c();
            for (Opportunity_CRM__c w :op)
            {    
                List<AggregateResult> groupedResults = [SELECT Employee_ID__c,COUNT(Amount__c)tntcnt FROM Opportunity_CRM__c GROUP BY Employee_ID__c];
                for (AggregateResult ar : groupedResults) 
                {
                    eid = (String)ar.get('Employee_ID__c');
                    tcount = (Integer)ar.get('tntcnt');
                    var.Employee_ID__c = eid;
                    var.Total_Opp_Cnt__c = tcount;
                    insert var;
                }
            }
            }
            catch(DmlException e) {}
    
      }
}

Initially i gat System.DmlException: Insert failed. First exception on row 0 with id a0R90000002Hg2wEAC; first error: INVALID_FIELD_FOR_INSERT_UPDATE, cannot specify Id in an insert call: [Id]..so i used try and catch to overcome that error.After that insert operation is successfully done but im not getting the desired results.

 

My Apex trigger,

trigger oppcnttrigger on Opportunity_CRM__c (after insert,after update) 
{
    Opportunity_CRM__c[] p = Trigger.new;
    opportunity.totaloppcnt(p);
}

Please help me..its urgent..

 

 

Vinit_KumarVinit_Kumar

Your logic does not seem to be correct as you are inserting record every test__c objetc.Hence,you must be getting a new record everytime with the correct value.

 

You need to check if a record already exists,then update it else create a new record.

 

also,if you cpould explain as how these objects are realted I can make it more clear.

meet.sivameet.siva

Hey thanks.

I cant get yout point.Can you please make it clear?

I created the custom objects Opportunity_CRM__c and test__c with required fields and I uploaded the data into Opportunity_CRM__c using dataloader.Once i upload the data,test__c must be populated with total count of opportunities for each employee.Data is uploaded into Opportunity_CRM__c and it is used for calculation.