+ Start a Discussion
Thomas CailletThomas Caillet 

Update account field when opportunity lookup is update

Hello,

I'm trying to create a trigger or a process builder to update an account status field when an opportunity populated on lookup field in account changed.
In fact I have a lookup field to opportunity on my account name's "Main_opportunity__c" and I want to update the account status when this "Main opportunity" is closed won.

Thanks to your answer
Thomas
Best Answer chosen by Thomas Caillet
Sagar LakhaniSagar Lakhani
Hi Thomas,

Please Add Below Apex Class and trigger in your org and change the field name according to your fields Api Name

Apex class

public class OpportunityTriggerHandler{  
    public static void updateRelatedAccStatusOnOpp(List<Opportunity> listOpportunity){
        List<Account> listAccToUpdate = new List<Account>();
        for(Opportunity objOpp : listOpportunity){
            for(Account objAcc : [SELECT Id,Name,Status from Account Where TestNamespace00__Main_Opportunity__c =: objOpp.Id]){
                if((objAcc != null) && (objOpp.StageName == 'Closed won')){
                    objAcc.Status = 'AccStatus';    
                    listAccToUpdate.add(objAcc); 
                }
            }
            update listAccToUpdate;
        }
    }
}



Apex Trigger on Opportunity which execute method of 'OpportunityTriggerHandler' class

trigger OpportunityTrigger on Opportunity (AFTER INSERT,AFTER UPDATE) { 
   if(Trigger.isAfter && Trigger.isUpdate){
       OpportunityTriggerHandler.updateRelatedAccStatusOnOpp(Trigger.New);
   }
}

if you need any other help then Contact me on my Skype.

Thanks,
Sagar Lakhani (Skype : Sagar.lakhani3)

 

All Answers

Sagar LakhaniSagar Lakhani
Hi Thomas,

Please Add Below Apex Class and trigger in your org and change the field name according to your fields Api Name

Apex class

public class OpportunityTriggerHandler{  
    public static void updateRelatedAccStatusOnOpp(List<Opportunity> listOpportunity){
        List<Account> listAccToUpdate = new List<Account>();
        for(Opportunity objOpp : listOpportunity){
            for(Account objAcc : [SELECT Id,Name,Status from Account Where TestNamespace00__Main_Opportunity__c =: objOpp.Id]){
                if((objAcc != null) && (objOpp.StageName == 'Closed won')){
                    objAcc.Status = 'AccStatus';    
                    listAccToUpdate.add(objAcc); 
                }
            }
            update listAccToUpdate;
        }
    }
}



Apex Trigger on Opportunity which execute method of 'OpportunityTriggerHandler' class

trigger OpportunityTrigger on Opportunity (AFTER INSERT,AFTER UPDATE) { 
   if(Trigger.isAfter && Trigger.isUpdate){
       OpportunityTriggerHandler.updateRelatedAccStatusOnOpp(Trigger.New);
   }
}

if you need any other help then Contact me on my Skype.

Thanks,
Sagar Lakhani (Skype : Sagar.lakhani3)

 
This was selected as the best answer
Thomas CailletThomas Caillet
Thanks very much Sagar.
Thomas
Thomas CailletThomas Caillet
Sagar,

I tried to create a test for this trigger :

@isTest
private class TestUpdateMainOpp {

    @isTest static void TestUpdateMainOpp () {
        
        account a1 = new account(
                        Name ='Test',
                        Account_Type_MAIN__c ='Prospect', 
                        Availpro_Status__c = 'In progress', 
                        Salesperson__c = '005w0000003eWsO',
                        Main_Account_Contact__c = '0032500000YS4TK',
                        Account_Type__c = 'Reseller');
                
        insert a1;
        
        Opportunity opp = new Opportunity(Name='Opportunity TEST',
                                       StageName='Prospecting',
                                       AccountId=a1.id,
                                       CloseDate=System.today().addMonths(1)
                                       );
        insert opp;
                
        // Perform test
        Test.startTest(); 
        
        a1.Main_Opportunity__c = opp.id;
        update a1;
        
        opp.StageName='Closed Won';
        opp.SyncedQuoteId=QUOT.Id;
        update opp;
        
        Test.stopTest();
    }
    
}

Bu I have an error caused by the after update and after insert.
Do you have any idea to fixe that please ?

