This code is been hitting a governor limit and getting Too many SOQL queries: 101, based on research I found that I need to remove SOQL query from my for loop and the For Loops within For Loop.
Requirement :
- We have a custom text field on Contact and Lead Object "NCPC_Pardot_Avail_Sub_Interest_Ids__c" which is also created on Pardot and used in forms or landing pages having 2 subscription/Interest IDs in that field.
- When the form is submitted from pardot prospect/lead is created in pardot, which is synced in CRM and passes the subscription/interest ID in the custom text field "NCPC_Pardot_Avail_Sub_Interest_Ids__c" which needs to be the comma-separated value(18 digit ID should be separated by (,))
- Based on the IDs Subscription/Interest record should be created which should be associated with the particular Lead ID for which it's created.
- Duplicate subscription/Interest should not be created for Lead if it already exists with subscription/interest i.e. If someone has unsubscribed it before and come back and fill out the form same subscription/interest record needs to Optin or Selected again instead of creating records.
public static void afterInsert(List newLeads,Map<ID,Lead> oldLeadMap){ //insert subscription records on field update - NCPC_Pardot_Avail_Sub_Interest_Ids__c subscriptionInterestRecords(newLeads,oldLeadMap);
}
//Process to create subscription from the indicated field update
public static void subscriptionInterestRecords(List<Lead> newList,Map<ID,Lead> oldMap){
try{
Map<Id,String> leadIdToAvailSubMap = new Map<Id,String>();
if(schema.SObjectType.Lead.isAccessible()
&& schema.SObjectType.Lead.fields.NCPC_Pardot_Avail_Sub_Interest_Ids__c.isAccessible()){
for(Lead leadRec : newList){
if( !String.isBlank(leadRec.NCPC_Pardot_Avail_Sub_Interest_Ids__c)){
//available subscription and interest are changing
leadIdToAvailSubMap.put(leadRec.Id,leadRec.NCPC_Pardot_Avail_Sub_Interest_Ids__c);
}
}
if(!leadIdToAvailSubMap.isEmpty()){
if(schema.SObjectType.ncpc__PC_Subscription__c.isAccessible()
&& schema.SObjectType.ncpc__PC_Interest__c.isAccessible()
&& schema.SObjectType.ncpc__PC_Available_Subscription_Interest__c.isAccessible()
&& schema.SObjectType.ncpc__PC_Available_Subscription_Interest__c.fields.ncpc__Type__c.isAccessible()
&& schema.SObjectType.ncpc__PC_Subscription__c.fields.ncpc__Contact__c.isAccessible()
&& schema.SObjectType.ncpc__PC_Interest__c.fields.ncpc__Contact__c.isAccessible()
){
for(Id leadId : leadIdToAvailSubMap.keySet()){
List<String> availSubscriptionInterestList = leadIdToAvailSubMap.get(leadId).split(',');
List<ncpc__PC_Subscription__c> addSubscriptionList = new List<ncpc__PC_Subscription__c>();
List<ncpc__PC_Interest__c> addInterestList = new List<ncpc__PC_Interest__c>();
Set<Id> subsIdSet = new Set<Id>();
Set<Id> interestIdSet = new Set<Id>();
if(availSubscriptionInterestList.size()>0){
for(ncpc__PC_Available_Subscription_Interest__c avail : [SELECT Id, ncpc__Type__c FROM ncpc__PC_Available_Subscription_Interest__c
WHERE Id IN : availSubscriptionInterestList ]){
if(avail.ncpc__Type__c == 'Subscription'){
subsIdSet.add(avail.Id);
}
if(avail.ncpc__Type__c == 'Interest'){
interestIdSet.add(avail.Id);
}
}
//subscriptionList
for( ncpc__PC_Subscription__c sub : [SELECT Id, ncpc__Lead__c,ncpc__Opt_In__c, ncpc__Related_Subscription_Interest__c FROM ncpc__PC_Subscription__c WHERE ncpc__Lead__c =: leadId]){
if(subsIdSet.contains(sub.ncpc__Related_Subscription_Interest__c)){
if(sub.ncpc__Opt_In__c == false){
ncpc__PC_Subscription__c s = new ncpc__PC_Subscription__c (Id=sub.Id);
s.ncpc__Opt_In__c = true;
s.ncpc__Opt_In_Date__c = Date.today();
addSubscriptionList.add(s);
}
//do nothing -- don't process this record and remove from set
subsIdSet.remove(sub.ncpc__Related_Subscription_Interest__c);
}
}
if(subsIdSet.size()>0){
//for remaining subscriptions
for(Id sub : subsIdSet){
ncpc__PC_Subscription__c recordSub = new ncpc__PC_Subscription__c();
recordSub.ncpc__Opt_In__c = true;
recordSub.ncpc__Lead__c = leadId;
recordSub.ncpc__Opt_In_Source__c = 'Pardot Form';
recordSub.ncpc__Related_Subscription_Interest__c = sub;
addSubscriptionList.add(recordSub);
}
}
//interestList
for( ncpc__PC_Interest__c sub : [SELECT Id, ncpc__Lead__c, ncpc__Selected__c, ncpc__Interest_Selected__c FROM ncpc__PC_Interest__c WHERE ncpc__Lead__c =: leadId]){
if(interestIdSet.contains(sub.ncpc__Interest_Selected__c)){
if(sub.ncpc__Selected__c == false){
ncpc__PC_Interest__c a = new ncpc__PC_Interest__c(Id=sub.Id);
a.ncpc__Selected__c = true;
a.ncpc__Captured_Date__c = Date.today();
addInterestList.add(a);
}
//do nothing -- don't process this record and remove from set
interestIdSet.remove(sub.ncpc__Interest_Selected__c);
}
}
if(interestIdSet.size()>0){
//for remaining interest
for(Id ins : interestIdSet){
ncpc__PC_Interest__c recordSub = new ncpc__PC_Interest__c();
recordSub.ncpc__Selected__c = true;
recordSub.ncpc__Lead__c = leadId;
recordSub.ncpc__Captured_Date__c = Date.today();
recordSub.ncpc__Interest_Selected__c = ins;
addInterestList.add(recordSub);
}
}
if(addSubscriptionList.size()>0){
upsert addSubscriptionList;
}
if(addInterestList.size()>0){
upsert addInterestList;
}
}
}
}
}
}
}catch(Exception e){
//catch error in some object
System.debug(+e.getMessage() + e.getStackTraceString());
}finally{
//blank out NCPC_Pardot_Avail_Sub_Interest_Ids__c to handle optouts and field not getting changed
List<Lead> updateLeadList = new List<Lead>();
for(Lead l : newList){
//update the field to blank once the subscription/interest records got created
if(String.isNotBlank(l.NCPC_Pardot_Avail_Sub_Interest_Ids__c)){
Lead lnew = new Lead(Id=l.Id,NCPC_Pardot_Avail_Sub_Interest_Ids__c='');
updateLeadList.add(lnew);
}
}
try{
if(updateLeadList.size()>0)
update updateLeadList;
}catch(Exception e){ System.debug(e.getStackTraceString() + ' ' + e.getMessage() );}
}
}
}
