+ Start a Discussion
AJ Kreuer 9AJ Kreuer 9 

Trigger to get Account Record's Total Order Amount

Hey all, 

I'm having a bit of trouble trying to figure out where to start on writing a trigger that would display the sum of all orders for a given Account on the Account page.  I've created a custom currency field on the Account record "Order_Grand_Total__c" but actually populating that field via apex trigger is where I'm stuck.  I thought I'd be able to do a roll-up summary field but it appears that only works with the Opportunities object and not the standard Order object.

Any help in the right direction is much appreciated!!
Best Answer chosen by AJ Kreuer 9
Derhyk Doggett -Derhyk Doggett -

Hi AJ,
Took some thinking. I know why now. Before the insert the Account does not have an ID yet. So the AccountID filter used in the query is null, thus throwing the error.

List<Order> orderList = [SELECT id, TotalAmount, AccountId FROM ORDER WHERE AccountId IN: Trigger.newMap.keySet()];

I suggest changing the trigger to before update (remove the before insert), and modifying the test class to perform an update on the account after your account creation and order creation.

-derhyk
 

All Answers

Derhyk Doggett -Derhyk Doggett -
Interesting that rollup summaries won't work. If that is possible, I would suggest that route as much simpler and less error prone.
If you must go down the route of a trigger, it would look something like this:
trigger SumOrders on Account (before insert, before update) {

    List<Order> orderList = [SELECT id, TotalAmount, AccountId FROM ORDER WHERE AccountId IN: Trigger.newMap.keySet()];

    for (Account a : Trigger.new)
    {
        for (Order o : orderList)
        {
            if (a.id == o.AccountId) //Add some additional parameters here to filter what orders you want to include. ie Active, etc
            {
                a.Order_Grand_Total__c += o.TotalAmount;
            }
        }
    }
}

(Note there may be some syntax errors above, I did not check against compiler.

Hope that helps

-derhyk
 
AJ Kreuer 9AJ Kreuer 9
Thanks Derhyk, I'm going to try that out... I also tried the Lookup Rollup Summaries option: http://screencast.com/t/GjbqPJqxRJ

However, that doesn't seem to calculate in realtime as the settings would suggest, and doesn't populate the field at all until an Account has atleast 2 orders: http://screencast.com/t/cc3xokJFT

So I'm thinking a Trigger is the next best option, I'll try your code out and see how it goes, thanks for your reply!
AJ Kreuer 9AJ Kreuer 9
Hey Derhyk,

I tried your code example and didn't have any errors, however, it's not actually updating the Order_Grand_Total__c when I add a new order.  Any ideas?

Thanks again!
Derhyk Doggett -Derhyk Doggett -

Hi AJ,
Can you share a screenshot of Account with the Oder Related list shown?
-derhyk

AJ Kreuer 9AJ Kreuer 9
Hey Derhyk,

Yes, here is a screenshot of the Account + the order I created for that Account: http://screencast.com/t/T6JIXA3f

I had to edit the trigger to be (after insert, after update) because it wouldn't let me create a new Account with those being set as before.  I'm also wondering if the trigger needs to be on the Order object instead of the Account object?

And then for my test class, I used this (not sure if this is correct, I passed tests with 57% code coverage, 4/7)
@IsTest
public class SumOrders {

    static testmethod void insertAccount() {
    
        Account a = new Account();
       
        a.Name = 'AJ Kreuer';
        a.Last_Name__c = 'Kreuer';
        a.Email__c = 'ajkreuer@mydomain.com'; 
       
        insert a;
   
    }

}

Again, I'm thinking maybe this should all be after insert, after update on the Order object and then my test class would be to insert a new order but I could be wrong?

Thanks!
Derhyk Doggett -Derhyk Doggett -

Hi AJ,
Thanks for the screenshot. I don't believe you'll want it to execute after, as it will not let you write to the Account. After is typically if you're updating other object types after you've entered information on the object the trigger is running on.
To avoid the error on the before you can remove the before insert and include some validation to only run if orders are found.

ie: update the if statement to execute when the list has entries

if (a.id == o.AccountId && orderList.size() > 0)

Good start on the test class. To increase coverage, you will want to include creating some orders associated with the account.

-derhyk
AJ Kreuer 9AJ Kreuer 9
Okay I've changed it back to before instert, before update, along with your new if statement... here's my new test class:
 
@IsTest
public class SumOrders {

    static testmethod void insertAccount() {
    
        Account a = new Account();
       
        a.Name = 'AJ Kreuer2';
        a.Last_Name__c = 'Kreuer2';
        a.Email__c = 'ajkreuer2@mydomain.com'; 
       
        insert a;      
    }
    
    static testmethod void insertOrder() {
    
    Order o = new Order();
       
        o.AccountId = '0013B000004Llt6';
        o.Order_ID__c = '0001';
        o.Status = 'Draft';
        o.EffectiveDate = Date.Today();
 
        insert o;
    }

}
This passes the order insert, but I get an error trying to inser the Account: 
 
System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, SumOrders: execution of BeforeInsert

caused by: System.NullPointerException: Attempt to de-reference a null object

Trigger.SumOrders: line 3, column 1: []

Stack trace: Class.SumOrders.insertAccount: line 12, column 1

Not sure where to go from here.  Thank you again for your help, I really appreciate it!!
 
Derhyk Doggett -Derhyk Doggett -

Hi AJ,
Took some thinking. I know why now. Before the insert the Account does not have an ID yet. So the AccountID filter used in the query is null, thus throwing the error.

List<Order> orderList = [SELECT id, TotalAmount, AccountId FROM ORDER WHERE AccountId IN: Trigger.newMap.keySet()];

I suggest changing the trigger to before update (remove the before insert), and modifying the test class to perform an update on the account after your account creation and order creation.

-derhyk
 
This was selected as the best answer
mounika reddy 87mounika reddy 87
trigger trghandler on Opportunity (before insert) {

for (Opportunity opp:trigger.new)

{
   if (opp.Amount =='' || opp.Amount==null)
   
   {
      opp.addError('please Enter the value into Amount');
      
   }
   
   }

}