Thanks
Thomas
Sagar LakhaniSagar Lakhani
Hi Thomas,

Please Try below code of Test Class & you get 100% code coverage.......


@isTest
public class OpportunityTriggerHandlerTest {
    static testMethod void updateRelatedAccStatusOnOppTest(){
        Opportunity objOpp = new Opportunity();
        objOpp.Name = 'TestOpp';
        objOpp.StageName = 'Closed won';
        objOpp.CloseDate = date.parse('10/12/2016');
        insert objOpp;
        
        Account objAcc = new Account();
        objAcc.Name = 'TestAcc';
        objAcc.TestNamespace00__Main_Opportunity__c = objOpp.ID;
        objAcc.Type = 'Other';
        insert objAcc;
        
        List<Opportunity> listOpportunity = new List<Opportunity>();
        listOpportunity.add(objOpp);
        
        OpportunityTriggerHandler.updateRelatedAccStatusOnOpp(listOpportunity);
    }
}

Thanks,
Sagar Lakhani (Skype : Sagar.lakhani3)

 
Thomas CailletThomas Caillet
Thanks again Sagar it's works. I have 100 % code coverage on the public class.
But just 50% on my trigger :

User-added image

Do you have an idea ?
Thanks
Thomas
Sagar LakhaniSagar Lakhani
Please change trigger code with below code...
trigger OpportunityTrigger on Opportunity (After Insert,After Update,Before Insert,Before Update) {
	if(Trigger.isAfter && (Trigger.isInsert || Trigger.isUpdate)){
	       OpportunityTriggerHandler.updateRelatedAccStatusOnOpp(Trigger.New);
	   }
}
Thanks,
Sagar Lakhani (Skype : Sagar.Lakhani3)
 
Thomas CailletThomas Caillet
Thanks that's works Sagar.
Sorry just one last question : if I want to update a checkbox on opportunity object, what can I do ?

Thanks for your time.
Thomas
Sagar LakhaniSagar Lakhani
When Opportunity goes to closed won then you have to update checkbox value?
Thomas CailletThomas Caillet
In the same time when I update account status
Sagar LakhaniSagar Lakhani

Please change trigger code to below otherwise you get error

if(Trigger.isBefore && (Trigger.isInsert || Trigger.isUpdate)){
       OpportunityTriggerHandler.updateRelatedAccStatusOnOpp(Trigger.New);
   }

and for checkbox update, In my triggerhandlerclass code

Add

objOpp.checkbox_api_Name = True or False;

below 

objAcc.Status = 'AccStatus'; 

Thomas CailletThomas Caillet
That doesn't work because we update just objAcc : update listAccToUpdate;
Not Opportunity object
Sagar LakhaniSagar Lakhani
We don't need to update opportunity object here. Here we can set the value of checkbox and its working fine please create test record and check this and if it doesn't work then let me know.
Thomas CailletThomas Caillet
Unfortunately that's doesn't work :/
Sagar LakhaniSagar Lakhani
It works for me. can u send the code please.
Thomas CailletThomas Caillet
Yes course.

Here is the trigger :

trigger OpportunityTrigger on Opportunity (After Insert,After Update,Before Insert,Before Update) {
    if(Trigger.isBefore && (Trigger.isInsert || Trigger.isUpdate)){
       OpportunityTriggerHandler.updateRelatedAccStatusOnOpp(Trigger.New);
   }
}

And public class :

public class OpportunityTriggerHandler{  
    public static void updateRelatedAccStatusOnOpp(List<Opportunity> listOpportunity){
        List<Account> listAccToUpdate = new List<Account>();
        for(Opportunity objOpp : [SELECT Id,StageName,First_Closed_Opp__c from Opportunity]){
            for(Account objAcc : [SELECT Id, Name, Account_Ownerchip__c, Account_Type_MAIN__c, AVP_Status__c, Availpro_Status__c, Invoicing_Status__c, Account_Support__c from Account Where Main_Opportunity__c =: objOpp.Id]){
                if((objAcc != null) && (objOpp.StageName == 'Closed won') && (objOpp.First_Closed_Opp__c == false) && (objAcc.Account_Ownerchip__c != 'Partner')){
                    objAcc.Account_Type_MAIN__c = 'To be activated';  <!-- Custom Account Status -->
                    objAcc.AVP_Status__c = 'To be set-up'; <!-- Custom Account Status -->
                    objAcc.Availpro_Status__c = 'Client'; <!-- Custom Account Status -->
                    objAcc.Invoicing_Status__c = 'To be set-up'; <!-- Custom Account Status -->
                    objAcc.Account_Support__c = 'AVAILPRO - NEW HOTEL'; <!-- Custom Account Status -->
                    objOpp.First_Closed_Opp__c = true; <!-- This is the checkbox -->
                    listAccToUpdate.add(objAcc); 
                }
            }
            update listAccToUpdate;
        }
    }
}

