+ Start a Discussion
Patrick Marks 2Patrick Marks 2 

Please help- how to calculate total value of related Opportunities in custom Opportunity field

Hi everyone-I have an interesting business case, so will try to keep this as concise as possible:

We have a business unit with 2 Opportunity record types, called Firm (parent opportunity) and Fund (child opportunity). I created a lookup relationship on the Opportunity object that also looks up to Opportunities. Opportunities of the Fund record type (child) are required to have a value in the lookup field that points to the parent opportunity (Firm).

Here is what I'm trying to solve for: we have a custom Currency field on the Firm record type called Total Contract Value. What I am trying to do is write a trigger that will calculate the sum of all Closed Won Fund opportunity amounts (children), plus the amount of the Firm opportunity (parent), assuming it is Closed Won as well. Would love some direction on what this trigger would look like. Thanks in advance!
Leo10Leo10
HI, 
you can refer this link,
https://blog.jeffdouglas.com/2009/07/30/roll-up-summary-fields-with-lookup-relationships-part-1/
I believe that you will get an idea about making roll-up summary field using triggers,  

Warm regards,
Nabeel
Raj VakatiRaj Vakati
You can use declarative rollup summary tool to do this work 

https://github.com/afawcett/declarative-lookup-rollup-summaries


Rollup information between Lookup relationships not previously possible without writing Apex Triggers
Define rollups using standard UI declaratively, no coding required
Define filter criteria on rollups for example Rollup Amount on Opportunity onto Account for Closed Won
Supports Realtime, Scheduled and Developer API modes
Open source, available in code and managed package form.
Managed package has passed Salesforce Security Review and is Aloha enabled
NEW Supports Custom Metadata, rollups can be included in Change Sets and Packages for easier deployment
Shruti SShruti S

+ 1 Raj V
Tried this using the free app 'Declarative Rollup Summaries' and it worked like a charm. Here are few screenshots.

User-added image

Take a look at the Roll Up Summary Config -

User-added image

Glyn Anderson (Slalom)Glyn Anderson (Slalom)
Patrick,  The idea of the trigger below is that when Funds are inserted/updated, they will update their parent Firms.  When Firms are updated, they will compute their Total Contract Value.  You could create efficiencies by testing whether a Fund's Amount or StageName has changed.  This version updates everything every time.  One known bug -- if you update a Fund and it's parent Firm in the same transaction, you'll get an error (so don't do that ;-) ).

<pre>
trigger RollupFundsToFirms on Opportunity ( after insert, before update, after update )
{
    if ( Trigger.isBefore )
    {
        //  rollup CW Fund Amounts for all Firms in the trigger
        Map<Id,Double> totalFundValues = new Map<Id,Double>();
        for ( AggregateResult result :
            [   SELECT  SUM(Amount) value, Firm__c
                FROM    Opportunity
                WHERE   (   RecordType.DeveloperName = 'Fund'
                        AND Firm__c IN :Trigger.new
                        AND StageName = 'Closed Won'
                        )
                GROUP BY Firm__c
            ]
            )
        {
            totalFundValues.put
            (   (Id) result.get( 'Firm__c' )
            ,   (Double) result.get( 'value' )
            );
        }

        //  compute Total Contract Values
        for ( Opportunity opportunity : Trigger.new )
        {
            opportunity.Total_Contract_Value =
            (   (   opportunity.StageName == 'Closed Won'
                ?   opportunity.Amount
                :   0
                )
            +   (   totalFundValues.containsKey( opportunity.Id )
                ?   totalFundValues.get( opportunity.Id )
                :   0
                )
            );
        }
    }

    if ( Trigger.isAfter )
    {
        //  find all the Firms referenced by Funds in the trigger
        Map<Id,Opportunity> firmsToUpdate = new Map<Id,Opportunity>();
        for ( Opportunity opportunity : Trigger.new )
        {
            if  (   opportunity.Firm__c != null
                &&  !firmsToUpdate.containsKey( opportunity.Firm__c )
                )
            {
                firmsToUpdate.put
                (   opportunity.Firm__c
                ,   new Opportunity( Id = firmId )
                );
            }
        }

        //  update the Firms
        update firmsToUpdate.values();
    }
}
</pre>