+ Start a Discussion
Vasu@blrVasu@blr 

System.LimitException: Too many script statements: 200001

My code is hitting governor limits, please help me how can I limit the number of statements,

Mainly while importing data using dataloader for upto 5000,  I am getting this error,

please help me to reduce the code 

 

 

trigger Behr_MonthsRollUp_US on THD_Sales__c (after insert, after update, after delete, after undelete) {
    Map<Id,Account> updateAccounts = new Map<Id,Account>();
    Set<Id> updateAccountIds = new Set<Id>();
    //Map<Id,THD_Sales__c> tmap = new Map<Id,THD_Sales__c>([select Id,SFDC_Account_ID__c from THD_Sales__c where id in : Trigger.newmap.keyset()]);
  // If we are inserting, updating, or undeleting, use the new ID values
  if(Trigger.isInsert || Trigger.isUpdate || Trigger.isUndelete)    
    //Set<Id> mset = new Set<Id>();
    for(THD_Sales__c thdsales:Trigger.new)
      updateAccountIds.add(thdsales.SFDC_Account_ID__c);
  // If we are updating, some accounts might change, so include that as well as deletes
  if(Trigger.isUpdate || Trigger.isDelete)
    for(THD_Sales__c thdsales:Trigger.old)
      updateAccountIds.add(thdsales.SFDC_Account_ID__c);
  // Do not create a record for null field
  updateAccountIds.remove(null);  
  // Create in-memory copies for all accounts that will be affected
  for(Id accountId:updateAccountIds){
    updateAccounts.put(accountId,new Account(id=accountId,
    Total_Behr_SO_Sales_January__c=0, Total_Behr_SO_Sales_February__c=0,Total_Behr_SO_Sales_March__c=0,
    Total_Behr_SO_Sales_April__c=0,Total_Behr_SO_Sales_May__c=0,Total_Behr_SO_Sales_June__c=0,
    Total_Behr_SO_Sales_July__c=0,Total_Behr_SO_Sales_August__c=0,Total_Behr_SO_Sales_September__c=0,
    Total_Behr_SO_Sales_October__c=0,Total_Behr_SO_Sales_November__c=0,Total_Behr_SO_Sales_December__c=0,
    Total_Cash_Carry_Sales_January__c=0, Total_Cash_Carry_Sales_February__c=0,Total_Cash_Carry_Sales_March__c=0,
    Total_Cash_Carry_Sales_April__c=0,Total_Cash_Carry_Sales_May__c=0,Total_Cash_Carry_Sales_June__c=0,
    Total_Cash_Carry_Sales_July__c=0,Total_Cash_Carry_Sales_August__c=0,Total_Cash_Carry_Sales_September__c=0,
    Total_Cash_Carry_Sales_October__c=0,Total_Cash_Carry_Sales_November__c=0,Total_Cash_Carry_Sales_December__c=0
    ));
  // Run an optimized query that looks for all accounts that meet the if/then criteria
  List<THD_Sales__c> thdlst = [select id ,Month_Year__c, POS_Order_Type__c,Amount__c,SFDC_Account_ID__c from THD_Sales__c
  where SFDC_Account_ID__c in :updateAccountIds and Month_Year__c != null];
  for(THD_Sales__c thdsales:thdlst){  
  //T SO January
  if((thdsales.SFDC_Account_ID__c == accountId) && (thdsales.POS_Order_Type__c == 'Special Order') && 
  thdsales.Month_Year__c.month() == 1) {
      updateAccounts.get(thdsales.SFDC_Account_ID__c).Total_Behr_SO_Sales_January__c += thdsales.Amount__c;
      System.Debug('nnnnnnnnn'+string.valueof(thdsales.Month_Year__c).substring(5,7));
      System.Debug('ccccccccc'+thdsales.Month_Year__c.month());
  }
  //T SO February
  if((thdsales.SFDC_Account_ID__c == accountId) && (thdsales.POS_Order_Type__c == 'Special Order') && 
  thdsales.Month_Year__c.month() == 2) {
      updateAccounts.get(thdsales.SFDC_Account_ID__c).Total_Behr_SO_Sales_February__c += thdsales.Amount__c;      
  }
  //T SO March
  if((thdsales.SFDC_Account_ID__c == accountId) && (thdsales.POS_Order_Type__c == 'Special Order') && 
  thdsales.Month_Year__c.month() == 3) {
      updateAccounts.get(thdsales.SFDC_Account_ID__c).Total_Behr_SO_Sales_March__c += thdsales.Amount__c;      
  }
  //T SO April
  if((thdsales.SFDC_Account_ID__c == accountId) && (thdsales.POS_Order_Type__c == 'Special Order') && 
  thdsales.Month_Year__c.month() == 4) {
      updateAccounts.get(thdsales.SFDC_Account_ID__c).Total_Behr_SO_Sales_April__c += thdsales.Amount__c;      
  }
  //T SO May
  if((thdsales.SFDC_Account_ID__c == accountId) && (thdsales.POS_Order_Type__c == 'Special Order') && 
  thdsales.Month_Year__c.month() == 5) {
      updateAccounts.get(thdsales.SFDC_Account_ID__c).Total_Behr_SO_Sales_May__c += thdsales.Amount__c;      
  }
  //T SO June
  if((thdsales.SFDC_Account_ID__c == accountId) && (thdsales.POS_Order_Type__c == 'Special Order') && 
  thdsales.Month_Year__c.month() == 6) {
      updateAccounts.get(thdsales.SFDC_Account_ID__c).Total_Behr_SO_Sales_June__c += thdsales.Amount__c;      
  }
  //T SO July
  if((thdsales.SFDC_Account_ID__c == accountId) && (thdsales.POS_Order_Type__c == 'Special Order') && 
  thdsales.Month_Year__c.month() == 7) {
      updateAccounts.get(thdsales.SFDC_Account_ID__c).Total_Behr_SO_Sales_July__c += thdsales.Amount__c;      
  }
  //T SO August
  if((thdsales.SFDC_Account_ID__c == accountId) && (thdsales.POS_Order_Type__c == 'Special Order') && 
  thdsales.Month_Year__c.month() == 8) {
      updateAccounts.get(thdsales.SFDC_Account_ID__c).Total_Behr_SO_Sales_August__c += thdsales.Amount__c;      
  }
  //T SO September
  if((thdsales.SFDC_Account_ID__c == accountId) && (thdsales.POS_Order_Type__c == 'Special Order') && 
  thdsales.Month_Year__c.month() == 9) {
      updateAccounts.get(thdsales.SFDC_Account_ID__c).Total_Behr_SO_Sales_September__c += thdsales.Amount__c;      
  }
  //T SO October
  if((thdsales.SFDC_Account_ID__c == accountId) && (thdsales.POS_Order_Type__c == 'Special Order') && 
  thdsales.Month_Year__c.month() == 10) {
      updateAccounts.get(thdsales.SFDC_Account_ID__c).Total_Behr_SO_Sales_October__c += thdsales.Amount__c;      
  }
  //T SO November
  if((thdsales.SFDC_Account_ID__c == accountId) && (thdsales.POS_Order_Type__c == 'Special Order') && 
  thdsales.Month_Year__c.month() == 11) {
      updateAccounts.get(thdsales.SFDC_Account_ID__c).Total_Behr_SO_Sales_November__c += thdsales.Amount__c;      
  }
  //T SO December
  if((thdsales.SFDC_Account_ID__c == accountId) && (thdsales.POS_Order_Type__c == 'Special Order') && 
  thdsales.Month_Year__c.month() == 12) {
      updateAccounts.get(thdsales.SFDC_Account_ID__c).Total_Behr_SO_Sales_December__c += thdsales.Amount__c;      
  }
  
  //T CC January
  if((thdsales.SFDC_Account_ID__c == accountId) && (thdsales.POS_Order_Type__c == 'Cash & Carry') && 
  thdsales.Month_Year__c.month() == 1) {
      updateAccounts.get(thdsales.SFDC_Account_ID__c).Total_Cash_Carry_Sales_January__c += thdsales.Amount__c;      
  }
  //T CC February
  if((thdsales.SFDC_Account_ID__c == accountId) && (thdsales.POS_Order_Type__c == 'Cash & Carry') && 
  thdsales.Month_Year__c.month() == 2) {
      updateAccounts.get(thdsales.SFDC_Account_ID__c).Total_Cash_Carry_Sales_February__c += thdsales.Amount__c;      
  }
  //T CC March
  if((thdsales.SFDC_Account_ID__c == accountId) && (thdsales.POS_Order_Type__c == 'Cash & Carry') && 
  thdsales.Month_Year__c.month() == 3) {
      updateAccounts.get(thdsales.SFDC_Account_ID__c).Total_Cash_Carry_Sales_March__c += thdsales.Amount__c;      
  }
  //T CC April
  if((thdsales.SFDC_Account_ID__c == accountId) && (thdsales.POS_Order_Type__c == 'Cash & Carry') && 
  thdsales.Month_Year__c.month() == 4) {
      updateAccounts.get(thdsales.SFDC_Account_ID__c).Total_Cash_Carry_Sales_April__c += thdsales.Amount__c;      
  }
  //T CC May
  if((thdsales.SFDC_Account_ID__c == accountId) && (thdsales.POS_Order_Type__c == 'Cash & Carry') && 
  thdsales.Month_Year__c.month() == 5) {
      updateAccounts.get(thdsales.SFDC_Account_ID__c).Total_Cash_Carry_Sales_May__c += thdsales.Amount__c;      
  }
  //T CC June
  if((thdsales.SFDC_Account_ID__c == accountId) && (thdsales.POS_Order_Type__c == 'Cash & Carry') && 
  thdsales.Month_Year__c.month() == 6) {
      updateAccounts.get(thdsales.SFDC_Account_ID__c).Total_Cash_Carry_Sales_June__c += thdsales.Amount__c;      
  }
  //T CC July
  if((thdsales.SFDC_Account_ID__c == accountId) && (thdsales.POS_Order_Type__c == 'Cash & Carry') && 
  thdsales.Month_Year__c.month() == 7) {
      updateAccounts.get(thdsales.SFDC_Account_ID__c).Total_Cash_Carry_Sales_July__c += thdsales.Amount__c;      
  }
  //T CC August
  if((thdsales.SFDC_Account_ID__c == accountId) && (thdsales.POS_Order_Type__c == 'Cash & Carry') && 
  thdsales.Month_Year__c.month() == 8) {
      updateAccounts.get(thdsales.SFDC_Account_ID__c).Total_Cash_Carry_Sales_August__c += thdsales.Amount__c;      
  }
  //T CC September
  if((thdsales.SFDC_Account_ID__c == accountId) && (thdsales.POS_Order_Type__c == 'Cash & Carry') && 
  thdsales.Month_Year__c.month() == 9) {
      updateAccounts.get(thdsales.SFDC_Account_ID__c).Total_Cash_Carry_Sales_September__c += thdsales.Amount__c;      
  }
  //T CC October
  if((thdsales.SFDC_Account_ID__c == accountId) && (thdsales.POS_Order_Type__c == 'Cash & Carry') && 
  thdsales.Month_Year__c.month() == 10) {
      updateAccounts.get(thdsales.SFDC_Account_ID__c).Total_Cash_Carry_Sales_October__c += thdsales.Amount__c;      
  }
  //T CC November
  if((thdsales.SFDC_Account_ID__c == accountId) && (thdsales.POS_Order_Type__c == 'Cash & Carry') && 
  thdsales.Month_Year__c.month() == 11) {
      updateAccounts.get(thdsales.SFDC_Account_ID__c).Total_Cash_Carry_Sales_November__c += thdsales.Amount__c;      
  }
  //T CC December
  if((thdsales.SFDC_Account_ID__c == accountId) && (thdsales.POS_Order_Type__c == 'Cash & Carry') && 
  thdsales.Month_Year__c.month() == 12) {
      updateAccounts.get(thdsales.SFDC_Account_ID__c).Total_Cash_Carry_Sales_December__c += thdsales.Amount__c;      
  }
    
    }
    }
  // Update all the accounts with new values.
  Database.update(updateAccounts.values());
}

 

