+ Start a Discussion
imishraimishra 

Sum of related list field value(Urgent)

Hi,
I have a  custom object Opportunity Service Line Item which has lookup relation to opportunity.
I have a field in Opportunity Service Line Item called 'TCV'.
Each Opportunity Line Item can have multiple Service Line Items.
I want to write a trigger to calculate the sum of TCV for all the Service Line Items and update in Opportunity Object.
Can anyone please provide a sample trigger for this.

Thanks,
Ipsita
Best Answer chosen by imishra
New_DeveloperNew_Developer
try this...

List<Opportunity> opplIst = new List<Opportunity>();

List<AggregateResult> ar = [Select Sum(TCV) tcvSum,OpportunityLineItem.OpportunityId from OpportunityServiceLineITem__c Group By OpportunityLineItem.OpportunityId]
Map<id , Double> map1= new Map<id , Double>();
Set<Id> ids = new Set<Id>();
for(AggregateResult result : ar)
{
map1.put((String)result.get('OppportunityLineItem.OpportunityId'),(Double)result.get('tcvSum'));
ids.add((ID)result.get('OpportunityLineITem.opportunityId'))
}

List<Opportunity> o = [Select id, fieldtoupdateonOoportunity__c from opportunity where id IN : ids];
for(opportunity opp : o {
opp.fieldtoupdateonOoportunity__c = map1.get(opp.id);
opplist.add(opp);
}
if(opplist.size()>0)
update opplist;

All Answers

New_DeveloperNew_Developer
try this...

List<Opportunity> opplIst = new List<Opportunity>();

List<AggregateResult> ar = [Select Sum(TCV) tcvSum,OpportunityLineItem.OpportunityId from OpportunityServiceLineITem__c Group By OpportunityLineItem.OpportunityId]
Map<id , Double> map1= new Map<id , Double>();
Set<Id> ids = new Set<Id>();
for(AggregateResult result : ar)
{
map1.put((String)result.get('OppportunityLineItem.OpportunityId'),(Double)result.get('tcvSum'));
ids.add((ID)result.get('OpportunityLineITem.opportunityId'))
}

List<Opportunity> o = [Select id, fieldtoupdateonOoportunity__c from opportunity where id IN : ids];
for(opportunity opp : o {
opp.fieldtoupdateonOoportunity__c = map1.get(opp.id);
opplist.add(opp);
}
if(opplist.size()>0)
update opplist;
This was selected as the best answer
imishraimishra
Thanks for the reply.
I have written the below code:
trigger updateRolledUpTCV on Opportunity_Service_Line_Item__c (after insert,after update) {

    List<Opportunity> opplIst = new List<Opportunity>();

    List<AggregateResult> ar = [Select Sum(TCV_Share__c) tcvSum,Opportunity_Service_Line_Item__c.Opportunity__c from Opportunity_Service_Line_Item__c Group By Opportunity_Service_Line_Item__c.Opportunity__c];
    Map<id , Double> map1= new Map<id , Double>();
    Set<Id> ids = new Set<Id>();
    for(AggregateResult result : ar){
        map1.put((String)result.get('Opportunity_Service_Line_Item__c.Opportunity__c'),(Double)result.get('tcvSum'));
        ids.add((ID)result.get('Opportunity_Service_Line_Item__c.Opportunity__c'));
    }

    List<Opportunity> o = [Select id, Rolled_Up_TCV_New__c from opportunity where id IN : ids];
    for(opportunity opp : o) {
        opp.Rolled_Up_TCV_New__c = map1.get(opp.id);
        opplist.add(opp);
    }
    if(opplist.size()>0)
    update opplist;

}

While saving the trigger i don't get any error, but when i try to save opportunity line item i get an error:
invalid field for aggregate result: Opportunity_Service_Line_Item__c.Opportunity__c.
imishraimishra
Sorry it was my mistake.
Your code worked fine.

Thanks.
Usman NasirUsman Nasir
hi imishra, I'm trying to use your trigger code and am getting the same error you got, how did you fix it? thanks in advance !