+ Start a Discussion
David81David81 

Beginner trigger writer - Too many SOQL queries...

Alright. So I've started writing my own triggers (instead of just editing the ones written by others for us) and I've run into a bit of a quandry. 


UpdateSFDCData: execution of BeforeInsert
caused by: System.Exception: Too many SOQL queries: 21
Trigger.UpdateSFDCData: line 9, column 16

 

I know this has to do with the limit on queries being exceeded in the trigger and the fix somehow involves a list, but I'm not sure how to go about implementing it.

 

Is my code way off base or is it a simple fix?

 

 

trigger UpdateSFDCData on GScore_Live__c (before insert, before update) {

for(GScore_Live__c gs: trigger.new){
String pcode = gs.Name;
String own = gs.Sales_User__c;
Integer mnth = System.Today().Month();
Integer yr = System.Today().Year();
Date today = System.Today();

gs.AcctOpen__c = [select count() from Account where CALENDAR_MONTH(Account_Established__c) = :mnth AND CALENDAR_YEAR(Account_Established__c) = :yr AND Account_Status__c = 'Open' AND Producer_Code__c = :pcode ];
gs.AcctPndg__c = [select count() from Account where Status__c IN ('Waiting Original','Open','In Process','Pending Admin Review','Waiting Internal','App Received','Callbacks') AND Producer_Code__c = :pcode ];
gs.ODTasks__c = [select count() from Task where ActivityDate < :today AND Owner.name = :own];
gs.SFDC_Interactions__c = [select count() from Task where Call_Result__c = 'Significant Interaction' AND CALENDAR_MONTH(ActivityDate) = :mnth AND CALENDAR_YEAR(ActivityDate) = :yr AND Owner.name = :own];


if(gs.Total_Points__c >= 85){
gs.Grade__c = 'A'; }
else if(gs.Total_Points__c >= 65){
gs.Grade__c = 'B'; }
else {
gs.Grade__c = 'C'; }
}
}

 

 

 

WilmerWilmer

Hi David,

 

What I can see on a first sight is that you do 4 queries by every summited record, so, if you load data using Dataloader or something else, or process data in a bulk operation, the Query limit exception would be launched after 5 processed records.

 

Here, you have to ways to solve this issue:

 

1. Limit the trigger action to only one record. (use a condition like  if(trigger.new.size()==1) before the loop FOR you have and close it after it).

 

2. Modify your trigger to work for bulk processing. Using a combination of Maps, Sets, List, etc in order to get the keys you need to query other objects and after that, run the query and process the result acording to each given key.

 

I hope this helps.

 

Regards.

 

Wilmer

David81David81

Wilmer,

 

Thanks for the reply.  I'm only loading about 25 records, but as you said, that will easily push this over the limit. I'm currently looking in to making the trigger work for bulk processing (need to learn how anyway). 

 

Unfortunately I don't have any formal training with this stuff so I'm learning as I go. The community has been a great help so far.

 

As a temporary fix, if I just insert the If statement, will I still be able to load data enmasse? We are sending "live" updates from our phone system through Informatica.

WilmerWilmer

Hi David,

 

If you consider to limit your trigger to work only with one record as I suggested, you'll get no error message when you perform a bulk process, BUT, the problem would be that everytime you do that, no action would be done over any record.

 

What I really suggest, is that you rewrite your trigger code as soon as possible to make it work for bulk processing.

 

Regards,

 

Wilmer

David81David81

I was afraid of that. I guess it's time to dig into this bulkifying process.

 

Any pointers on where to start?

WilmerWilmer

Hi David,

 

I recommend you to look for into Apex documentation, here there are some suggested links:

 

http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_triggers_bulk.htm

 

http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_triggers_bulk_idioms.htm

 

 I hope this info helps you.

 

Regards,

 

 

Wilmer

 

 

 

 

 

David81David81

Ok,

 

So I hope I'm heading down the right path here...

 

I've built lists of certain fields from the updated records. Now I need to go out and do a count() on records that match one of those fields. Can I add values to a list from a count() query?

 

I'm getting an error

Error: Compile Error: Illegal assignment from Integer to LIST:Integer at line 17 column 1

 On this

 

 

 

