-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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.