Apex Code Development You need to sign in to do that
Don't have an account?
Gallery 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?
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?
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
Can you please tell me what value you are assigning to Relationship Manager. The field under 'Value' is only partially visible.
Its the field from contacts object associated with this campaign member called "Relation_Manager__c" too. It is a picklist field.
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!
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!
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
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
3rd Process Builder
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; } }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).
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.
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(); } }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(); } }Thanks for this.
I manage to take out the query from the loop.