Best Answer chosen by Admin (Salesforce Developers) 
sfdcfoxsfdcfox

You had a loop inside a loop, pointlessly; that was the entire purpose of making a map in memory, which you ignored in your code, so you've got a massive loop that's completely unnecessary. Here's a more optimized type of code:

 

trigger Behr_MonthsRollUp_US on THD_Sales__c (after insert, after update, after delete, after undelete) {
	Map< id, account > updateAccounts = new map< id, account >( );
	if( trigger.old != null )
		for( thd_sales__c record : trigger.old )
			updateAccounts.put( record.sfdc_account__c, null );
	if( trigger.new != null )
		for( thd_sales__c record : trigger.new )
			updateAccounts.put( record.sfdc_account__c, null );
	updateAccounts.remove( null );
	for( id accountId : updateAccounts.keySet( ) )
		updateAccounts.put( accountId, new Account(id=accountId,
		Total_Behr_SO_Sales_January__c=0, Total_Behr_SO_Sales_February__c=0,Total_Behr_SO_Sales_March__c=0,
		Total_Behr_SO_Sales_April__c=0,Total_Behr_SO_Sales_May__c=0,Total_Behr_SO_Sales_June__c=0,
		Total_Behr_SO_Sales_July__c=0,Total_Behr_SO_Sales_August__c=0,Total_Behr_SO_Sales_September__c=0,
		Total_Behr_SO_Sales_October__c=0,Total_Behr_SO_Sales_November__c=0,Total_Behr_SO_Sales_December__c=0,
		Total_Cash_Carry_Sales_January__c=0, Total_Cash_Carry_Sales_February__c=0,Total_Cash_Carry_Sales_March__c=0,
		Total_Cash_Carry_Sales_April__c=0,Total_Cash_Carry_Sales_May__c=0,Total_Cash_Carry_Sales_June__c=0,
		Total_Cash_Carry_Sales_July__c=0,Total_Cash_Carry_Sales_August__c=0,Total_Cash_Carry_Sales_September__c=0,
		Total_Cash_Carry_Sales_October__c=0,Total_Cash_Carry_Sales_November__c=0,Total_Cash_Carry_Sales_December__c=0
		));
	for( 

	// Run an optimized query that looks for all accounts that meet the if/then criteria
	map< string, list< schema.sobject > > fieldMap =
		new map< string, list< schema.sobjectfield > > {
			'Special Order' =>
				new list< schema.sobjectfield > {
					null,
					Account.Total_Behr_SO_Sales_January__c,
					Account.Total_Behr_SO_Sales_February__c,
					Account.Total_Behr_SO_Sales_March__c,
					Account.Total_Behr_SO_Sales_April__c,
					Account.Total_Behr_SO_Sales_May__c,
					Account.Total_Behr_SO_Sales_June__c,
					Account.Total_Behr_SO_Sales_July__c,
					Account.Total_Behr_SO_Sales_August__c,
					Account.Total_Behr_SO_Sales_September__c,
					Account.Total_Behr_SO_Sales_October__c,
					Account.Total_Behr_SO_Sales_November__c,
					Account.Total_Behr_SO_Sales_December__c
				}
			'Cash & Carry' =>
				new list< schema.sobjectfield > {
					null,
					Account.Total_Behr_Cash_Carry_Sales_January__c,
					Account.Total_Behr_Cash_Carry_Sales_February__c,
					Account.Total_Behr_Cash_Carry_Sales_March__c,
					Account.Total_Behr_Cash_Carry_Sales_April__c,
					Account.Total_Behr_Cash_Carry_Sales_May__c,
					Account.Total_Behr_Cash_Carry_Sales_June__c,
					Account.Total_Behr_Cash_Carry_Sales_July__c,
					Account.Total_Behr_Cash_Carry_Sales_August__c,
					Account.Total_Behr_Cash_Carry_Sales_September__c,
					Account.Total_Behr_Cash_Carry_Sales_October__c,
					Account.Total_Behr_Cash_Carry_Sales_November__c,
					Account.Total_Behr_Cash_Carry_Sales_December__c
				}
		};

	for(THD_Sales__c record:[SELECT Id, Month_Year__c, POS_Order_Type__c, Amount__c, SFDC_Account_ID__c
							FROM  THD_Sales__c
							WHERE SFDC_Account_ID__c IN :updateAccounts.keySet( ) AND POS_ORDER_TYPE__C IN ('Special Order','Cash & Carry') AND Month_Year__c != null]){ 
							
		updateAccounts.get( thdsales.sfdc_account_id__c ).put(
			fieldmap.get( thdsales.pos_order_type__c )[thdsales.month_year__c.month()] ),
			( Decimal )( updateAccounts.get( thdsales.sfdc_account_id__c ).get( fieldmap.get( thdsales.pos_order_type__c )[thdsales.month_year__c.month()] ) ) )+
			thdsales.Amount__c );
	}
  // Update all the accounts with new values.
  Database.update(updateAccounts.values());
}

 

