+ Start a Discussion
Gallery IntegrationGallery Integration 

Process builder hitting SOQL queries:101 error

Hi Everyone,

I am trying to add 100++ campaign members to the campaign that I have created.
However while doing this we encounter a SOQL queries:101 error.
After doing some checking this error is caused by this 3 process builders:
1. Process builder to update a campaign member field based on contact field whenever it is created.
2. Process builder to update a campaign member field based on other campaign member field whenever it is created.
3. Process builder to update campaign member field based on other campaign field whenever it is created or edited.

If the we limit the upload to 25 or 50 members at once we will not receive this error.
After deactivating these 3 process builder the upload can work correctly even with 100++ member at once

Are there any solution to solve this? Does process builder do not support bulkified upload?

User-added image
Best Answer chosen by Gallery Integration
Narender Singh(Nads)Narender Singh(Nads)
Hi,

First create a class named 'checkRecursive' and copy the following code in it:
public Class checkRecursive{
    private static boolean run = true;
    public static boolean runOnce(){
    if(run){
     run=false;
     return true;
    }else{
        return run;
    }
    }
}

Now create a trigger on Campaign member and copy this code in your trigger:
trigger UpdateCM on CampaignMember (after insert, after update) {
    
    if(checkRecursive.runOnce())
    {
        list<id> CMIDs= new list<id>();
    	set<id> ConIds=new set<id>();
    	for(CampaignMember c:trigger.new){
        	if(c.ContactId!=NULL){
            	CMIDs.add(c);
            	ConIds.add(c.ContactId);
        	}
    	}
   		map<id,contact> ConMap=new map<id,contact>();
   		ConMap=[select id,Relation_Manager__c from contact where id in :ConIds ];
   		CampaignMember[] cmList=[select id,contactid,Relation_Manager__c where id in :CMIDs];
   
    	CampaignMember[] ListToUpdate=new CampaignMember[]{};
    	for(CampaignMember c:cmList){
        	c.Relation_Manager__c=conmap.get(c.contactid).Relation_Manager__c;
        	ListToUpdate.add(c);
    	}
    	if(ListToUpdate.size()>0)
        	update ListToUpdate;
     }
}
Note: There might be few compilation errors(typos).

Let me know if it helps.
Thanks!

All Answers

Narender Singh(Nads)Narender Singh(Nads)
Hi,
Can you please tell me what value you are assigning to Relationship Manager. The field under 'Value' is only partially visible.
Gallery IntegrationGallery Integration
Hi Nads,

Its the field from contacts object associated with this campaign member called "Relation_Manager__c" too. It is a picklist field.


 
Narender Singh(Nads)Narender Singh(Nads)
Hi,

First create a class named 'checkRecursive' and copy the following code in it:
public Class checkRecursive{
    private static boolean run = true;
    public static boolean runOnce(){
    if(run){
     run=false;
     return true;
    }else{
        return run;
    }
    }
}

Now create a trigger on Campaign member and copy this code in your trigger:
trigger UpdateCM on CampaignMember (after insert, after update) {
    
    if(checkRecursive.runOnce())
    {
        list<id> CMIDs= new list<id>();
    	set<id> ConIds=new set<id>();
    	for(CampaignMember c:trigger.new){
        	if(c.ContactId!=NULL){
            	CMIDs.add(c);
            	ConIds.add(c.ContactId);
        	}
    	}
   		map<id,contact> ConMap=new map<id,contact>();
   		ConMap=[select id,Relation_Manager__c from contact where id in :ConIds ];
   		CampaignMember[] cmList=[select id,contactid,Relation_Manager__c where id in :CMIDs];
   
    	CampaignMember[] ListToUpdate=new CampaignMember[]{};
    	for(CampaignMember c:cmList){
        	c.Relation_Manager__c=conmap.get(c.contactid).Relation_Manager__c;
        	ListToUpdate.add(c);
    	}
    	if(ListToUpdate.size()>0)
        	update ListToUpdate;
     }
}
Note: There might be few compilation errors(typos).

Let me know if it helps.
Thanks!
This was selected as the best answer
Gallery IntegrationGallery Integration
Hi Nads,

Thanks for this! Really appreciate your help.

However, this is just one of the process builder that caused the error.
Does this means that I must create another 2 trigger to cover the other process builder?

Thanks!
Narender Singh(Nads)Narender Singh(Nads)
Hi,
No, not at all. We can append the logic for the other two Processes. How can that be done will depend on what logic the other two processes executing.

If you found my answer helpful, please mark it as the best answer so that others with similar kind of problem can benefit from this post.

Thanks
Gallery IntegrationGallery Integration
Hi Nads,

Really appreciate for your help.
I am new in Apex and just start learning.
As i read in some article it said that i's better to use "click and drag" process like workflow rule and process builder rather  than Apex code, because Apex required more effort and also time for the deployment.
However, if process builder could not provide what we need then I believe we don't have other choices.

This is the second and third process builders, could these fit into 1 trigger or shall we create another triggers for them?

2nd Process Builder
User-added image
User-added image


3rd Process Builder
User-added image
User-added image


Also, in addition to the code that you write before for the 1st process builder.
Whats the checkRecursive function for?
I tried another similar code like this, and it also work.
Shall this code be replaced?
trigger CampaignMemberUpdateRMandMailingList on CampaignMember (before insert, before update) {
 // create a set of all the unique ownerIds
    Set<id> CampaignMemberIds = new Set<id>();
    for (CampaignMember a : Trigger.new)
        CampaignMemberIds.add(a.ContactId);   

    // query for all the User records for the unique userIds in the records
    // create a map for a lookup / hash table for the user info
    Map<id, Contact> contacts = new Map<id, Contact>([Select Relationship_Manager__c, Mailing_List__c from Contact Where Id in :CampaignMemberIds]);  

    // iterate over the list of records being processed in the trigger and
    // set the color before being inserted or updated
    for (CampaignMember a : Trigger.new)
    { a.Relationship_Manager__c = contacts.get(a.ContactId).Relationship_Manager__c;
      a.Mailing_List__c = contacts.get(a.ContactId).Mailing_List__c;
    }

}

 
Narender Singh(Nads)Narender Singh(Nads)
Hi,
The code you provided will also work. It just skipped my mind that we could do all this on before event as well.
The purpose of checkrecursive class was to skip the recursion because the trigger was working on After update event(Let me know if the point is still not clear to you).

And about the article which you read, it is absolutely correct. Apex code should be your last resort. Because coding comes with a lot of considerations which programmers often miss which leads to unhandled exceptions.
But why use code in this scenario?
The answer is because you are hitting the governer limits.

So you have two options now: Either use a flow(I haven't worked much with flows) or use the apex trigger.

And for the snaps you sent me, yes we can add that functionality in your trigger code. No need to create seperate triggers for them.
It is always recommended that you use a single trigger per object(Best Practice).
 
Gallery IntegrationGallery Integration
Hi Nad,

Thanks a lot for your clarification.
Since the before event code is simpler, I will use that as it has the same functionality.

Will try to create another 2 triggers, I think it's best to separate trigger for the 3rd one because it associated with campaign object, not campaign member.

 
Narender Singh(Nads)Narender Singh(Nads)
Cool. Happy Coding!
Gallery IntegrationGallery Integration
Hi Nads,

The trigger is succesfully created, and I successfully inserted 200 campaign member at once.
However, when I create the Test Class the same 101 soql queries occured again.

Is there anything wrong with my test class?
@isTest(SeeAllData=true) 
public class CampaignMemberUpdateRMandMailingListTest {

    static testMethod void testBulkInsert() {
        
        //Creates Contact to be linked to Campaign Member
        List<contact> contacts = new List<contact>();
        
       	for (Integer i=0;i<200;i++) {

            Contact a = new Contact(
                Email = 'Test'+ i + '@testreminder.com',
                LastName = 'TestContact',
                Relationship_Manager__c = 'mr abc',
                Mailing_List__c = 'mailing list abc'             
            );
            contacts.add(a);
        } 
        insert contacts;
        
        //Create Campaign to be linked with Campaign Member
       Campaign camp = new Campaign(Name = 'TestOnlyCampaign', IsActive = TRUE, Official_Title__C = 'TestOnlyCampaign', Type = 'Others', Venue__c = 'venue 123');            
       insert camp;
        
     test.startTest();
        
        //Insert Campaign member
        List<CampaignMember> members = new List<CampaignMember>();
        
       	for (Integer i=0;i<200;i++) {

            CampaignMember c = new CampaignMember();
            List<Contact> con = [SELECT ID FROM Contact Where Email =: 'Test'+ i + '@testreminder.com' LIMIT 1];
            c.ContactId = con[0].ID;
            c.Status = 'Open';
            c.Sender__c = 'mr abc';
            c.CampaignId = camp.Id;        
           
        members.add(c);
        } 
        insert members;

     test.stopTest();

        
    }    
}

 
Narender Singh(Nads)Narender Singh(Nads)
Hi,
The reason you are getting this error is because you are doing a SOQL query in your for loop.

Try this code:
@isTest(SeeAllData=true) 
public class CampaignMemberUpdateRMandMailingListTest {

    static testMethod void testBulkInsert() {
        
        //Creates Contact to be linked to Campaign Member
        List<contact> contacts = new List<contact>();
        
       	for (Integer i=0;i<200;i++) {

            Contact a = new Contact(
                Email = 'Test'+ i + '@testreminder.com',
                LastName = 'TestContact',
                Relationship_Manager__c = 'mr abc',
                Mailing_List__c = 'mailing list abc'             
            );
            contacts.add(a);
        } 
        insert contacts;
        
        //Create Campaign to be linked with Campaign Member
       Campaign camp = new Campaign(Name = 'TestOnlyCampaign', IsActive = TRUE, Official_Title__C = 'TestOnlyCampaign', Type = 'Others', Venue__c = 'venue 123');            
       insert camp;
        
     test.startTest();
        
        //Insert Campaign member
        List<CampaignMember> members = new List<CampaignMember>();
        
       	for (Integer i=0;i<200;i++) {

            CampaignMember c = new CampaignMember();
            c.ContactId = con[0].ID;
            c.Status = 'Open';
            c.Sender__c = 'mr abc';
            c.CampaignId = camp.Id;        
           
        members.add(c);
        } 
        insert members;

     test.stopTest();
   }    
}

 
Gallery IntegrationGallery Integration
Hi Nads,

Thanks for this.
I manage to take out the query from the loop.