function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Chad RitchieChad Ritchie 

Aggregate Result Trigger

Hey guys, trying to create trigger to sum up positions(new, existing), and then update total values for the investors. I am using SEI_Investor_Id__c to match positions to investors.

trigger SumPositions on Investor__c (before insert, before update, before delete) {
    for (Investor__c record : Trigger.new) {
        record.Sum_of_Positions__c = null;
    }
    for(AggregateResult result: [SELECT SUM(Position__c.Balance__c) Amt, SEI_Investor_Id__c, Id 
                                 FROM Position__c Id 
                                 WHERE SEI_Investor_Id__c 
                                 IN :Trigger.new GROUP BY SEI_Investor_Id__c, Id]) {
         Trigger.newMap.get((Id)result.get('Id')).Sum_of_Positions__c = (Decimal)result.get('Amt');
     }
 }
Best Answer chosen by Chad Ritchie
Steven NsubugaSteven Nsubuga
It is much better to create the trigger on the child record rather than the parent. 
Trigger SumPositions on Position__c (after insert, after update, after delete) {
	
	Set<Id> investorIds = new Set<Id>();
    List<Investor__c> investorsToUpdate = new List<Investor__c>();

	if (!Trigger.isDelete) {
		for (Position__c p : Trigger.new) {
			investorIds.add(p.SEI_Investor_Id__c);
		}
	}

    if (Trigger.isUpdate || Trigger.isDelete) {
        for (Position__c p : Trigger.old) {
            investorIds.add(SEI_Investor_Id__c);
        }
    }

    // get a map of the accounts with the number of items
    Map<id, Investor__c> popMap = new Map<id,Investor__c>([select id, Sum_of_Positions__c from Investor__c where id IN :investorIds]);

    List<AggregateResult> ars = [SELECT SEI_Investor_Id__c, count(Id) FROM Position__c WHERE SEI_Investor_Id__c IN :investorIds GROUP BY SEI_Investor_Id__c];
    
    for (AggregateResult ar : ars) {
        popMap.get(ar.get('SEI_Investor_Id__c')).Sum_of_Positions__c = ar.get('expr0');
        investorsToUpdate.add(popMap.get(ar.get(SEI_Investor_Id__c)));
    }

    update investorsToUpdate;
}

 

All Answers

Steven NsubugaSteven Nsubuga
trigger SumPositions on Investor__c (before insert, before update, before delete) {
    for (Investor__c record : Trigger.new) {
        record.Sum_of_Positions__c = null;
    }
    for(AggregateResult result: [SELECT SEI_Investor_Id__c, SUM(Position__c.Balance__c)
                                 FROM Position__c WHERE SEI_Investor_Id__c 
                                 IN :Trigger.newMap.keyset() GROUP BY SEI_Investor_Id__c]) {
         Trigger.newMap.get(result.get('SEI_Investor_Id__c')).Sum_of_Positions__c = (Decimal)result.get('expr0');
     }
 }

 
Steven NsubugaSteven Nsubuga
It is much better to create the trigger on the child record rather than the parent. 
Trigger SumPositions on Position__c (after insert, after update, after delete) {
	
	Set<Id> investorIds = new Set<Id>();
    List<Investor__c> investorsToUpdate = new List<Investor__c>();

	if (!Trigger.isDelete) {
		for (Position__c p : Trigger.new) {
			investorIds.add(p.SEI_Investor_Id__c);
		}
	}

    if (Trigger.isUpdate || Trigger.isDelete) {
        for (Position__c p : Trigger.old) {
            investorIds.add(SEI_Investor_Id__c);
        }
    }

    // get a map of the accounts with the number of items
    Map<id, Investor__c> popMap = new Map<id,Investor__c>([select id, Sum_of_Positions__c from Investor__c where id IN :investorIds]);

    List<AggregateResult> ars = [SELECT SEI_Investor_Id__c, count(Id) FROM Position__c WHERE SEI_Investor_Id__c IN :investorIds GROUP BY SEI_Investor_Id__c];
    
    for (AggregateResult ar : ars) {
        popMap.get(ar.get('SEI_Investor_Id__c')).Sum_of_Positions__c = ar.get('expr0');
        investorsToUpdate.add(popMap.get(ar.get(SEI_Investor_Id__c)));
    }

    update investorsToUpdate;
}

 
This was selected as the best answer
Chad RitchieChad Ritchie
Thank you very much! That explanation really helped.