+ Start a Discussion
SFAdmin5SFAdmin5 

trigger to count child records

I've got a trigger that should just sum up the number of child records on a field in the parent.

 

Trigger below compiles but the aggregattion in the map doesn't seem to be working right.  Basically what should happen is that, anytime there is an account record  x of record type partner, any customer usage records created from accounts where the referrring partner is x should update the account field Active_Paying_M__c with that new add.

 

Example:

 

Partner = x

Sales account where referring partner is x = y 

Sales account where referring partner is x = r

customer usage record on y = z

customer usage record on r = p

 

If the above are true, the field value in Active_Paying_M__c on record x should be "2".  Trigger below keeps setting the value to 1 in testing.

 

Any ideas?  I suspect the issue is with the first map but not sure

 

trigger CustomerUsage on Customer_Usage__c (before insert, before update, after insert, after update, after delete) {

// ------------------------------------------------------
//test
// ------------------------------------------------------

if ((Trigger.isInsert || Trigger.isUpdate) && Trigger.isAfter) {    
     
set<Id> AccountIds = new set<Id>();
 
  if(trigger.isInsert || trigger.isUpdate){
    for(Customer_Usage__c p : trigger.new){
      AccountIds.add(p.Account__c);
    }
  }
 
  map<Id,Double> AccountMap = new map <Id,Double>();
 
  for(AggregateResult  q : [select Account__c,sum(FPID_Value__c)
    from Customer_Usage__c where Account__c IN :AccountIds group by Account__c ]){
      AccountMap.put(
      
      (ID)q.get('Account__c'),
      (Double)q.get('expr0')
      
      
      );
  }
 
//stage 1 - find the lookup records id's
List<String> tempLst=new List<String>();
Map<String, String> accountid_partner_map=new Map<String, String>();

for(Account a : [Select Id, Referring_Partner__c
        from Account
        where Id IN :AccountIds]){
    tempLst.add(a.Referring_Partner__c);
    accountid_partner_map.put(a.Referring_Partner__c, a.id);
}

//stage 2 - retreive the lookup records + update their dates.
List <Account> AccountsToUpdate = new List <Account>();
 
  for(Account a : [Select Id,Referring_Partner__c,Partner_Most_Recent_Gross_Add__c from Account where Id IN :tempLst]){
    Double FPID = AccountMap.get(accountid_partner_map.get(a.Id));
     
     a.Active_Paying_M__c = FPID ;
     
     
    
     AccountsToUpdate.add(a);
 
  update AccountsToUpdate;
 
  }
  
}
}

 

 

 

dmchengdmcheng
You are not summing up the values of the child accounts and assigning to the parent - instead, you are just assigning each child account's value to the parent field.
 
Example:  Account C1 has two usage records and Account C2 has three usage records, and both accounts are linked to Account P1.  You have not added the C1 and C2 results - you are only assigning C1 to P1.Active_Paying_M__c, and then you assign C2 to P2.Active_Paying_M__c.
 
This is not easy to solve.  If you had the Referral Account ID in the Customer usage record, you could aggregate on that.  However, GROUP BY doesn't support formula fields, and you can't use __r relationships either.
 
I think you have two choices:
1.  Write account triggers to populate the customer usage records with the referring account, then in this trigger aggregate on the referring account.
 
2.  Build a class to hold the referral account ID and calculation total, create a map of records of this class,  loop through your child account aggregate results and add to the specific referral account total record using the lookup map that you already have.
dmchengdmcheng

One unrelated item - in stage 2, your update statement should be outside the for loop, but this is not the root cause.

SFAdmin5SFAdmin5

Thanks a lot.  Yeah so basically I am taking an alternative route here.  Because this trigger is essentially just counting account records that belong to a given account record through a lookup relationship, I'm going with an update to my account trigger and summing up there, based on field values in qualifying records that should be counted.  Works fine this way.  Only way this will not work is if the underlying fields in my code do not hold accurate values, but this would be due to either mistakes in existing code that someone should fix, or user error.  Either way I think this is the best way to go.  Thanks a lot.