You need to sign in to do that
Don't have an account?
Dave 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; }
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
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
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
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