Apex Code Development You need to sign in to do that
Don't have an account?
rick82000 Rollup Summary For Lookup Relationship
Hello,
I am trying to roll up a custom amount fields based on the parent/child relationship on the account.
For Example:
- There is a Parent Company record, and two Child company records.
- Each Child Company has 100k in the field (Assets Under Advisement). I would like to sum the Child company Assets Under
- Advisement on the parent company. Hence the parent company Assets Under Advisement should be 200k.
Here is my Trigger for the same (I need help on how can I complete this trigger without running on Apex Governor limit for too many SOQL Queries. Please note that my trigger is not complete.Of course :smileytongue::)
trigger RecalculateAssetsUnderAdvicement on Account (after update) {
Double AssetsTotal =0.0;
Account[] Originator = Trigger.new;
Account [] UpdateAcc = new Account[]{};
for (Account Origin : Originator){
for (Account OuterLoop : [select id, Assets_Under_Advicement__c from Account where id =: Origin.ParentId]) {
for (Account ChildRecords: [select id, Assets_Under_Advicement__c from Account where id =: Origin.ParentId]) {
AssetsTotal = ChildRecords.Assets_Under_Advicement__c;
}
OuterLoop.Assets_Under_Advicement__c = AssetsTotal;
UpdateAcc.add(OuterLoop);
}
update UpdateAcc;
}
}
trigger RecalculateAssetsUnderAdvicement on Account (after update) {
Double AssetsTotal =0.0;
Account[] Originator = Trigger.new;
for (Account Origin : Originator){
if (Origin.ParentId != null) {
for (Account ChildRecords: [select id, Assets_Under_Advicement__c from Account where id =: Origin.ParentId]) {
AssetsTotal = ChildRecords.Assets_Under_Advicement__c;
}
}
update UpdateAcc;
}
}
I have around 2600 records that i had to iterate through. I wrote another method that worked for me. I simply added and subtracted the value to the parent record instead of re-summing all records.
Bob, I am curious to know do we have to do the Batch Apex Method if we have more than 1000 records?
Thanks for you help.
trigger AssetsUnderAdvisement on Investment_Name__c (before insert, before update) { Set <Id> accountId = new Set<Id>(); // Double Difference = 0.0; Double secondDifference = 0.0; for (Investment_Name__c Origin : Trigger.new) { if (Trigger.isUpdate) { Investment_Name__c beforeUpdate = System.Trigger.oldMap.get(Origin.Id); if (beforeUpdate != null) { if ((beforeUpdate.Consultant_del__c != null) & (beforeUpdate.Consultant_del__c != Origin.Consultant_del__c)){ accountId.add(beforeupdate.Consultant_del__c); if (Origin.Consultant_del__c != null) { accountId.add(Origin.Consultant_del__c); } } else if (beforeUpdate.Consultant_del__c == Origin.Consultant_del__c) { accountId.add(Origin.Consultant_del__c); } else if (Origin.Consultant_del__c == null) { break; } // Adding all account ids for Primary Consultant if ((beforeUpdate.Secondary_Consultant__c != null) & (beforeUpdate.Secondary_Consultant__c != Origin.Secondary_Consultant__c)){ accountId.add(beforeupdate.Secondary_Consultant__c); if (Origin.Secondary_Consultant__c != null) { accountId.add(Origin.Secondary_Consultant__c); } } else if (beforeUpdate.Secondary_Consultant__c == Origin.Secondary_Consultant__c) { accountId.add(Origin.Secondary_Consultant__c); } else if (Origin.Secondary_Consultant__c == null) { break; } // Adding all accounts ids for Secondary Consultants } else{ if (Origin.Consultant_del__c != null) { accountId.add(Origin.Consultant_del__c); } if (Origin.Secondary_Consultant__c != null) { accountId.add(Origin.Secondary_Consultant__c); } // Adding all account ids if the before Update List is Null } accountid.add(Origin.Consultant_del__c); accountid.add(Origin.Secondary_Consultant__c); } // Is Update Id Triggers if (Trigger.isInsert) { if (Origin.Consultant_del__c != null) { accountId.add(Origin.Consultant_del__c); } if (Origin.Secondary_Consultant__c != null) { accountId.add(Origin.Secondary_Consultant__c); } } // Is Insert Triggers } // End of the first Trigger for Loop. List <Account> allAccounts = [select id, Assets_Under_Advisement__c from Account where id in : accountId]; /*========================================Actual Trigger Actions Start==================================================*/ for (Investment_Name__c Origin : Trigger.new) { if (Trigger.isInsert) { for (Account acctoUpdate : allAccounts) { if (Origin.Consultant_del__c != null) { acctoUpdate.Assets_Under_Advisement__c += Origin.USD_Value__c; } if (Origin.Secondary_Consultant__c != null) { acctoUpdate.Assets_Under_Advisement__c += Origin.USD_Value__c; } } } /*============================================================================================================================ Update Trigger logic ==============================================================================================================================*/ if (Trigger.isUpdate) { Investment_Name__c beforeacc = System.Trigger.oldMap.get(Origin.Id); for (Account acctoUpdate : allAccounts) { if (beforeacc.USD_Value__c != null) { Difference = Origin.USD_Value__c - beforeacc.USD_Value__c; secondDifference = Origin.USD_Value__c - beforeacc.USD_Value__c; } else { Difference = Origin.USD_Value__c; SecondDifference = Origin.USD_Value__c - beforeacc.USD_Value__c; } // Primary Consultant Math Starts Here // If the Before Consultant Id equals null then continue if (beforeacc.Consultant_del__c == null) { // If the Origin Consultant id equals null then don't take any action else add the Value to the Origin Consultant record. if (Origin.Consultant_del__c != null) { if (acctoUpdate.Id == Origin.Consultant_del__c) { acctoUpdate.Assets_Under_Advisement__c += Difference; Difference = 0.0; } } } // If the Before Consultant not equal to null then continue if (beforeacc.Consultant_del__c != null) { //if before Consultant Id equals after consultant id then just add the difference. if (beforeacc.Consultant_del__c == Origin.Consultant_del__c) { if (acctoUpdate.Id == Origin.Consultant_del__c) acctoUpdate.Assets_Under_Advisement__c += Difference; Difference = 0.0; } // If before Consultant Id not equals to after consultant Id then do the following // First Add the USD value on Investment to Consultant or Origin Trigger //Subtract the USD value of the Investment from the before account id. if (beforeacc.Consultant_del__c != Origin.Consultant_del__c) { if (acctoUpdate.Id == Origin.Consultant_del__c) { acctoUpdate.Assets_Under_Advisement__c += Origin.USD_Value__c; } if (acctoUpdate.Id == beforeacc.Consultant_del__c) { acctoUpdate.Assets_Under_Advisement__c -= Origin.USD_Value__c; } } } else if (beforeacc.Consultant_del__c == null) { System.debug('Did you get to where Consultant before is null' + beforeacc.Consultant_del__c); if (Origin.Consultant_del__c != null) { if (acctoupdate.Id == Origin.Consultant_del__c) { acctoUpdate.Assets_Under_Advisement__c += Origin.USD_Value__c; } } } // Primary Consultant Math Ends Here // Secondary Consultant Math Starts Here if (beforeacc.Secondary_Consultant__c != null) { //if before Consultant Id equals after consultant id then just add the secondDifference. if (beforeacc.Secondary_Consultant__c == Origin.Secondary_Consultant__c) { if (acctoUpdate.Id == Origin.Secondary_Consultant__c) acctoUpdate.Assets_Under_Advisement__c += secondDifference; secondDifference = 0.0; } // If before Consultant Id not equals to after consultant Id then do the following // First Add the USD value on Investment to Consultant or Origin Trigger //Subtract the USD value of the Investment from the before account id. if (beforeacc.Secondary_Consultant__c != Origin.Secondary_Consultant__c) { if (acctoUpdate.Id == Origin.Secondary_Consultant__c) { acctoUpdate.Assets_Under_Advisement__c += Origin.USD_Value__c; } if (acctoUpdate.Id == beforeacc.Secondary_Consultant__c) { acctoUpdate.Assets_Under_Advisement__c -= Origin.USD_Value__c; } } } if (beforeacc.Secondary_Consultant__c == null) { system.debug('Secondary Consultant should be null' + beforeacc.Secondary_Consultant__c + Origin.Secondary_Consultant__c); if (Origin.Secondary_Consultant__c != null) { system.debug('Original Consultant is not null' + Origin.Secondary_Consultant__c); if (acctoupdate.Id == Origin.Secondary_Consultant__c) { System.debug('Original and Sets' + acctoupdate.Id + Origin.Secondary_Consultant__c); acctoUpdate.Assets_Under_Advisement__c += Origin.USD_Value__c; } } } // Primary Consultant Math Starts Here } } } update allAccounts; }All Answers
I think you want something like the following:
trigger RecalculateAssetsUnderAdvicement on Account (after update) { Double AssetsTotal =0.0; Account[] Originator = Trigger.new; Account [] UpdateAcc = new Account[]{}; // get all the parent accounts Map<Id, Account> parentMap=new Map<Id, Account>(); parentMap.putAll([select id, Assets_Under_Advicement__c from Account where id // get all the child accounts Map<Id, List<Account>> childMap=new Map<Id, List<Account>>(); List<Id> parentIds=new List<Id>(); for (Account acc : trigger.new) { parentIds.put(acc.parentId); } for (Account childAccs : [select id, Assets_Under_Advicement__c from Account where id IN :parentIds]) { List<Account> children=childMap.get(acc.parentId); if (null==children) { children=new List<Account>(); childMap.put(acc.parentId, children); } children.add(acc); } for (Account Origin : Originator){ Account parent=parentMap.get(origin.parentId); for (Account ChildRecords: childMap.get(origin.parentId) { AssetsTotal = ChildRecords.Assets_Under_Advicement__c; } parent.Assets_Under_Advicement__c = AssetsTotal; UpdateAcc.add(parent); } update UpdateAcc; }All the parents/children are retrieved up front using minimal queries and stored in collections, then the collections are iterated and changes made as appropriate. The update is moved outside of the loop so that it can be accomplished with a single call.
Caveat - I haven't compiled this so there may be some typos, but the principle should work.
Bob Buzzard,
Thanks for responding. I know you warned me about typos, but I am confused on your half SOQL statement.
I am not sure if you are trying to reference a specific account or how does this part of the trigger works.
Thank you for your help in advance.
Ha - a somewhat larger typo than I anticipated. Revised code snippet below - as you can see the parent ids list is created slightly earlier now and used to retrieve all the parent accounts through a single SOQL query.
// get all the parent accounts List<Id> parentIds=new List<Id>(); for (Account acc : trigger.new) { parentIds.put(acc.parentId); } Map<Id, Account> parentMap=new Map<Id, Account>(); parentMap.putAll([select id, Assets_Under_Advicement__c from Account where id in :parentIds]); // get all the child accounts Map<Id, List<Account>> childMap=new Map<Id, List<Account>>();The MAP has a limit for 1000 records. So does the list. How do you get over that limit?
What are your data volumes then?
I have around 2600 records that i had to iterate through. I wrote another method that worked for me. I simply added and subtracted the value to the parent record instead of re-summing all records.
Bob, I am curious to know do we have to do the Batch Apex Method if we have more than 1000 records?
Thanks for you help.
trigger AssetsUnderAdvisement on Investment_Name__c (before insert, before update) { Set <Id> accountId = new Set<Id>(); // Double Difference = 0.0; Double secondDifference = 0.0; for (Investment_Name__c Origin : Trigger.new) { if (Trigger.isUpdate) { Investment_Name__c beforeUpdate = System.Trigger.oldMap.get(Origin.Id); if (beforeUpdate != null) { if ((beforeUpdate.Consultant_del__c != null) & (beforeUpdate.Consultant_del__c != Origin.Consultant_del__c)){ accountId.add(beforeupdate.Consultant_del__c); if (Origin.Consultant_del__c != null) { accountId.add(Origin.Consultant_del__c); } } else if (beforeUpdate.Consultant_del__c == Origin.Consultant_del__c) { accountId.add(Origin.Consultant_del__c); } else if (Origin.Consultant_del__c == null) { break; } // Adding all account ids for Primary Consultant if ((beforeUpdate.Secondary_Consultant__c != null) & (beforeUpdate.Secondary_Consultant__c != Origin.Secondary_Consultant__c)){ accountId.add(beforeupdate.Secondary_Consultant__c); if (Origin.Secondary_Consultant__c != null) { accountId.add(Origin.Secondary_Consultant__c); } } else if (beforeUpdate.Secondary_Consultant__c == Origin.Secondary_Consultant__c) { accountId.add(Origin.Secondary_Consultant__c); } else if (Origin.Secondary_Consultant__c == null) { break; } // Adding all accounts ids for Secondary Consultants } else{ if (Origin.Consultant_del__c != null) { accountId.add(Origin.Consultant_del__c); } if (Origin.Secondary_Consultant__c != null) { accountId.add(Origin.Secondary_Consultant__c); } // Adding all account ids if the before Update List is Null } accountid.add(Origin.Consultant_del__c); accountid.add(Origin.Secondary_Consultant__c); } // Is Update Id Triggers if (Trigger.isInsert) { if (Origin.Consultant_del__c != null) { accountId.add(Origin.Consultant_del__c); } if (Origin.Secondary_Consultant__c != null) { accountId.add(Origin.Secondary_Consultant__c); } } // Is Insert Triggers } // End of the first Trigger for Loop. List <Account> allAccounts = [select id, Assets_Under_Advisement__c from Account where id in : accountId]; /*========================================Actual Trigger Actions Start==================================================*/ for (Investment_Name__c Origin : Trigger.new) { if (Trigger.isInsert) { for (Account acctoUpdate : allAccounts) { if (Origin.Consultant_del__c != null) { acctoUpdate.Assets_Under_Advisement__c += Origin.USD_Value__c; } if (Origin.Secondary_Consultant__c != null) { acctoUpdate.Assets_Under_Advisement__c += Origin.USD_Value__c; } } } /*============================================================================================================================ Update Trigger logic ==============================================================================================================================*/ if (Trigger.isUpdate) { Investment_Name__c beforeacc = System.Trigger.oldMap.get(Origin.Id); for (Account acctoUpdate : allAccounts) { if (beforeacc.USD_Value__c != null) { Difference = Origin.USD_Value__c - beforeacc.USD_Value__c; secondDifference = Origin.USD_Value__c - beforeacc.USD_Value__c; } else { Difference = Origin.USD_Value__c; SecondDifference = Origin.USD_Value__c - beforeacc.USD_Value__c; } // Primary Consultant Math Starts Here // If the Before Consultant Id equals null then continue if (beforeacc.Consultant_del__c == null) { // If the Origin Consultant id equals null then don't take any action else add the Value to the Origin Consultant record. if (Origin.Consultant_del__c != null) { if (acctoUpdate.Id == Origin.Consultant_del__c) { acctoUpdate.Assets_Under_Advisement__c += Difference; Difference = 0.0; } } } // If the Before Consultant not equal to null then continue if (beforeacc.Consultant_del__c != null) { //if before Consultant Id equals after consultant id then just add the difference. if (beforeacc.Consultant_del__c == Origin.Consultant_del__c) { if (acctoUpdate.Id == Origin.Consultant_del__c) acctoUpdate.Assets_Under_Advisement__c += Difference; Difference = 0.0; } // If before Consultant Id not equals to after consultant Id then do the following // First Add the USD value on Investment to Consultant or Origin Trigger //Subtract the USD value of the Investment from the before account id. if (beforeacc.Consultant_del__c != Origin.Consultant_del__c) { if (acctoUpdate.Id == Origin.Consultant_del__c) { acctoUpdate.Assets_Under_Advisement__c += Origin.USD_Value__c; } if (acctoUpdate.Id == beforeacc.Consultant_del__c) { acctoUpdate.Assets_Under_Advisement__c -= Origin.USD_Value__c; } } } else if (beforeacc.Consultant_del__c == null) { System.debug('Did you get to where Consultant before is null' + beforeacc.Consultant_del__c); if (Origin.Consultant_del__c != null) { if (acctoupdate.Id == Origin.Consultant_del__c) { acctoUpdate.Assets_Under_Advisement__c += Origin.USD_Value__c; } } } // Primary Consultant Math Ends Here // Secondary Consultant Math Starts Here if (beforeacc.Secondary_Consultant__c != null) { //if before Consultant Id equals after consultant id then just add the secondDifference. if (beforeacc.Secondary_Consultant__c == Origin.Secondary_Consultant__c) { if (acctoUpdate.Id == Origin.Secondary_Consultant__c) acctoUpdate.Assets_Under_Advisement__c += secondDifference; secondDifference = 0.0; } // If before Consultant Id not equals to after consultant Id then do the following // First Add the USD value on Investment to Consultant or Origin Trigger //Subtract the USD value of the Investment from the before account id. if (beforeacc.Secondary_Consultant__c != Origin.Secondary_Consultant__c) { if (acctoUpdate.Id == Origin.Secondary_Consultant__c) { acctoUpdate.Assets_Under_Advisement__c += Origin.USD_Value__c; } if (acctoUpdate.Id == beforeacc.Secondary_Consultant__c) { acctoUpdate.Assets_Under_Advisement__c -= Origin.USD_Value__c; } } } if (beforeacc.Secondary_Consultant__c == null) { system.debug('Secondary Consultant should be null' + beforeacc.Secondary_Consultant__c + Origin.Secondary_Consultant__c); if (Origin.Secondary_Consultant__c != null) { system.debug('Original Consultant is not null' + Origin.Secondary_Consultant__c); if (acctoupdate.Id == Origin.Secondary_Consultant__c) { System.debug('Original and Sets' + acctoupdate.Id + Origin.Secondary_Consultant__c); acctoUpdate.Assets_Under_Advisement__c += Origin.USD_Value__c; } } } // Primary Consultant Math Starts Here } } } update allAccounts; }Here's another alternative:
Salesforce Roll-Up Summary Trigger