Apex Code Development You need to sign in to do that
Don't have an account?
swapna9 How to solve Too many SOQL queries: 101
Hi,
I am new to set & map methods. I have written a trigger as below but it throws me an error of Too many SOQL queries:101.
I read somewhere that I can overcome this by using map methods. Can anybody help me with this?
trigger TrainingShedulerNew on Revenue_Schedule__c (before update) {
List<Training_Schedule__c> Told = new list<Training_Schedule__c>();
List<Revenue_Schedule__c > RNext = new list<Revenue_Schedule__c >();
List<double> training = new List<double>();
List<double> production = new List<double>();
integer count,j;
Training_schedule__c Tn = new Training_Schedule__c();
for(Revenue_Schedule__c R : trigger.New)
{
Revenue_Schedule__c beforeUpdate = System.Trigger.oldMap.get(R.Id);
{
If ((R.No_of_FTE_s_for_Training__c!=beforeUpdate.No_of_FTE_s_for_Training__c)||(R.weeks__c!=beforeUpdate.weeks__c))
{
Told = [select id from Training_schedule__c where Related_Revenue_Schedule_Id__c=:R.id];
if(Told.size()>0)
delete Told;
double weeks=R.weeks__c;
if(weeks!=null)
{
training.clear();
if(weeks==1)
{
training.add(0.25);
production.add(0.75);
}
else if(weeks==2)
{
training.add(0.50);
production.add(0.50);
}
else if(weeks==3)
{
training.add(0.75);
production.add(0.25);
}
else if(weeks==4)
{
training.add(1);
production.add(0.0);
}
}
Rnext=[select id, price_book_name__r.id,No_of_FTE_s_for_Training__c from Revenue_Schedule__c where price_book_name__c=:r.price_book_name__c and date__c>=:r.date__c order by date__c limit 1];
for(count=0,j=0; j<Rnext.size(); j++)
{
if(R.No_of_FTE_s_for_Training__c > 0)
{
double tt=0;
double pp=0;
double tFee;
double pFee;
double fee;
double feee;
integer size;
size=training.size();
if(count<training.size())
tt =training[count];
else
tt=0;
if(tt<1)
pp=r.No_of_FTE_s_for_Training__c;
else
pp=0;
Tn = new Training_Schedule__c(Revenue_Schedule__c= RNext[j].id,
price_book__c=RNext[j].price_book_name__r.id,
No_of_FTE_for_Training__c=R.No_of_FTE_s_for_Training__c ,
training__c=tt,
Related_Opportunity_Name__c=R.Opportunity_ID__c,
No_of_FTE_for_Production__c=size,
production__c=1-tt,
Related_Revenue_Schedule_Id__c=R.id
// Date_by_Sales__c=R.Date__c
);
insert Tn;
}
count=count+1;
}}
}}
for(Revenue_Schedule__c Rs : trigger.New)
{
List<training_schedule__c> ts= new list<training_schedule__c>();
List<revenue_schedule__c> rs1= new list<revenue_schedule__c>();
ts=[select id, amount__c from training_schedule__c where price_book__c=:rs.price_book_name__c];
integer size=ts.size();
double amount=0;
for(integer i=0; i<size;i++)
{
amount=amount+ts[i].amount__c;
}
rs.Maximum_amount__c=amount;
}
}
Thanks in advance
Hi Swapna,
Try the following code, i hope this will resolve all your issues:
List<String> priceBookNames = new List<String>();
for(Revenue_Schedule__c R : trigger.New) {
priceBookNames.add(R.price_book_name__c);
}
List<Revenue_Schedule__c> revenueSchedules = [select id, price_book_name__r.id,No_of_FTE_s_for_Training__c, date__c, price_book_name__c from Revenue_Schedule__c where price_book_name__c IN :priceBookNames order by date__c limit 1];
Map<String, List<Revenue_Schedule__c>> revenueSchedulesMap = new Map<String, List<Revenue_Schedule__c>>();
List<Revenue_Schedule__c> revenueScheduleList;
for(Revenue_Schedule__c revenueSchedule : revenueSchedules) {
revenueScheduleList = revenueSchedulesMap.get(revenueSchedule.price_book_name__c);
if(revenueScheduleList == null) {
revenueScheduleList = new List<Revenue_Schedule__c>();
revenueSchedulesMap.put(revenueSchedule.price_book_name__c, revenueScheduleList);
}
revenueScheduleList.add(revenueSchedule);
}
List<Revenue_Schedule__c > RNext; = new list<Revenue_Schedule__c>();
List<training_schedule__c> ts;
for(Revenue_Schedule__c R : trigger.New) {
RNext = new list<Revenue_Schedule__c>();
revenueScheduleList = revenueSchedulesMap.get(R.price_book_name__c);
ts = revenueScheduleList;
for(Revenue_Schedule__c revenueSchedule : revenueScheduleList) {
if(revenueSchedule.date__c >= R.date__c) {
Rnext.add(revenueSchedule);
break;
}
}
...................
...................
}
and please mark this post as solution if this answers to your question.
All Answers
Hi Swapna,
you are executing the SOQL queries in the for loop ... that's why it is giving error. You can use list & maps to get rid of this. e.g. you have written this SOQL in for loop:
"select id from Training_schedule__c where Related_Revenue_Schedule_Id__c=:R.id"
instead you can achieve the same by following code:
List<Id> revScheIds = new List<Id>();
for(Revenue_Schedule__c R : trigger.New) {
revScheIds.add(R.Id);
}
List<Training_Schedule__c> Told = [select id from Training_schedule__c where Related_Revenue_Schedule_Id__c IN :revScheIds ];
You have to modify your code for other SOQL queries in for loop as well.
Hopefully this will solve your issue, let me know if you any questions.
Use following link to understand the best practice of writing triggers
http://wiki.developerforce.com/page/Apex_Code_Best_Practices
If a reply to a post answers your question or resolves your problem, please mark it as the solution to the post so that others may benefit.
Thanks for ur reply,
I modified the code as below.
trigger TrainingShedulerNewOne on Revenue_Schedule__c (before update) {
List<Revenue_Schedule__c > RNext = new list<Revenue_Schedule__c >();
List<double> training = new List<double>();
List<double> production = new List<double>();
integer count,j;
Training_schedule__c Tn = new Training_Schedule__c();
List<Id> revScheIds = new List<Id>();
for(Revenue_Schedule__c R : trigger.New) {
revScheIds.add(R.Id);
}
List<Training_Schedule__c> Told = [select id from Training_schedule__c where Related_Revenue_Schedule_Id__c IN :revScheIds ];
if(Told.size()>0)
delete Told;
for(Revenue_Schedule__c R : trigger.New)
{
Revenue_Schedule__c beforeUpdate = System.Trigger.oldMap.get(R.Id);
{
If ((R.No_of_FTE_s_for_Training__c!=beforeUpdate.No_of_FTE_s_for_Training__c)||(R.weeks__c!=beforeUpdate.weeks__c))
{
double weeks=R.weeks__c;
if(weeks!=null)
..........................
...........................
}
}
}
}
How to put these queries out side of the loop
1.Rnext=[select id, price_book_name__r.id,No_of_FTE_s_for_Training__c from Revenue_Schedule__c where price_book_name__c=:r.price_book_name__c and date__c>=:r.date__c order by date__c limit 1];
2.ts=[select id, amount__c from training_schedule__c where price_book__c=:rs.price_book_name__c];
There is a way to avoid query inside the loop. Refer the link in my previous post.
If a reply to a post answers your question or resolves your problem, please mark it as the solution to the post so that others may benefit.
Hi Swapna,
Try the following code, i hope this will resolve all your issues:
List<String> priceBookNames = new List<String>();
for(Revenue_Schedule__c R : trigger.New) {
priceBookNames.add(R.price_book_name__c);
}
List<Revenue_Schedule__c> revenueSchedules = [select id, price_book_name__r.id,No_of_FTE_s_for_Training__c, date__c, price_book_name__c from Revenue_Schedule__c where price_book_name__c IN :priceBookNames order by date__c limit 1];
Map<String, List<Revenue_Schedule__c>> revenueSchedulesMap = new Map<String, List<Revenue_Schedule__c>>();
List<Revenue_Schedule__c> revenueScheduleList;
for(Revenue_Schedule__c revenueSchedule : revenueSchedules) {
revenueScheduleList = revenueSchedulesMap.get(revenueSchedule.price_book_name__c);
if(revenueScheduleList == null) {
revenueScheduleList = new List<Revenue_Schedule__c>();
revenueSchedulesMap.put(revenueSchedule.price_book_name__c, revenueScheduleList);
}
revenueScheduleList.add(revenueSchedule);
}
List<Revenue_Schedule__c > RNext; = new list<Revenue_Schedule__c>();
List<training_schedule__c> ts;
for(Revenue_Schedule__c R : trigger.New) {
RNext = new list<Revenue_Schedule__c>();
revenueScheduleList = revenueSchedulesMap.get(R.price_book_name__c);
ts = revenueScheduleList;
for(Revenue_Schedule__c revenueSchedule : revenueScheduleList) {
if(revenueSchedule.date__c >= R.date__c) {
Rnext.add(revenueSchedule);
break;
}
}
...................
...................
}
and please mark this post as solution if this answers to your question.
Thank you very much....
I solved that issue.
Hi Swapna,
If a reply to a post answers your question or resolves your problem, please mark it as the solution to the post so that others may benefit.