Apex Code Development You need to sign in to do that
Don't have an account?
how to remove query in for loop And want to avoid for loop with in for loop
Hello Community,
I am stuck to find the solution on my Trigger . I want to Optimize my trigger code because some one use Query in for loop and nested for loop. I am abel to run code for single record but not in bulk.
Here My code please help on this, Thanks In advance.
Thanks
Accky
I am stuck to find the solution on my Trigger . I want to Optimize my trigger code because some one use Query in for loop and nested for loop. I am abel to run code for single record but not in bulk.
Here My code please help on this, Thanks In advance.
try{
for(opportunity opportunityobj:trigger.new)
{
opportunity opptyofoldmap=trigger.oldmap.get(opportunityobj.id);
if(opptyofoldmap.closeDate!=null&opptyofoldmap.closeDate!=opportunityobj.closeDate)
{
Integer dueDate=opptyofoldmap.closeDate.daysBetween(opportunityobj.closeDate);
if(dueDate!=0 && dueDate > 0 )
{
list<OpportunityLineItem> newopp=[select id,Revenue_Start_Date__c,Opportunityid, UnitPrice,
Revenue_Exchange_Rate__c, TCV__c, contract_term__c, CYR__c, NYR__c
from OpportunityLineItem where Opportunityid=:opportunityobj.id AND Revenue_Start_Date__c < :opportunityobj.closeDate];
for(OpportunityLineItem OpportunityProductobject:newopp)
{
integer i=0;
OpportunityProductobject.Revenue_Start_Date__c=OpportunityProductobject.Revenue_Start_Date__c+dueDate;
OpportunityProductupdate.add(OpportunityProductobject);
}
}
}
}
}
Thanks
Accky
Move the Soql query outside of the for loop. Since you have to add some logic to 'Revenue_Start_Date__c' of oppLine item, try something like below,
try{ List<OpportunityLineItem>OpportunityProductupdate= new List<OpportunityLineItem>(); map<ID,opportunity>oldOpps= new map<ID,opportunity>(); map<ID,opportunity>newOpps= new map<ID,opportunity>(); map<ID,opportunity>matchingOpps= new Map<ID,opportunity>(); for(opportunity opportunityobj:trigger.new) { opportunity opptyofoldmap=trigger.oldmap.get(opportunityobj.id); newOpps.put(opportunityobj.id,opportunityobj); oldOpps.put(opportunityobj.id,trigger.oldmap.get(opportunityobj.id)); Integer dueDate=opptyofoldmap.closeDate.daysBetween(opportunityobj.closeDate); if(dueDate!=0 && dueDate > 0 ) { matchingOpps.put(opportunityobj.id,opportunityobj); } } list<OpportunityLineItem> newopp=[select id,Revenue_Start_Date__c,Opportunityid, UnitPrice from OpportunityLineItem where Opportunityid in : matchingOpps.values() ]; for(OpportunityLineItem OpportunityProductobject:newopp) { if( OpportunityProductobject.Revenue_Start_Date__c < matchingOpps.get(OpportunityProductobject.Opportunityid).closeDate){ Integer dueDate=oldOpps.get(OpportunityProductobject.Opportunityid).closeDate.daysBetween(newOpps.get(OpportunityProductobject.Opportunityid).closeDate); OpportunityProductobject.Revenue_Start_Date__c=OpportunityProductobject.Revenue_Start_Date__c+dueDate; OpportunityProductupdate.add(OpportunityProductobject); } } update OpportunityProductupdate; }regardsSampath
All Answers
Please check once below snippet.
try{ set<id> oppIds = new set<id>(); for(opportunity opportunityobj:trigger.new) { opportunity opptyofoldmap=trigger.oldmap.get(opportunityobj.id); if(opptyofoldmap.closeDate!=null&opptyofoldmap.closeDate!=opportunityobj.closeDate) { Integer dueDate=opptyofoldmap.closeDate.daysBetween(opportunityobj.closeDate); if(dueDate!=0 && dueDate > 0 ) { oppIds.add(opportunityobj.id); } } } list<OpportunityLineItem> newopp=[select id,Revenue_Start_Date__c,Opportunityid, UnitPrice, Revenue_Exchange_Rate__c, TCV__c, contract_term__c, CYR__c, NYR__c from OpportunityLineItem where Opportunityid in : oppIds AND Revenue_Start_Date__c < :opportunityobj.closeDate]; for(OpportunityLineItem OpportunityProductobject:newopp) { integer i=0; OpportunityProductobject.Revenue_Start_Date__c=OpportunityProductobject.Revenue_Start_Date__c+dueDate; OpportunityProductupdate.add(OpportunityProductobject); } }Hope this helps you!
If my answer helps resolve your query, please mark it as the 'Best Answer' & upvote it to benefit others.
Thanks
Varaprasad
@For Support: varaprasad4sfdc@gmail.com
Move the Soql query outside of the for loop. Since you have to add some logic to 'Revenue_Start_Date__c' of oppLine item, try something like below,
try{ List<OpportunityLineItem>OpportunityProductupdate= new List<OpportunityLineItem>(); map<ID,opportunity>oldOpps= new map<ID,opportunity>(); map<ID,opportunity>newOpps= new map<ID,opportunity>(); map<ID,opportunity>matchingOpps= new Map<ID,opportunity>(); for(opportunity opportunityobj:trigger.new) { opportunity opptyofoldmap=trigger.oldmap.get(opportunityobj.id); newOpps.put(opportunityobj.id,opportunityobj); oldOpps.put(opportunityobj.id,trigger.oldmap.get(opportunityobj.id)); Integer dueDate=opptyofoldmap.closeDate.daysBetween(opportunityobj.closeDate); if(dueDate!=0 && dueDate > 0 ) { matchingOpps.put(opportunityobj.id,opportunityobj); } } list<OpportunityLineItem> newopp=[select id,Revenue_Start_Date__c,Opportunityid, UnitPrice from OpportunityLineItem where Opportunityid in : matchingOpps.values() ]; for(OpportunityLineItem OpportunityProductobject:newopp) { if( OpportunityProductobject.Revenue_Start_Date__c < matchingOpps.get(OpportunityProductobject.Opportunityid).closeDate){ Integer dueDate=oldOpps.get(OpportunityProductobject.Opportunityid).closeDate.daysBetween(newOpps.get(OpportunityProductobject.Opportunityid).closeDate); OpportunityProductobject.Revenue_Start_Date__c=OpportunityProductobject.Revenue_Start_Date__c+dueDate; OpportunityProductupdate.add(OpportunityProductobject); } } update OpportunityProductupdate; }regardsSampath
public static void afterInsert(List<Lead> 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() );} } } }