function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Mohammed SiddiqueMohammed Siddique 

System.LimitException: Too many SOQL queries: 101 : CANNOT_INSERT_UPDATE_ACTIVATE_ENT

Hello,
I dont have any Coding knowledge and really hoping some one to help me out with the trigger issue I have.
we have a trigger written to pull all the contact info and roll up on one of our Account record type which is working fine in development. But we are receiving an error within our Hubspot application (Installed Package) during a mass update to contacts. The Error message in HubSpot for not syncing the contacts reads: java.lang.RuntimeException: Salesforce save error: trgr_NumberOfProvidersAccountRollUp: System.LimitException: Too many SOQL queries: 101 : CANNOT_INSERT_UPDATE_ACTIVATE_ENT wanted to know how can we adjust our code so that the number os SOQL fired is less than 100. Below is the Code can you please help us out


trigger trgr_ParentAccount_Provider_Count on Account (after insert, after update, after delete, after undelete)
{
    Set<Id> QPAcctIds = new Set<Id>();
    List<Account> UpdtAcct = new List<Account>();

    if(Trigger.isDelete)
    {
        for (Account Acct0 : Trigger.old)
        {
            //qualify only those deleted accounts that are not Parent
            //Add parentid of those deleted accounts to the Id list
            
            system.debug('Deleted Account ---> ' + Acct0.name);
            
            If(Acct0.ParentID != NULL)
               {
                   QPAcctIds.add(Acct0.ParentId);
               }
        }
    }
    
    if(!Trigger.isDelete)
    {
        for (Account Acct : Trigger.new)
        {
            //qualify only those accounts that are not Parent
            //Add parentid of those accounts to the same Id list from above
            
            system.debug('Account ---> ' + Acct.name);
            
            If(Acct.ParentID != NULL)
               {
                   QPAcctIds.add(Acct.ParentId);
               }
        }
    }   
    
    system.debug('Qualified Parent Accounts ---> ' + QPAcctIds);
        
    for (Id QPAId : QPAcctIds)
    {
        Account PAcct = [select id, name from Account where id =: QPAId];
        
        for (AggregateResult result : [Select sum(Full_time_provider_Licenses__c) FTP,
                                       sum(Part_time_provider_Licenses__c) PTP,
                                       sum(Hospitalist_Licenses__c) HSP,
                                       sum(Locum_Licenses__c) LCM,
                                       sum(Rotating_Provider_Licenses__c) RTP
                                       from Account where ParentID =: QPAId])
        {   
            PAcct.P_Full_time_providers__c = (Decimal)result.get('FTP');
            PAcct.P_Part_time_providers__c = (Decimal)result.get('PTP');
            PAcct.P_Hospitalists__c = (Decimal)result.get('HSP');
            PAcct.P_Locums__c = (Decimal)result.get('LCM');
            PAcct.P_Rotating_Providers__c = (Decimal)result.get('RTP');            
        }
        
        system.debug('FTP count ---> '+ PAcct.P_Full_time_providers__c);
        system.debug('PTP count ---> '+ PAcct.P_Part_time_providers__c);
        system.debug('HSP count ---> '+ PAcct.P_Hospitalists__c);
        system.debug('LCM count ---> '+ PAcct.P_Locums__c);
        system.debug('RTP count ---> '+ PAcct.P_Rotating_Providers__c);
        
        UpdtAcct.add(PAcct);
    }
        
        system.debug('Parent Accounts to be updated ---> ' + UpdtAcct);
        
        Update UpdtAcct;    
}
Steven NsubugaSteven Nsubuga
Use this 
trigger trgr_ParentAccount_Provider_Count on Account (after insert, after update, after delete, after undelete)
{
    Set<Id> QPAcctIds = new Set<Id>();
    List<Account> UpdtAcct = new List<Account>();

    if(Trigger.isDelete)
    {
        for (Account Acct0 : Trigger.old)
        {
            //qualify only those deleted accounts that are not Parent
            //Add parentid of those deleted accounts to the Id list
            
            system.debug('Deleted Account ---> ' + Acct0.name);
            
            If(Acct0.ParentID != NULL)
               {
                   QPAcctIds.add(Acct0.ParentId);
               }
        }
    }
    
    if(!Trigger.isDelete)
    {
        for (Account Acct : Trigger.new)
        {
            //qualify only those accounts that are not Parent
            //Add parentid of those accounts to the same Id list from above
            
            system.debug('Account ---> ' + Acct.name);
            
            If(Acct.ParentID != NULL)
               {
                   QPAcctIds.add(Acct.ParentId);
               }
        }
    }   
    
    system.debug('Qualified Parent Accounts ---> ' + QPAcctIds);
    
	Map<Id, Account> PAcct = new Map<Id, Account>([select id, name, 
																P_Full_time_providers__c, 
																P_Part_time_providers__c,
																P_Hospitalists__c,
																P_Locums__c,
																P_Rotating_Providers__c
										from Account where id IN :QPAId]);
	
	for (AggregateResult result : [Select sum(Full_time_provider_Licenses__c) FTP,
                                       sum(Part_time_provider_Licenses__c) PTP,
                                       sum(Hospitalist_Licenses__c) HSP,
                                       sum(Locum_Licenses__c) LCM,
                                       sum(Rotating_Provider_Licenses__c) RTP,
									   ParentID
                                       from Account where ParentID IN :QPAId
									   group by ParentID])
        {   
			Account acct = PAcct.get(String.valueOf(result.get('ParentID')));
            acct.P_Full_time_providers__c = (Decimal)result.get('FTP');
            acct.P_Part_time_providers__c = (Decimal)result.get('PTP');
            acct.P_Hospitalists__c = (Decimal)result.get('HSP');
            acct.P_Locums__c = (Decimal)result.get('LCM');
            acct.P_Rotating_Providers__c = (Decimal)result.get('RTP');

			system.debug('FTP count ---> '+ acct.P_Full_time_providers__c);
			system.debug('PTP count ---> '+ acct.P_Part_time_providers__c);
			system.debug('HSP count ---> '+ acct.P_Hospitalists__c);
			system.debug('LCM count ---> '+ acct.P_Locums__c);
			system.debug('RTP count ---> '+ acct.P_Rotating_Providers__c);
			
			UpdtAcct.add(acct);
        }
        
        system.debug('Parent Accounts to be updated ---> ' + UpdtAcct);
        
        Update UpdtAcct;    
}

 
Mohammed SiddiqueMohammed Siddique
Let me replace my code and check now. Thanks
Mohammed SiddiqueMohammed Siddique
I have got an error.. 
Error: Compile Error: Variable does not exist: QPAId at line 46 column 67
Raj VakatiRaj Vakati
trigger trgr_ParentAccount_Provider_Count on Account (after insert, after update, after delete, after undelete)
{
    Set<Id> QPAcctIds = new Set<Id>();
    List<Account> UpdtAcct = new List<Account>();

    if(Trigger.isDelete)
    {
        for (Account Acct0 : Trigger.old)
        {
            //qualify only those deleted accounts that are not Parent
            //Add parentid of those deleted accounts to the Id list
            
            system.debug('Deleted Account ---> ' + Acct0.name);
            
            If(Acct0.ParentID != NULL)
               {
                   QPAcctIds.add(Acct0.ParentId);
               }
        }
    }
    
    if(!Trigger.isDelete)
    {
        for (Account Acct : Trigger.new)
        {
            //qualify only those accounts that are not Parent
            //Add parentid of those accounts to the same Id list from above
            
            system.debug('Account ---> ' + Acct.name);
            
            If(Acct.ParentID != NULL)
               {
                   QPAcctIds.add(Acct.ParentId);
               }
        }
    }   
    
    system.debug('Qualified Parent Accounts ---> ' + QPAcctIds);
    
    Map<Id, Account> PAcct = new Map<Id, Account>([select id, name, 
                                                                P_Full_time_providers__c, 
                                                                P_Part_time_providers__c,
                                                                P_Hospitalists__c,
                                                                P_Locums__c,
                                                                P_Rotating_Providers__c
                                        from Account where id IN :QPAId]);
    
    for (AggregateResult result : [Select sum(Full_time_provider_Licenses__c) FTP,
                                       sum(Part_time_provider_Licenses__c) PTP,
                                       sum(Hospitalist_Licenses__c) HSP,
                                       sum(Locum_Licenses__c) LCM,
                                       sum(Rotating_Provider_Licenses__c) RTP,
                                       ParentID
                                       from Account where ParentID IN :QPAId
                                       group by ParentID])
        {   
        if(result.get('ParentID')!=null)
            Account acct = PAcct.get(String.valueOf(result.get('ParentID')));
        if(result.get('FTP')!=null)
            acct.P_Full_time_providers__c = (Decimal)result.get('FTP');
        if(result.get('PTP')!=null)
            acct.P_Part_time_providers__c = (Decimal)result.get('PTP');
        if(result.get('HSP')!=null)
            acct.P_Hospitalists__c = (Decimal)result.get('HSP');
        if(result.get('LCM')!=null) 
            acct.P_Locums__c = (Decimal)result.get('LCM');
        if(result.get('RTP')!=null)
            acct.P_Rotating_Providers__c = (Decimal)result.get('RTP');

            system.debug('FTP count ---> '+ acct.P_Full_time_providers__c);
            system.debug('PTP count ---> '+ acct.P_Part_time_providers__c);
            system.debug('HSP count ---> '+ acct.P_Hospitalists__c);
            system.debug('LCM count ---> '+ acct.P_Locums__c);
            system.debug('RTP count ---> '+ acct.P_Rotating_Providers__c);
            
            UpdtAcct.add(acct);
        }
        
        system.debug('Parent Accounts to be updated ---> ' + UpdtAcct);
        if(UpdtAcct.size()>0){
        Update UpdtAcct;
        }        
}
Steven NsubugaSteven Nsubuga
Fixed it
Try this 
trigger trgr_ParentAccount_Provider_Count on Account (after insert, after update, after delete, after undelete)
{
    Set<Id> QPAcctIds = new Set<Id>();
    List<Account> UpdtAcct = new List<Account>();

    if(Trigger.isDelete)
    {
        for (Account Acct0 : Trigger.old)
        {
            //qualify only those deleted accounts that are not Parent
            //Add parentid of those deleted accounts to the Id list
            
            system.debug('Deleted Account ---> ' + Acct0.name);
            
            If(Acct0.ParentID != NULL)
               {
                   QPAcctIds.add(Acct0.ParentId);
               }
        }
    }
    
    if(!Trigger.isDelete)
    {
        for (Account Acct : Trigger.new)
        {
            //qualify only those accounts that are not Parent
            //Add parentid of those accounts to the same Id list from above
            
            system.debug('Account ---> ' + Acct.name);
            
            If(Acct.ParentID != NULL)
               {
                   QPAcctIds.add(Acct.ParentId);
               }
        }
    }   
    
    system.debug('Qualified Parent Accounts ---> ' + QPAcctIds);
    
	Map<Id, Account> PAcct = new Map<Id, Account>([select id, name, 
																P_Full_time_providers__c, 
																P_Part_time_providers__c,
																P_Hospitalists__c,
																P_Locums__c,
																P_Rotating_Providers__c
										from Account where id IN :QPAcctIds]);
	
	for (AggregateResult result : [Select sum(Full_time_provider_Licenses__c) FTP,
                                       sum(Part_time_provider_Licenses__c) PTP,
                                       sum(Hospitalist_Licenses__c) HSP,
                                       sum(Locum_Licenses__c) LCM,
                                       sum(Rotating_Provider_Licenses__c) RTP,
									   ParentID
                                       from Account where ParentID IN :QPAcctIds
									   group by ParentID])
        {   
			Account acct = PAcct.get(String.valueOf(result.get('ParentID')));
            acct.P_Full_time_providers__c = (Decimal)result.get('FTP');
            acct.P_Part_time_providers__c = (Decimal)result.get('PTP');
            acct.P_Hospitalists__c = (Decimal)result.get('HSP');
            acct.P_Locums__c = (Decimal)result.get('LCM');
            acct.P_Rotating_Providers__c = (Decimal)result.get('RTP');

			system.debug('FTP count ---> '+ acct.P_Full_time_providers__c);
			system.debug('PTP count ---> '+ acct.P_Part_time_providers__c);
			system.debug('HSP count ---> '+ acct.P_Hospitalists__c);
			system.debug('LCM count ---> '+ acct.P_Locums__c);
			system.debug('RTP count ---> '+ acct.P_Rotating_Providers__c);
			
			UpdtAcct.add(acct);
        }
        
        system.debug('Parent Accounts to be updated ---> ' + UpdtAcct);
        
        Update UpdtAcct;    
}

 
Mohammed SiddiqueMohammed Siddique
I was able to replace my code with the updated code what Steven sent me, so the changes are made on the sandbox so i will need to deploye this code to production now for it to overwrite the exiting code we have?
Steven NsubugaSteven Nsubuga
Yes, but you need to write a test class for the trigger.
Mohammed SiddiqueMohammed Siddique
This is what test class was wirtten. Do i need to make changes to this as well? 
@istest
public class test_trgr_ParentAccount_Provider_Count
{
    Static testmethod void trgr_ParentAccount_Provider_Count()
    {
        List<Account> accts = new List<Account>();
        
        Integer numAccts = 10;
        Integer numContactsPerAcct = 2;

        Account b = new Account(Name='TestAccount0');
        insert b;
       
        system.debug('test parent ---> ' + b.name);
        
        for(Integer i=0;i<numAccts;i++) {
            Account a = new Account(Name='TestAccount' + i, ParentId = b.Id);
            accts.add(a);
            system.debug('inserted test account name ---> ' + a.name);
        }
        insert accts;
        
        List<Contact> cons = new List<Contact>();
        for (Integer j=1;j<numAccts;j++) {
            Account acct = accts[j];            
            // For each account just inserted, add contacts
            for (Integer k=numContactsPerAcct*j;k<numContactsPerAcct*(j+1);k++) {
                cons.add(new Contact(firstname='Test'+k,
                                     lastname='Test'+k,
                                     AccountId=acct.Id,
                                     Full_Part_Time_Locum__c = 'Hospitalist'));
            }
        }
        // Insert all contacts for all accounts
        insert cons;
        system.debug('Inserted contact --> ' + cons);
        
        //Delete one of the Accounts created earlier
        
        Delete accts[1];
        
    }
}

 
Raj VakatiRaj Vakati
try this code

