Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

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 ?

share|improve this question
    
Any special reason why the Account - Invoice can't be a Master-Detail too? –  eyescream Dec 24 '13 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. –  Jesse Altman Dec 24 '13 at 16:35

1 Answer 1

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;
    }
}
share|improve this answer
2  
Any reason why you chose not to use SUM() and group by queries ? –  Samuel De Rycke Dec 24 '13 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. –  Jesse Altman Dec 24 '13 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). –  Jesse Altman Dec 24 '13 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 '13 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. –  Andrew Fawcett Dec 24 '13 at 15:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.