+ Start a Discussion
Dave ShulmanDave Shulman 

Trigger to total related object then update relationship on Parent

Hi All.  Long Time admin but Very new to apex and developing.  I have a child object for accounts "sales data" which essentially is just transactional line items of purchases (related to the distributor, and the account).  So now I want to have a trigger on the sales data object that takes the sum of the amt in last 45 days by account then by distributor.  So that i can update the primary & secondary relationship on the account to indicate the distributor that is doing the most and second most sales for that account respectively.  below is what i have so far.  Please take a look and help out with getting thsi to run, and also how do i bulkify this??  Thanks guys!
 
trigger DistributorIDs on Sales_data__c (after insert, after update) {
       Date AfterDate = Date.today().addDays(-45);
       List <Account> AccountsList = new List<Account>();
    For (Sales_data__c Sd: Trigger.new) {
        AccountsList.add(Sd.End_user__r);
    } 
       Set<Account> DeDupSet = new Set<Account>();
       List<Account> DeDupAccounts = new List<Account>();
       DeDupSet.addAll(AccountsList);
       DeDupAccounts.addAll(DeDupSet);
    For (Account Acc: DeDupAccounts) {
        List<AggregateResult> Distributors = [SELECT Distributor__c, SUM(Amount__c) FROM Sales_Data__c 
                                    WHERE Date__c >= :AfterDate AND End_user__c = :Acc.Id
                                    GROUP BY Distributor__c ORDER BY SUM(Amount__c) LIMIT 2];
        ID FirstPosition = (ID)Distributors[0].get('expr0');
        ID SecondPosition = (ID)Distributors[1].get('expr1');
        Acc.Acct_Primary_Distributor__c = FirstPosition;
        Acc.Acct_Secondary_Distributor__c = SecondPosition;
    }
    update DeDupAccounts;
}

 
Best Answer chosen by Dave Shulman
SUCHARITA MONDALSUCHARITA MONDAL
Hey Dave,
You don't have to put third layer of loop as (line 24). Since you already have distributor list from subquery under line 17.
Just try to use that as sd.Distributor  (as your are doing for amount). One more thing, there must/should be two distrubutors/fields coming from Sales_Data as per line 29 and 30.

Note. Try to use one loop or max two  (remember the time complexity).

For total amount :- Analyse the loop (line 22 and line 23).  It says for every account , it'll process it's all sales area and once the inner loop closed then assign the total amount to Account.

for(Account acc : accList){ //looping through every account of above list
            for(Sales_Data__c sd : acc.Sales_Datas__r){  // looping thorugh every child of account
                if(sd.Amount__c !=null && sd.Distributor__c!=''){
                    totalAmount = totalAmount +sd.Amount__c; // summing up amount from every sales area
                  }
            }
           
            acc.Acct_Secondary_Distributor__c = totalAmount;  // here account will have sum of amount from it's all sales area.

Hope this helps.

Thanks,
Sucharita

All Answers

SUCHARITA MONDALSUCHARITA MONDAL

Hi Dave,

Please check with following code:
Observation from your code:
1. Adding AccountIds into Set (instead of putting it into List and then creating Set Line 10)
2. Refrain from using SOQL into For loops (Line 12), you will hit governer limit
3. For bulkification, code should be able to handle more than one record (Use Trigger.new --> List of records rather Trigger.new[0]--> just first record from entire list)
https://trailhead.salesforce.com/en/content/learn/modules/apex_triggers/apex_triggers_bulk
4.  Not sure why your are putting sumOfAmount on Acct_Secondary_Distributor__c (Line 18)
5. Use trigger framwork going forward (http://amitsalesforce.blogspot.com/2015/06/trigger-best-practices-sample-trigger.html)


Revised Code:

trigger DistributorIDs on Sales_data__c (after insert, after update) {
       private static double totalAmount = 0.0;
       private string distributor = "";
       Date AfterDate = Date.today().addDays(-45);
       Set<Id>accountIds = new Set<Id>()
       for (Sales_data__c Sd: Trigger.new) {
           if(Sd.End_user__c!=null)
                accountIds.add(Sd.End_user__c); // adding it to set
       } 
      
       List<Account> accountToUpdate = new List<Account>(); 
       
       List<Account>accList = new List<Account>([SELECT Id,Acct_Primary_Distributor__c,Acct_Secondary_Distributor__c,
                                                (SELECT Id,Distributor__c,Amount__c FROM Sales_Datas__r) // used sub query
                                                 FROM Account
                                                 WHERE Id IN : accountIds AND Date__c >= :AfterDate ]);
       
       if(accList.size()>0){ 
           for(Account acc : accList){ //looping through every account of above list
            for(Sales_Data__c sd : acc.Sales_Datas__r){  // looping thorugh every child of account
                if(sd.Amount__c !=null && sd.Distributor__c!=''){
                    totalAmount = totalAmount +sd.Amount__c;
                    distributor = distributor+","+sd.Distributor__c;
                  }
            }
            acc.Acct_Primary_Distributor__c = distributor;   // adding all distributors name from child to it's parent account
            acc.Acct_Secondary_Distributor__c = totalAmount; // adding all amount from child to it's parent account
            accountToUpdate.add(acc);
          } 
       }
      if(accountToUpdate.size()>0){
        update accountToUpdate;
       }
}       
      

Let me know your thoughts

Thanks,
Sucharita

Dave ShulmanDave Shulman
Thanks a ton for the advice.  The code looks a lot cleaner.  I think this above does something a little different then what im trying to have it do.  

Looking for the code to go through each account totaling the sales data for each distributor to find what the largest 2 distributors are for each account. 

So would the way to do that be something like add another layer of for loop.  So it would ook something like this?  I also have no idea how to get the total Amount for each distributor in there thats why that section of loop has no code in it
 
trigger DistributorIDs on Sales_data__c (after insert, after update) {
       private static double totalAmount = 0.0;
       private string distributor;
       Date AfterDate = Date.today().addDays(-45);
       ID LargestDistributor;
       ID SecondDistributor;
       Set<Id>accountIds = new Set<Id>();
       Set<Account>Distributors = new Set<Account>();
       for (Sales_data__c Sd: Trigger.new) {
           if(Sd.End_user__c!=null && Sd.End_user__c!=null){
                accountIds.add(Sd.End_user__c); // adding accounts to set
                Distributors.add(Sd.Distributor__r);  // adding distributors to set
           }
       } 

       List<Account> accountToUpdate = new List<Account>(); 
       List<Account> accList = new List<Account>([SELECT Id,Acct_Primary_Distributor__c,Acct_Secondary_Distributor__c,
                                                (SELECT Id,Distributor__c,Amount__c,Date__c FROM Sales_Data__r WHERE
                                                 Date__c >= :AfterDate) // used sub query on Sales Data
                                                 FROM Account WHERE Id IN : accountIds]);
       if(accList.size()>0){ 
          for(Account acc : accList){ //looping through every account to update in above list
             for(Sales_Data__c sd: acc.Sales_Data__r){  // looping thorugh every child of account
                 for(Account dist: Distributors){  // loop through distributors
                     totalAmount = totalAmount + sd.Amount__c; //total distributor amount
                      // use thet totals to figure out which distributor is largest for each account                    
                 }
            }
            acc.Acct_Primary_Distributor__c = LargestDistributor;   // adding largest distributors ID to parent account
            acc.Acct_Secondary_Distributor__c = SecondDistributor; // adding 2nd largest distributors ID to parent account
            accountToUpdate.add(acc);
          } 
       }
      if(accountToUpdate.size()>0){
        update accountToUpdate;
       }
}

 
SUCHARITA MONDALSUCHARITA MONDAL
Hey Dave,
You don't have to put third layer of loop as (line 24). Since you already have distributor list from subquery under line 17.
Just try to use that as sd.Distributor  (as your are doing for amount). One more thing, there must/should be two distrubutors/fields coming from Sales_Data as per line 29 and 30.

Note. Try to use one loop or max two  (remember the time complexity).

For total amount :- Analyse the loop (line 22 and line 23).  It says for every account , it'll process it's all sales area and once the inner loop closed then assign the total amount to Account.

for(Account acc : accList){ //looping through every account of above list
            for(Sales_Data__c sd : acc.Sales_Datas__r){  // looping thorugh every child of account
                if(sd.Amount__c !=null && sd.Distributor__c!=''){
                    totalAmount = totalAmount +sd.Amount__c; // summing up amount from every sales area
                  }
            }
           
            acc.Acct_Secondary_Distributor__c = totalAmount;  // here account will have sum of amount from it's all sales area.

Hope this helps.

Thanks,
Sucharita
This was selected as the best answer
Dave ShulmanDave Shulman
Thanks for the help SUCHARITA !  i think i got it working