Edit: Forgot I changed a map to a list half-way through the code for efficiency.

All Answers

Hengky IlawanHengky Ilawan

Hi,

 

Try to use if then else instead in your loop, it should reduce your running script statements quite a lot.

 

if (thdsales.SFDC_Account_ID__c == accountId) {
    if (thdsales.POS_Order_Type__c == 'Special Order') {
        // T SO January
        if (thdsales.Month_Year__c.month() == 1) {
            ...
        }
        // T SO February
        else if (thdsales.Month_Year__c.month() == 2) {
            ...
        }
    }
    else if (thdsales.POS_Order_Type__c == 'Cash & Carry') {
        ...
    }
}

And btw, why do you have nested "for" loop?

I don't think it is necessary, because you are getting the updateAccountIds from your trigger.new (THD_Sales__c object) and then you re-query the THD_Sales__c object again using the same updateAccountIds. It does not make sense.

 

Regards,

Hengky

sfdcfoxsfdcfox

You had a loop inside a loop, pointlessly; that was the entire purpose of making a map in memory, which you ignored in your code, so you've got a massive loop that's completely unnecessary. Here's a more optimized type of code:

 

trigger Behr_MonthsRollUp_US on THD_Sales__c (after insert, after update, after delete, after undelete) {
	Map< id, account > updateAccounts = new map< id, account >( );
	if( trigger.old != null )
		for( thd_sales__c record : trigger.old )
			updateAccounts.put( record.sfdc_account__c, null );
	if( trigger.new != null )
		for( thd_sales__c record : trigger.new )
			updateAccounts.put( record.sfdc_account__c, null );
	updateAccounts.remove( null );
	for( id accountId : updateAccounts.keySet( ) )
		updateAccounts.put( accountId, new Account(id=accountId,
		Total_Behr_SO_Sales_January__c=0, Total_Behr_SO_Sales_February__c=0,Total_Behr_SO_Sales_March__c=0,
		Total_Behr_SO_Sales_April__c=0,Total_Behr_SO_Sales_May__c=0,Total_Behr_SO_Sales_June__c=0,
		Total_Behr_SO_Sales_July__c=0,Total_Behr_SO_Sales_August__c=0,Total_Behr_SO_Sales_September__c=0,
		Total_Behr_SO_Sales_October__c=0,Total_Behr_SO_Sales_November__c=0,Total_Behr_SO_Sales_December__c=0,
		Total_Cash_Carry_Sales_January__c=0, Total_Cash_Carry_Sales_February__c=0,Total_Cash_Carry_Sales_March__c=0,
		Total_Cash_Carry_Sales_April__c=0,Total_Cash_Carry_Sales_May__c=0,Total_Cash_Carry_Sales_June__c=0,
		Total_Cash_Carry_Sales_July__c=0,Total_Cash_Carry_Sales_August__c=0,Total_Cash_Carry_Sales_September__c=0,
		Total_Cash_Carry_Sales_October__c=0,Total_Cash_Carry_Sales_November__c=0,Total_Cash_Carry_Sales_December__c=0
		));
	for( 

	// Run an optimized query that looks for all accounts that meet the if/then criteria
	map< string, list< schema.sobject > > fieldMap =
		new map< string, list< schema.sobjectfield > > {
			'Special Order' =>
				new list< schema.sobjectfield > {
					null,
					Account.Total_Behr_SO_Sales_January__c,
					Account.Total_Behr_SO_Sales_February__c,
					Account.Total_Behr_SO_Sales_March__c,
					Account.Total_Behr_SO_Sales_April__c,
					Account.Total_Behr_SO_Sales_May__c,
					Account.Total_Behr_SO_Sales_June__c,
					Account.Total_Behr_SO_Sales_July__c,
					Account.Total_Behr_SO_Sales_August__c,
					Account.Total_Behr_SO_Sales_September__c,
					Account.Total_Behr_SO_Sales_October__c,
					Account.Total_Behr_SO_Sales_November__c,
					Account.Total_Behr_SO_Sales_December__c
				}
			'Cash & Carry' =>
				new list< schema.sobjectfield > {
					null,
					Account.Total_Behr_Cash_Carry_Sales_January__c,
					Account.Total_Behr_Cash_Carry_Sales_February__c,
					Account.Total_Behr_Cash_Carry_Sales_March__c,
					Account.Total_Behr_Cash_Carry_Sales_April__c,
					Account.Total_Behr_Cash_Carry_Sales_May__c,
					Account.Total_Behr_Cash_Carry_Sales_June__c,
					Account.Total_Behr_Cash_Carry_Sales_July__c,
					Account.Total_Behr_Cash_Carry_Sales_August__c,
					Account.Total_Behr_Cash_Carry_Sales_September__c,
					Account.Total_Behr_Cash_Carry_Sales_October__c,
					Account.Total_Behr_Cash_Carry_Sales_November__c,
					Account.Total_Behr_Cash_Carry_Sales_December__c
				}
		};

	for(THD_Sales__c record:[SELECT Id, Month_Year__c, POS_Order_Type__c, Amount__c, SFDC_Account_ID__c
							FROM  THD_Sales__c
							WHERE SFDC_Account_ID__c IN :updateAccounts.keySet( ) AND POS_ORDER_TYPE__C IN ('Special Order','Cash & Carry') AND Month_Year__c != null]){ 
							
		updateAccounts.get( thdsales.sfdc_account_id__c ).put(
			fieldmap.get( thdsales.pos_order_type__c )[thdsales.month_year__c.month()] ),
			( Decimal )( updateAccounts.get( thdsales.sfdc_account_id__c ).get( fieldmap.get( thdsales.pos_order_type__c )[thdsales.month_year__c.month()] ) ) )+
			thdsales.Amount__c );
	}
  // Update all the accounts with new values.
  Database.update(updateAccounts.values());
}

 

Edit: Forgot I changed a map to a list half-way through the code for efficiency.

This was selected as the best answer
Vasu@blrVasu@blr

Thank you very much !!!