+ Start a Discussion
Sindhu NagabhushanSindhu Nagabhushan 

Trigger to fetch records in one quarter

Hi,
Please help me with this requirement

I have 'score' field on objectA and this object has lookup relationship to account.
I want to take average of 'score' field on all objectA records created in current quarter and stamp that value on account.


Thanks in advance
RamuRamu (Salesforce Developers) 
Hi Sindhu, Please review the below article on how to create a rollup summary field on a lookup relation and try out for your requirement

http://www.sundoginteractive.com/sunblog/posts/rollup-summary-with-a-lookup-field-salesforce
GlynAGlynA
SOQL has date literals that you can use to filter your query results.  For your requirement, you could use something like this in an Account before update trigger:

<pre>
for ( AggregateResult result :
    [   SELECT  AVG(Score__c) average, Account__c
        FROM    ObjectA__c
        WHERE   (   CreatedDate = THIS_QUARTER
                AND Account__c IN Trigger.new
                )
        GROUP BY Account__c
    ]
    )
{
    Trigger.newMap.get( (Id) result.Account__c ).averageScore__c = (Decimal) result.average;
}
</pre>

This code is not tested.  I just typed it in here.  Let me know if you have any issues with it.
Sindhu NagabhushanSindhu Nagabhushan
Thanks Glyna,

I tried this code

trigger update_fb on feedback__c(after insert,after update)
{
    list<aggregateresult> test;
    for(feedback__c fb:trigger.new)
    {
        test=[select avg(Average_Score__c)aver from feedback__c where createddate=this_quarter and account__c=:fb.account__c];
    system.debug(test);
        string str='' +test[0].get('aver');
        fb.account__r.average__c=decimal.valueOf(str);
        system.debug(fb.account__r.average__c);
    }
}

but i get this error

System.NullPointerException: Attempt to de-reference a null object: Trigger.update_fb: line 9, column 1

Please help me with this error, i do not know what exactly i am doing wrong

 
GlynAGlynA
The problem is with the expression: fb.account__r.average__c.  The feedback__c record, fb, is coming into the trigger.  It will have all of the feedback__c fields in it, but it won't have any fields from any parent objects (e.g. account__r).  The field account__c will be populated, but account__r will be null.

Have you tried the code that I posted?  It won't have this problem, and it is bulk-safe, unlike your code.  You should put queries inside for loops.

Note that my code will be in the Account before update trigger, not the Feedback trigger.  You can also put code in the Feedback after trigger - something like this:

<pre>
Map<Id,Account> accountsToUpdate = new Map<Id,Account>();
for ( Feedback__c feedback : Trigger.isDelete ? Trigger.old : Trigger.new )
{
    if ( !accountsToUpdate.containsKey( feedback.Account__c ) )
    {
        accountsToUpdate.put( feedback.Account__c, new Account( Id = feedback.Account__c ) );
    }
}
update accountsToUpdate.values();
</pre>

This will cause the related accounts to update themselves (in the Account trigger) any time a Feedback item is inserted, updated, deleted, or undeleted (assuming you trigger on all these events).

Let me know if this solves the problem.
GlynAGlynA
I meant to say, "You should NOT put queries into for loops."  Sorry for the typo.