// Add the below values in to account insert  

			b.P_Part_time_providers__c
			b.P_Hospitalists__c
			b.P_Locums__c
			b.P_Rotating_Providers__c '

@istest
public class test_trgr_ParentAccount_Provider_Count
{
    Static testmethod void trgr_ParentAccount_Provider_Count()
    {
        List<Account> accts = new List<Account>();
        
        Integer numAccts = 10;
        Integer numContactsPerAcct = 2;

        Account b = new Account(Name='TestAccount0');
		// Add the below values
			b.P_Part_time_providers__c
			b.P_Hospitalists__c
			b.P_Locums__c
			b.P_Rotating_Providers__c '
        insert b;
       
        system.debug('test parent ---> ' + b.name);
        
        for(Integer i=0;i<numAccts;i++) {
            Account a = new Account(Name='TestAccount' + i, ParentId = b.Id);
			// Add the below values
			a.P_Part_time_providers__c
			a.P_Hospitalists__c
			a.P_Locums__c
			a.P_Rotating_Providers__c '
            accts.add(a);
            system.debug('inserted test account name ---> ' + a.name);
        }
        insert accts;
        
        List<Contact> cons = new List<Contact>();
        for (Integer j=1;j<numAccts;j++) {
            Account acct = accts[j];            
            // For each account just inserted, add contacts
            for (Integer k=numContactsPerAcct*j;k<numContactsPerAcct*(j+1);k++) {
                cons.add(new Contact(firstname='Test'+k,
                                     lastname='Test'+k,
                                     AccountId=acct.Id,
                                     Full_Part_Time_Locum__c = 'Hospitalist'));
            }
        }
        // Insert all contacts for all accounts
        insert cons;
        system.debug('Inserted contact --> ' + cons);
        
        //Delete one of the Accounts created earlier
        
        Delete accts[1];
        
    }
}

 
Steven NsubugaSteven Nsubuga
Try running the test as it is to see what happens. If it runs ok then you are good to go
Mohammed SiddiqueMohammed Siddique
It gives me an error.. Error: Compile Error: Missing ';' at 'b.P_Hospitalists__c' at line 14 column 13
Mohammed SiddiqueMohammed Siddique
Steven: I tried running test with existing class  there wasn't any issue it passed. will i be good to do the change set now?
Steven NsubugaSteven Nsubuga
Yes!! Include the trigger and the test class in the same changeset