Apex Code Development You need to sign in to do that
Don't have an account?
Ben Wild 8 System.LimitException: Too many SOQL queries: 101 Querying FiscalYearSettings in Trigger
I have a trigger that queries the Period object for the FiscalYearSettings to use. This is to allow my trigger to automatically update is the Fiscal Year was ever changed in the Company Profile. The issue I have is that it seems to run an SOQL query for every run of the trigger meaning I exceed the SOQL limit. I've looked at bulkifying it but non of the guides I've read help as I only use the SOQL to get a date. My code is as below;
trigger SetProductQuarter on OpportunityLineItem (before insert, before update)
{
Date FYStartDate = [SELECT FiscalYearSettings.StartDate FROM Period WHERE Type = 'Year' AND StartDate <= TODAY AND EndDate >= TODAY].FiscalYearSettings.StartDate;
for(OpportunityLineItem opitem : Trigger.new){
//Current Date Q1
if(opitem.Date_Today__c < FYStartDate.addMonths(3) && opitem.Date_Today__c > FYStartDate){
if(opitem.ServiceDate < FYStartDate.addMonths(3) && opitem.ServiceDate > FYStartDate)
{
opitem.Product_Quarter__c = 'Q1';
}
else if(opitem.ServiceDate < FYStartDate.addMonths(6) && opitem.ServiceDate > FYStartDate.addMonths(3))
{
opitem.Product_Quarter__c = 'Q2';
}
else if(opitem.ServiceDate < FYStartDate.addMonths(9) && opitem.ServiceDate > FYStartDate.addMonths(6))
{
opitem.Product_Quarter__c = 'Q3';
}
else if(opitem.ServiceDate < FYStartDate.addMonths(12) && opitem.ServiceDate > FYStartDate.addMonths(9))
{
opitem.Product_Quarter__c = 'Q4';
}
else
{
opitem.Product_Quarter__c = '=';
}
}
//Current Date Q2
else if(opitem.Date_Today__c < FYStartDate.addMonths(6) && opitem.Date_Today__c > FYStartDate.addMonths(3)){
if(opitem.ServiceDate < FYStartDate.addMonths(15) && opitem.ServiceDate > FYStartDate.addMonths(12))
{
opitem.Product_Quarter__c = 'Q1';
}
else if(opitem.ServiceDate < FYStartDate.addMonths(6) && opitem.ServiceDate > FYStartDate.addMonths(3))
{
opitem.Product_Quarter__c = 'Q2';
}
else if(opitem.ServiceDate < FYStartDate.addMonths(9) && opitem.ServiceDate > FYStartDate.addMonths(6))
{
opitem.Product_Quarter__c = 'Q3';
}
else if(opitem.ServiceDate < FYStartDate.addMonths(12) && opitem.ServiceDate > FYStartDate.addMonths(9))
{
opitem.Product_Quarter__c = 'Q4';
}
else if(opitem.ServiceDate < FYStartDate.addMonths(3) && opitem.ServiceDate > FYStartDate)
{
opitem.Product_Quarter__c = 'PQ1';
}
else
{
opitem.Product_Quarter__c = '=';
}
}
//Current Date Q3
else if(opitem.Date_Today__c < FYStartDate.addMonths(9) && opitem.Date_Today__c > FYStartDate.addMonths(6)){
if(opitem.ServiceDate < FYStartDate.addMonths(15) && opitem.ServiceDate > FYStartDate.addMonths(12))
{
opitem.Product_Quarter__c = 'Q1';
}
else if(opitem.ServiceDate < FYStartDate.addMonths(18) && opitem.ServiceDate > FYStartDate.addMonths(15))
{
opitem.Product_Quarter__c = 'Q2';
}
else if(opitem.ServiceDate < FYStartDate.addMonths(9) && opitem.ServiceDate > FYStartDate.addMonths(6))
{
opitem.Product_Quarter__c = 'Q3';
}
else if(opitem.ServiceDate < FYStartDate.addMonths(12) && opitem.ServiceDate > FYStartDate.addMonths(9))
{
opitem.Product_Quarter__c = 'Q4';
}
else if(opitem.ServiceDate < FYStartDate.addMonths(3) && opitem.ServiceDate > FYStartDate)
{
opitem.Product_Quarter__c = 'PQ1';
}
else if(opitem.ServiceDate < FYStartDate.addMonths(6) && opitem.ServiceDate > FYStartDate.addMonths(3))
{
opitem.Product_Quarter__c = 'PQ2';
}
else
{
opitem.Product_Quarter__c = '=';
}
}
//Current Date Q4
else if(opitem.Date_Today__c < FYStartDate.addMonths(12) && opitem.Date_Today__c > FYStartDate.addMonths(9)){
if(opitem.ServiceDate < FYStartDate.addMonths(15) && opitem.ServiceDate > FYStartDate.addMonths(12))
{
opitem.Product_Quarter__c = 'Q1';
}
else if(opitem.ServiceDate < FYStartDate.addMonths(18) && opitem.ServiceDate > FYStartDate.addMonths(15))
{
opitem.Product_Quarter__c = 'Q2';
}
else if(opitem.ServiceDate < FYStartDate.addMonths(21) && opitem.ServiceDate > FYStartDate.addMonths(18))
{
opitem.Product_Quarter__c = 'Q3';
}
else if(opitem.ServiceDate < FYStartDate.addMonths(12) && opitem.ServiceDate > FYStartDate.addMonths(9))
{
opitem.Product_Quarter__c = 'Q4';
}
else if(opitem.ServiceDate < FYStartDate.addMonths(3) && opitem.ServiceDate > FYStartDate)
{
opitem.Product_Quarter__c = 'PQ1';
}
else if(opitem.ServiceDate < FYStartDate.addMonths(6) && opitem.ServiceDate > FYStartDate.addMonths(3))
{
opitem.Product_Quarter__c = 'PQ2';
}
else if(opitem.ServiceDate < FYStartDate.addMonths(9) && opitem.ServiceDate > FYStartDate.addMonths(6))
{
opitem.Product_Quarter__c = 'PQ3';
}
else
{
opitem.Product_Quarter__c = '=';
}
}
}
}
The following error appears when you exceed the Execution Governors Limit (you can run up to a total 100 SOQL queries in a single call or context).
System.LimitException: Too many SOQL queries: 101 error.
- https://help.salesforce.com/articleView?id=000181404&type=1
- https://developer.salesforce.com/forums/?id=906F0000000MKFnIAO
let us know if it helps.Please mark it as best answer if the information is informative.so that question is removed from an unanswered question and appear as a proper solution.
Thanks
Rahul Kumar
It seems like some code is calling this trigger more than 100 times. There is only 1 query in this trigger, so, I think calling class/method is invoking more than 100 times. Try to bulkify calling method.