+ Start a Discussion
KimBaileyKimBailey 

How Do I Sum records from another custom object?


Hi,

I'm a PHP and Wordpress developer, new to SF.

I created a new custom object, called "referrals".

Every record in this object has a couple of fields and also an "email" field.

In my accounts object, I created a new field - "number of referrals".

In this field, I want to get the total number of referrals that has the same email address as the account.

What's the best way to do that?

Thanks!
Brian FordBrian Ford
Does the Referrals object have a master-detail or lookup relationship with the Account? If it's a master-detail relationship, you can create a rollup summary field on the account and select the Referral fields you need to summarize.

If it's a lookup relationship, you'll need a trigger to summarize the fields. You'll find some sample triggers for this through a quick Google search.
James LoghryJames Loghry
Kim,  there are a couple of ways you could do this, but the best way you could do this declaritively would be to:

1)  Create a relationship between referrals and accounts. Depending on your model, this may be a 1 to many relationship, or a many to many relationship.  For the latter, you would look into setting up what's called a "Junction object".  Your relationship between Account and either the Referral or Referral Junction object would e a Master Detail.  This would allow you to use a "Roll up Summary field" to count the number of referrals for that given account.  This may or may not involve a trigger to establish the referral -> account relationship, depending on how your referrals are created.

2) Another option is to write a trigger on the Referrals object.  Any time a referral is created, or the email address is updated, then you would query for accounts with the same email.  You're going to run into a few issues here.   Mainly, you'll have to support Referral deletes as well if you want an accurate representation on the account.  For instance, if a referral is deleted, you'll have to decrease the Number_Of_Referral fields on the Account as well.  Additionally, using the emailAddress is more of a logical relationship than defining a Master Detail or Lookup from Referral to Account, so you'll have to get creative with Maps in your trigger, for one.

trigger referralTrigger on Referral__c(after insert, after update){

    List<String> emailAddresses = new List<String>();
    for(Referral__c r : Trigger.new){
        
        //Check to see if insert, or if email has been updated..
        if(Trigger.oldMap == null || Trigger.oldMap.get(r.Id).Email__c != r.Email__c){
            emailAddresses.add(r.Email__c);
        }
    }

    List<AggregateResult> arList = [Select Email__c,Count(Id) From Referral__c Where Email__c in :emailAddresses Group By Email__c];

    List<Account> accounts = [Select Number_Of_Referrals,Email__c From Account Where Email__c in : emailAddresses];
    for(Account acct : accounts){
        for(AggregateResult ar : arList){
            //expr0 refers to the first expression in an aggregateresult, expr1 is next, etc.
            if(acct.Email__c == ar.get('expr0')){
                acct.Number_Of_Referrals__c = ar.get('expr1');
            }
        }
    }
   update accounts;
}

Above is a simple example of how your trigger, using the second option *might* work.  Note, I'm not handling the deletion case above, so you'll have to work that out too.  

For more on working with Group By / Aggregate results, see this: http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_agg_fns.htm