function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
janeisaacjaneisaac 

How do I create a RUS on child opportunities? (linked by a lookup field to parent opp)

I am looking for some help to create a field on the Opportunity page which sums the amount of Child Opportunities. The child opp has a lookup to the Parent. It does not appear to let me use a Roll-up summary formula to do this so I am looking for help on a way to do this.

ericmonteericmonte

So in your child object are you using a Lookup or a Master Detail Relationship?


Remember, Roll Up Summary only works with Master Detail Relationship.

 

For example: Custom_Object__c has a field Master Detail to Opportunity. Once you done this then you can do Roll up Summary from the Opportunity level.

janeisaacjaneisaac
Eric, Since an Opportunity cannot be in a Master-Detail relationship with another opportunity, I realize that I cannot use a RUS so I was asking for alternative ideas.

I am expecting that it will take a little code and if anyone had some source that they could point me to to help me.

Sorry if I was not clear.
ericmonteericmonte

Ahh, yes sorry i didn't catch that you dida  self look up relationship to Opportunities.

 

What you can do is write a trigger class for the record, where you do an aggregate functions to get the sum of the records, then update Opportunities.


You might want to look in to jeff douglas blog and how he used aggregate functions:

http://blog.jeffdouglas.com/2010/04/12/using-aggregateresult-in-salesforce-com-soql/

 

 

Let me know if this helps, if not I can try to write a quick code snippet for you.

janeisaacjaneisaac

Thanks Eric,

The coding is a little beyond me but thanks for the pointer.

 

This Aggregate function looks like it should do it but I could use some help. On the Parent Opp, I want to sum the amounts from any Child Opp unless the Child Opp is in stage either Closed Lost or Dropped. If you can take a stab, I would really appreciate it.

 

Jane

ericmonteericmonte
I have something in my sanbox that I have done a while back let me see if i can retool it for your needs. Give me a couple hours and i'll get back to you u.
janeisaacjaneisaac
Thanks!
ericmonteericmonte

So apparently, this was a little harder than i though, but here is a sample of what i did.

 

Basically what the trigger does is to update the Parent after the Opportunity get's update or created. I didnt account for opportunity for being deleted on undeleted. Also this is no longer just a Formula and Validation Rules but rather more of APEX and a lot of development still needs to be looked at including testing and so forth. Besides that hope this helps you.

 

trigger trgOpportunity on Opportunity (after insert, after update) {
   
    List <Opportunity> oppList = [select id, Child_Oppty__c from Opportunity where id =: trigger.new and Child_Oppty__c != null Limit 1];
    if(oppList.size() > 0){
    Id oppId = [select Child_Oppty__c from Opportunity where id =: trigger.new and Child_Oppty__c != null LIMIT 1][0].Child_Oppty__c;
    Decimal intSum;
    system.debug('what is my id ===' +oppId);
   
        AggregateResult[] groupedResults = [SELECT SUM(Amount) amt
        FROM Opportunity where Child_Oppty__c =: oppId and StageName != "Exclude Stages"];
       
        for (AggregateResult a : groupedResults){
           
            intSum = (Decimal)a.get('amt');
        }
       
        Opportunity opp = [select id, SumRoll__c from Opportunity where id =: oppId];
        opp.SumRoll__c = intSum;
        update opp;
        system.debug('sum amount *****' +intSum);
    }
}

SF AdminsSF Admins
Hi Eric and Jane,

I am looking for help on Rollup summary on self lookup relationship and I came across this page. I am beginner in apex programming and I was able to implement the above solution. Solution is working fine but for my use case it is updating only first line.
Sales Order Header and Sales Order Lines have Master detail relationship.
Sales Order header has lookup relationship to Sales Order header
Sales Order Lines have lookup relationship to Sales Order lines

Use Case: Parent Sales Order header has 3 sales order lines. When split Child Sales Order has three lines. I want the rollup of all the lines onto respective parent Sales Order lines. Currently the above solution is working for first line.

Any help or refernece would be highly appreciated. Thank you.

Krishna