Thanks Sagar
Sagar LakhaniSagar Lakhani

for(Opportunity objOpp : [SELECT Id,StageName,First_Closed_Opp__c from Opportunity])

I think in above line you have to Use 'listOpportunity' in place of [SELECT Id,StageName,First_Closed_Opp__c from Opportunity]

 

Thomas CailletThomas Caillet
Perfect Thanks very much Sagar for your time I appreciate
Have a nice day
Thomas 
Sagar LakhaniSagar Lakhani
you're welcome anytime Thomas...
 
Thomas CailletThomas Caillet
Hi Sagar,

I don't know if you will see my post but I try :)
Thanks for the trigger that's work perfectly. 
But I have one problem. Yesterday, I tried to mass update stage field of 17000 opportunity and I got this error : OpportunityTrigger: System.LimitException: Too many SOQL queries: 101.
I think that the trigger is trigger for any edit on opportunity. Could we change the code to execute this trigger just when the opportunity (linked to the main opportunity field on account) is closed won ?

Thanks
Thomas
Sagar LakhaniSagar Lakhani
Please update your code with below code and try to mass update and me know you get success or not.
public class OpportunityTriggerHandler{  
    public static void updateRelatedAccStatusOnOpp(List<Opportunity> listOpportunity){
		List<Opportunity> listOpportunitiesToUpdate = new List<Opportunity>();
        List<Account> listAccToUpdate = new List<Account>();
        for(Opportunity objOpp : [SELECT Id,StageName,First_Closed_Opp__c from Opportunity]){
            for(Account objAcc : [SELECT Id, Name, Account_Ownerchip__c, Account_Type_MAIN__c, AVP_Status__c, Availpro_Status__c, Invoicing_Status__c, Account_Support__c from Account Where Main_Opportunity__c =: objOpp.Id]){
                if((objAcc != null) && (objOpp.StageName == 'Closed won') && (objOpp.First_Closed_Opp__c == false) && (objAcc.Account_Ownerchip__c != 'Partner')){
                    objAcc.Account_Type_MAIN__c = 'To be activated';  <!-- Custom Account Status -->
                    objAcc.AVP_Status__c = 'To be set-up'; <!-- Custom Account Status -->
                    objAcc.Availpro_Status__c = 'Client'; <!-- Custom Account Status -->
                    objAcc.Invoicing_Status__c = 'To be set-up'; <!-- Custom Account Status -->
                    objAcc.Account_Support__c = 'AVAILPRO - NEW HOTEL'; <!-- Custom Account Status -->
                    objOpp.First_Closed_Opp__c = true; <!-- This is the checkbox -->
	                listOpportunitiesToUpdate.add(objOpp);
                    listAccToUpdate.add(objAcc); 
                }
            }
            update listAccToUpdate;
        }
		update listOpportunitiesToUpdate;
    }
}

Thanks,
Sagar Lakhani (Skype : Sagar.Lakhani3)
 
Thomas CailletThomas Caillet
Thanks Sagar but the code doesn't work I have this error when I tried to closed won my opportunity : 
Error:Apex trigger OpportunityTrigger caused an unexpected exception, contact your administrator: OpportunityTrigger: execution of BeforeUpdate caused by: System.SObjectException: DML statement cannot operate on trigger.new or trigger.old: Class.OpportunityTriggerHandler.updateRelatedAccStatusOnOpp: line 20, column 1

Thomas
Sagar LakhaniSagar Lakhani
Please ping me on sagar.lakhani3 so we can communicate in better way.
Jeevika SeenivasanJeevika Seenivasan
how to clear a record from a single page block after record select and shifted to another page block .....i used clear in vf apexbut its not working....
if any one knows pls share me.....