4

i have 3 objects: invoice,invoice line item & standard Account.

relationships:

  • invoice & invoice line item--->master-detail.

  • Account & invoice--->lookup.

On the invoice object i have one Rollup summary field called total price(sum of all prices on invoice line item).

On account i have 1 field called Account_balance

On the save event of a invoice line item i want to update Account_balance field with rollup summary value of invoice object.

Is this possible with a trigger ?

2
  • Any special reason why the Account - Invoice can't be a Master-Detail too?
    – eyescream
    Dec 24, 2013 at 14:07
  • 1
    By the way Jenish, welcome to the Salesforce StackExchange! If my answer below helped you solve your problem, can you please mark it as the correct answer? A feature of this site (and something we are measured on) is our answer rate. We are trying to grow the answer rate to get out of beta and your accept can help! Thanks and let me know if you need help with any of the site's features. Dec 24, 2013 at 16:35

1 Answer 1

7

This is definitely possible with a trigger. To give you some pseudo code, I would write your trigger on the Invoice__c object. It will handle any updates of the Invoice_Line_Item__c object because of the rollup summary. You will want to have something like:

trigger InvoiceTrigger on Invoice__c (after insert, after update) {
    if(Trigger.isAfter && (Trigger.isInsert || Trigger.isUpdate)){
        InvoiceTriggerHandler.updateAccountBalance(Trigger.newMap);
    }
}

public class InvoiceTriggerHandler{
    public static void updateAccountBalance(Map<Id, Invoice__c> invoices){
        AggregateResult[] results = [
            SELECT 
                Account__c, SUM(Total_Price__c)
            FROM
                Invoice__c 
            WHERE 
                Id IN :invoices.keySet 
            GROUP BY 
                Account__c
        ];
        Set<Account> accountsToUpdate = new Set<Account>();
        for(AggregateResult result:results){
            accountsToUpdate.add(new Account(
                Id = result.get('Account__c'), 
                Account_Balance__c = result.get('expr0')
            );
        }
        update accountsToUpdate;
    }
}

You will have to clean that up and write your own tests, but that is the general idea. Every time an Invoice_Line_Item__c is updated and it changes the rollup summary field, this trigger will fire to update the account. There is some additional logic you may want to add, such as checking the old value of the Total_Price__c field with the new value and only running through Accounts who have had Invoice__c changes rather than all of them. There may be some other ways to tweak this as well, I just quickly wrote this up. Keep in mind this is not production ready by any means, but I hope this points you in the right direction.

The approach above utilizes aggregate functions and setting Id fields for update.


EDIT: I originally had the following code, which should still work, but the above is more optimal.

trigger InvoiceTrigger on Invoice__c (after insert, after update) {
    if(Trigger.isAfter && (Trigger.isInsert || Trigger.isUpdate)){
        InvoiceTriggerHandler.updateAccountBalance(Trigger.new);
    }
}

public class InvoiceTriggerHandler{
    public static void updateAccountBalance(List<Invoice__c> invoices){
        Set<Id> accountIds = new Set<Id>();
        for(Invoice__c invoice:invoices){
            accountIds.add(invoice.Account__c);
        }
        List<Account> accounts = [SELECT Id, Account_Balance__c, (SELECT Id, Total_Price__c FROM Invoices__r) FROM Account WHERE Id IN :accountIds];
        for(Account acc:accounts){
            acc.Account_Balance__c = 0;
            for(Invoice__c invoice:acc.Invoices__r){
                acc.Account_Balance__c += invoice.Total_Price__c;
            }
        }
        update accounts;
    }
}
7
  • 2
    Any reason why you chose not to use SUM() and group by queries ? Dec 24, 2013 at 14:10
  • It is early? Haha, no not really to be honest. I just didn't think of it. I don't use aggregate queries enough and it kind of just slipped my mind. I just wanted to throw something together. With that said, that is a really good point and that would most likely be the better way to go. Dec 24, 2013 at 14:17
  • 1
    I updated the code above to use aggregate queries as you suggested. It is a much better approach. I think the code I wrote will compile and do what they want, but I'm not exactly sure (I just wrote it all on this site). Dec 24, 2013 at 14:34
  • 2
    Aggregates are neat but need more attention if company uses (dated) currency rates. If account is in USD but the organization's default currency is EUR you need to multiply the result by the appropriate CurrencyType record... The convertCurrency() call (salesforce.com/us/developer/docs/soql_sosl/Content/…) won't work.
    – eyescream
    Dec 24, 2013 at 14:46
  • 1
    To avoid errors in the calculations during periods of high user concurrency you will likely need to use FOR UPDATE on your Account select, otherwise the balance cannot be accurate. Dec 24, 2013 at 15:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .