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
learn_sfdclearn_sfdc 

Copying fields from Opportunity Product to Opportunity

I am trying to copy value from Opportunity product to Opportunity

Field is TotalPrice on Opportunity Product to RSF_CLD_CB_CommValue__c on Opportunity

below is the code i am using but im getting error


Error: Compile Error:
id,RSF_CLD_CB_CommValue__c from Opportunity)
^
ERROR at Row:1:Column:62
Didn't understand relationship 'Opportunity' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names. at line 5 column 51


 
trigger CommValue on OpportunityLineItem (after insert,after update) {

            List<Opportunity> lstOppUpdate = new List<Opportunity>();
                    
               List<OpportunityLineItem> lstOli = [select id,TotalPrice,(select id,RSF_CLD_CB_CommValue__c from Opportunity) 
                        from OpportunityLineItem where id in: trigger.newmap.keyset()];
                    
                
                       
                for(OpportunityLineItem oli : lstOli){
                
                if(oli.TotalPrice != NULL){

            for(Opportunity opp: oli.Opportunity__r){              

                opp.RSF_CLD_CB_CommValue__c += oli.TotalPrice;
                
                lstOppUpdate.add(opp); 
                }
        }

    }

    if(lstOppUpdate.size() > 0){

        update lstOppUpdate;

    }

 
pigginsbpigginsb
Hi, sj_dev.

It looks like you have the relationship in the wrong order. Opportunity is parent to the OpportunityLineItem, meaning that OpportunityLineItem has a lookup to the Opportunity.

I believe you'll want to collect the Opportunity Id values into a Set, then query for Opportunities while capturing their child OpportunityLineItems. Here's how I might approach this...
Set<Id> opportunityIdSet = new Set<Id>();

for (OpportunityLineItem each : trigger.new) {
    opportunityIdSet.add(each.OpportunityId);
}

// note that you can build the null total price check into the nested query for child OpportunityLineItems
List<Opportunity> affectedOpportunityList = [select Id, RSF_CLD_CB_CommValue__c, (select TotalPrice from OpportunityLineItems where TotalPrice != null) from Opportunity where Id in :opportunityIdSet];

for (Opportunity eachOppty : affectedOpportunityList) {
    eachOppty.RSF_CLD_CB_CommValue__c = 0; // assuming we can start with zero value before adding up TotalPrice from line items
    for (OpportunityLineItem eachLine : eachOppty.OpportunityLineItems) {
        eachOppty.RSF_CLD_CB_CommValue__c += eachLine.TotalPrice;
    }
}

update affectedOpportunityList;

What could be even more fun is using AggregateResult (https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_agg_fns.htm) to sum up TotalPrice from OpportunityLineItems directly in the query, without having to iterate over them.
AggregateResult[] lineItemResults = [SELECT SUM(TotalPrice) totalCommValue from OpportunityLineItem where Id in :trigger.new Group By OpportunityId];

List<Opportunity> affectedOpportunityList = new List<Opportunity>();

for (AggregateResult each : lineItemResults) {
    affectedOpportunityList.add(new Opportunity(Id = each.get('OpportunityId'), RSF_CLD_CB_CommValue__c = (Decimal)each.get('totalCommValue'));
}

update affectedOpportunityList;


Have you considered what should happen if/when an OpportunityLineItem is deleted? In this case, you'd probably want to fire an "after delete" trigger and reference trigger.old to capture the affected Opportunity Id values.

For that matter, you should probably consider firing a trigger "after undelete" on OpportunityLineItem too, because Salesforce also allows the undeleting of records.

I hope this helps. Please reach out if you think I might have missed something.

Glyn Anderson 3Glyn Anderson 3
Try this - it solves the problem of the child relationship query that should be a parent relationship query.

<pre>
trigger CommValue on OpportunityLineItem ( after insert, after update )
{
    List<Opportunity> lstOppUpdate = new List<Opportunity>();
    List<OpportunityLineItem> lstOli =
    [   SELECT  TotalPrice, Opportunity.Id, Opportunity.RSF_CLD_CB_CommValue__c
        FROM    OpportunityLineItem
        WHERE   Id IN :Trigger.new
    ];
    for ( OpportunityLineItem oli : lstOli )
    {
        if ( oli.TotalPrice == null ) continue;
        oli.Opportunity.RSF_CLD_CB_CommValue__c += oli.TotalPrice;
        lstOppUpdate.add( oli.Opportunity );
    }
    // there is no error to update an empty collection,
    // and it does not count against DML limits
    update lstOppUpdate;
}
</pre>
Glyn Anderson 3Glyn Anderson 3
Pigginsb is giving good advice if you want your opportunities to maintain the sum of the Total Price fields.  You really want a rollup summary, and the easiest way is to do that is with an aggregate query.  His example sums only the line items that are in the trigger, while you probably want to sum all the line items for any opportunities that are affected.  And to GROUP BY in an aggregate query, you must also SELECT that field.  Try this:

<pre>
trigger CommValue on OpportunityLineItem ( after insert, after update, after delete, after undelete )
{
    Set<Id> oppIds = new Set<Id>();
    for ( OpportunityLineItem oli : Trigger.isDelete ? Trigger.old : Trigger.new )
    {
        oppIds.add( oli.OpportunityId );
    }
    List<Opportunity> oppsToUpdate = new List<Opportunity>();
    for ( AggregateResult result :
        [   SELECT  SUM(TotalPrice) totalPrice, OpportunityId
            FROM    OpportunityLineItem
            WHERE   OpportunityId IN :oppIds
            GROUP BY OpportunityId
        ]
        )
    {
        oppsToUpdate.add
        (   new Opportunity
            (   Id = (Id) result.get( 'OpportunityId' )
            ,   RSF_CLD_CB_CommValue__c = (Decimal) result.get( 'totalPrice' )
            )
        );
    }
   update oppsToUpdate;
}
</pre>
learn_sfdclearn_sfdc
Hello @pigginsb / @ Glyn Anderson 3,

Thank you so much for rerplying, it helped me a lot.

 
Glyn Anderson 3Glyn Anderson 3
sj_dev,  You're very welcome.  Could you mark the question as "Solved" by selecting a post as the best answer?  Thanks!
Glyn Anderson 3Glyn Anderson 3
sj_dev,  We would really appreciate it if you would select one of the posts as the "Best Answer".  Thanks!