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
HARSHIL U PARIKHHARSHIL U PARIKH 

Opportunity Amount Roll-up trigger throwing an error: Error: Compile Error: A non foreign key field cannot be referenced in a path expression: Opportunities at line 26 column 31

Hello Developers!
I am trying to write trigger on opportunity which would sum the Amount field on Account.
This might be possiable via standard but I am trying to have similar trigger on different set of object and solving this would solve the other one as well.
Trigger:
trigger AmountRollup on Opportunity(After insert, After update, After delete, After undelete){
    
    Set<ID> accountIds = New Set<ID>();
    
    If(Trigger.IsInsert || Trigger.IsUpdate || Trigger.IsUndelete){
        For(Opportunity opp: Trigger.New){
            accountIds.add(opp.AccountID);
        }
    }
    If(Trigger.IsDelete){
        For(Opportunity opp: Trigger.Old){
            accountIds.add(opp.AccountID);
        }
    }
    
    List<Account> AccountListToUpdate = New List<Account>();
    Double AmountTotal = 0.00;
    
    
    For(Account a : [Select id, total_opportunity_amount__c, 
                                (Select id, Amount FROM opportunities) 
                                FROM Account WHERE ID = :accountIds])
    {
         For(Integer I = 0; I < a.opportunities.size(); I++)
         {
             AmountTotal += a.opportunities.Amount; // Here I am trying add all amount sum into AmountTotal
             a.total_opportunity_amount__c = AmountTotal;
             AccountListToUpdate.add(a);
         }
                                
    }
    try{
    update AccountListToUpdate;
    }
    catch(Exception E){
        system.debug('Error thrown is: ' + E.getMessage());
    }
}

Thank you for the help!
Best Answer chosen by HARSHIL U PARIKH
Lokesh KumarLokesh Kumar
Hi Govind,

Please use the below-working code and FYI Opportunity and Account are not directly related to each other.
 
trigger CalculateTotalOppAmount on Opportunity(After insert, After update, After delete, After undelete){
    
    Set<ID> accountIds = New Set<ID>();
    
    If(Trigger.IsInsert || Trigger.IsUpdate || Trigger.IsUndelete){
        For(Opportunity opp: Trigger.New){
            accountIds.add(opp.AccountID);
        }
    }
    If(Trigger.IsDelete){
        For(Opportunity opp: Trigger.Old){
            accountIds.add(opp.AccountID);
        }
    }
    
    List<Account> AccountListToUpdate = New List<Account>();
    For(Account a : [Select id, total_opportunity_amount__c, 
                                (Select id, Amount FROM opportunities) 
                                FROM Account WHERE ID IN :accountIds])
    {
        a.total_opportunity_amount__c = 0;
        for(Opportunity opp: a.Opportunities) {
               a.total_opportunity_amount__c += opp.Amount;
            }
        
           AccountListToUpdate.add(a);                 
    }
    try{
    update AccountListToUpdate;
    }
    catch(Exception E){
        system.debug('Error thrown is: ' + E.getMessage());
    }
}

Thanks
Lokesh

 

All Answers

Lokesh KumarLokesh Kumar
Hi Govind,

Please use the below-working code and FYI Opportunity and Account are not directly related to each other.
 
trigger CalculateTotalOppAmount on Opportunity(After insert, After update, After delete, After undelete){
    
    Set<ID> accountIds = New Set<ID>();
    
    If(Trigger.IsInsert || Trigger.IsUpdate || Trigger.IsUndelete){
        For(Opportunity opp: Trigger.New){
            accountIds.add(opp.AccountID);
        }
    }
    If(Trigger.IsDelete){
        For(Opportunity opp: Trigger.Old){
            accountIds.add(opp.AccountID);
        }
    }
    
    List<Account> AccountListToUpdate = New List<Account>();
    For(Account a : [Select id, total_opportunity_amount__c, 
                                (Select id, Amount FROM opportunities) 
                                FROM Account WHERE ID IN :accountIds])
    {
        a.total_opportunity_amount__c = 0;
        for(Opportunity opp: a.Opportunities) {
               a.total_opportunity_amount__c += opp.Amount;
            }
        
           AccountListToUpdate.add(a);                 
    }
    try{
    update AccountListToUpdate;
    }
    catch(Exception E){
        system.debug('Error thrown is: ' + E.getMessage());
    }
}

Thanks
Lokesh

 
This was selected as the best answer
HARSHIL U PARIKHHARSHIL U PARIKH
Hello Lokesh,
Your solution on looping amount field worked! Wow I don't thought that way! but however, I have a quick question, why we need to set a.total_opportunity_amount__c = 0; on line 21?
a.total_opportunity_amount__c = 0;
I mean.. on line 17  we are fetching all accounts and at that time  total_opportunity_amount__c for those account is null anyway right? If yes, then why we need to assign it to zero (e.g., a.total_opportunity_amount__c = 0) again.

BTW, whithout assigning a.total_opportunity_amount__c = 0, code acts like this:
If account is ABC, INC. and created opp_1 amount is 1000 then total_opportunity_amount__c becomes 1000;
But when I go back to that opp_1 and update it again then total_opportunity_amount__c becomes 2000.

I am soo badly want to know the roll of total_opportunity_amount__c = 0 here.
Thank you for the help!
Lokesh KumarLokesh Kumar
HI Govind,

By Default all the primitive(ID, Integer, Decimal etc) are null. FYI Try to execute this in the Developer console.
 
Integer i ;
if(i==0)
System.debug('true');
System.debug('false');

Thanks
Lokesh

 
HARSHIL U PARIKHHARSHIL U PARIKH
the system.debug('false') statement ran..
Thank you!