trigger UpdateSFDCData on GScore_Live__c (before update) { List<Id> gsIds = new List<Id>(); List<String> pcodes = new List<String>(); List<String> user = new List<String>(); Integer mnth = System.Today().Month(); Integer yr = System.Today().Year(); Date today = System.Today(); for(GScore_Live__c gs: trigger.new){ gsIds.add(gs.Id); pcodes.add(gs.Name); user.add(gs.Sales_User__c); } List<Integer> pending = [select count() from Account where Status__c IN ('Waiting Original','Open','In Process','Pending Admin Review','Waiting Internal','App Received','Callbacks') AND Producer_Code__c IN :pcodes ]; //Integer pending = [select count() from Account where Status__c IN ('Waiting Original','Open','In Process','Pending Admin Review','Waiting Internal','App Received','Callbacks') AND Producer_Code__c = :pcode ]; //Integer overdue = [select count() from Task where ActivityDate < :today AND Owner.name = :user]; //Integer si = [select count() from Task where Call_Result__c = 'Significant Interaction' AND CALENDAR_MONTH(ActivityDate) = :mnth AND CALENDAR_YEAR(ActivityDate) = :yr AND Owner.name = :user]; //gs.AcctOpen__c = open; //gs.AcctPndg__c = pending; //gs.ODTasks__c = overdue; //gs.SFDC_Interactions__c = si; //if(gs.Total_Points__c >= 85){ //gs.Grade__c = 'A'; } //else if(gs.Total_Points__c >= 65){ //gs.Grade__c = 'B'; } //else { //gs.Grade__c = 'C'; } }

 I've left the old in, but commented out, for reference of what I'm trying to accomplish.

 

Am I at least barking up the right tree here?

 

Thanks again for your assistance so far.

 

 

WilmerWilmer

Hi David,

 

No, it is not possible, what you have to do, instead of :

 

 

List<Integer> pending = [select count() from Account where Status__c IN ('Waiting Original','Open','In Process','Pending Admin Review','Waiting Internal','App Received','Callbacks') AND Producer_Code__c IN :pcodes ];

 is doing this: 

 

Integer pending = [select count() from Account where Status__c IN ('Waiting Original','Open','In Process','Pending Admin Review','Waiting Internal','App Received','Callbacks') AND Producer_Code__c IN  :pcodes ];

 

Look David,

 

What do you really need to count?

 

Total Accounts for all "Producer_Code__c" processed in transaction? or For Each Producer_Code__c looking for Total Accounts?

 

 

 

Message Edited by Wilmer on 02-11-2010 02:43 PM
Message Edited by Wilmer on 02-11-2010 02:56 PM
David81David81

Won't that just give me one total though? I need to get the total for each pcode individually to load back into the gscore_live__c objects.

 

Sorry to be a bother.

 

What I need to count is Account records that have the Producer_Code__c field matching one of the pcodes in the list and are the Status__c field matching a few options. (e.g. pcode 001 has 24 accounts that are in one of those statuses, pcode 002 has 34 accounts in one of those statuses, etc.)

Message Edited by David81 on 02-11-2010 12:07 PM
WilmerWilmer

Don't worry.

 

Look, I have modified your code and here is my suggested code:

 

NOTE: Make a backup of your code or deactivate your original trigger and create this new one with another name. Also, I recommend you to test it on your Sandbox org as much as possible before deploying it.

 

 

trigger UpdateSFDCData on GScore_Live__c (before update) { Set<String> gsIds = new Set<String>(); Set<String> pcodes = new Set<String>(); Set<String> user = new Set<String>(); Integer mnth = System.Today().Month(); Integer yr = System.Today().Year(); Double dbTMPQuantity = 0; Date today = System.Today(); map<string, double> pending_map = new map<string, double>(); map<string, double> overdue_map = new map<string, double>(); map<string, double> si_map = new map<string, double>(); for(GScore_Live__c gs: trigger.new){ //gsIds.add(gs.Id); // This variable doesn't work later, I suggest to delete it pcodes.add(gs.Name); user.add(gs.Sales_User__c); // initialize maps with every ID having 0 as default pending_map.put(gs.Name, 0); overdue_map.put(gs.Sales_User__c, 0); si_map.put(gs.Sales_User__c, 0); } // Look for every pending Account for(Account PendingAccount : [ select Producer_Code__c from Account where Status__c IN ('Waiting Original','Open','In Process','Pending Admin Review','Waiting Internal','App Received','Callbacks') AND Producer_Code__c IN :pcodes order by Producer_Code__c]){ dbTMPQuantity = pending_map.get(PendingAccount.Producer_Code__c) + 1; pending_map.put(PendingAccount.Producer_Code__c, dbTMPQuantity); } // Look for every Overdue Task for(Task OverDueTask : [select OwnerId, Owner.name from Task where ActivityDate < :today AND Owner.name IN :user order by OwnerId]){ dbTMPQuantity = overdue_map.get(OverDueTask.Owner.name) + 1; overdue_map.put(OverDueTask.Owner.name, dbTMPQuantity); } // Lool for every "si" Task for(Task SI_Task : [select OwnerId, Owner.name from Task where Call_Result__c = 'Significant Interaction' AND CALENDAR_MONTH(ActivityDate) = :mnth AND CALENDAR_YEAR(ActivityDate) = :yr AND Owner.name IN :user order by OwnerId]){ dbTMPQuantity = si_map.get(SI_Task.Owner.name) + 1; si_map.put(SI_Task.Owner.name, dbTMPQuantity); } // Goes around every record again to set the result values for(GScore_Live__c gs: trigger.new){ //gs.AcctOpen__c = open; // "open" variable seems to not exist in this code gs.AcctPndg__c = pending_map.get(gs.Name); gs.ODTasks__c = overdue_map.get(gs.Sales_User__c); gs.SFDC_Interactions__c = si_map.get(gs.Sales_User__c); if(gs.Total_Points__c >= 85){ gs.Grade__c = 'A'; }else if(gs.Total_Points__c >= 65){ gs.Grade__c = 'B'; }else{ gs.Grade__c = 'C'; } } }

 

 

 Check if this fullfills what you need.

 

I hope this helps.

 

Regards,

 

 

Wilmer

 

 

David81David81

Wow. Wilmer. That's amazing. At least from a humble beginner like myself. Thank you so much.

 

I'll be making a study of your code to learn how I can do similar things in the future.

 

Hopefully I'll be going through a proper training regimine very soon and I won't need to bug the community as much. 

 

Thanks again.