-1

I am getting this error Too many SOQL queries: 101 for this trigger when I tried to push it into production. Can anybody please tell what is wrong in the script below. I will really appreciate any help.

Trigger

trigger OpenAgingDetailsCount on NRProducts__c (before update) {

for (NRProducts__c np : Trigger.new) {

  AggregateResult[] results =[SELECT Count(id)total FROM Aging_Detail__c  WHERE  (Status__c='New' OR Status__c='In Process') AND NRProducts__c IN :Trigger.new  ];

  np.Open_Aging_Count__c = (decimal)results [0].get('total'); 

  }
 }

2 Answers 2

3

You need to "bulkify" by moving your query outside fo the FOR loop (otherwise it runs once per record)

trigger OpenAgingDetailsCount on NRProducts__c (before update) {

    Map<Id, NRProducts__c> results = new Map<Id, NRProducts__c>([SELECT Id, (SELECT Id FROM Aging_Details__r WHERE Status__c = 'New' OR Status__c = 'In Process') FROM NRProducts__c WHERE Id IN :Trigger.new]);

    for (NRProducts__c np : Trigger.new) {
        if (results.containsKey(np.Id) && !results.get(np.Id).Aging_Details__r != null) {
            np.Open_Aging_Count__c = results.get(np.Id).Aging_Details__r.size();
        }
    }

}
17
  • Hi Nathan, thank your for your response! I am getting this error now while saving the updated code: Error: Compile Error: Didn't understand relationship 'Aging_Details__r' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names. at line 4 column 62 Any ideas?
    – Carlos
    Apr 13, 2015 at 15:56
  • You'll need to figure out what the relationship name is for Aging Details... Go to the Aging Details object and click on the NR Product lookup field... it will tell you a "Relationship Name"... replace "Aging_Details__r" with the relationship name + "__r" Apr 13, 2015 at 16:00
  • total is not a field on Aging_Details__c Object, I am trying to get the aggregated value so I tried this count(Id)Total and I am getting this error: Error: Compile Error: only root queries support aggregate expressions at line 4 column 62
    – Carlos
    Apr 13, 2015 at 16:02
  • 1
    Ok thank you! I will try your solution and let you know.
    – Carlos
    Apr 13, 2015 at 16:07
  • 1
    the status was 'In Progress' for the Aging_Detail Record my bad :)
    – Carlos
    Apr 13, 2015 at 16:37
1

You need to 'Bulkify' your query, so that the loop through the objects in Trigger.new only does one query. You can find a good resource, here General trigger bulkification - best practices

1
  • Mark, thank you!
    – Carlos
    Apr 13, 2015 at 16:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .