Apex Code Development You need to sign in to do that
Don't have an account?
El.H optimisation of bulk insert code
Hello,
First time to post so apologies if this is not in the correct area.
I am working on some code executed in anonymous that will insert some records in a charity's org. The charity has job records, on those jobs exist shifts, and those shifts can have hours, for context.
The problem at the moment is that several jobs exist in the org which have shifts, but the hours were not correctly added, so I created the script below which can create those hour records and successfully copy over all the information that is needed from the shift/job record. However, when I attempt to run this in the live version on a single record (which has 21 shifts) I am getting Error:System.LimitException: Too many SOQL queries:101.
Below is my script:
I believe I need to adjust this so that it is calling fewer queries, but I am not entirely sure what I can do other than put a LIMIT which won't really work for me as I need to run through all of the shifts on a given job record in one go.
When I change the shift query to the following, the script will run fine and insert hours corresponding to the correct shift records:
I am grateful for any assistance on this.
Thank you.
El
First time to post so apologies if this is not in the correct area.
I am working on some code executed in anonymous that will insert some records in a charity's org. The charity has job records, on those jobs exist shifts, and those shifts can have hours, for context.
The problem at the moment is that several jobs exist in the org which have shifts, but the hours were not correctly added, so I created the script below which can create those hour records and successfully copy over all the information that is needed from the shift/job record. However, when I attempt to run this in the live version on a single record (which has 21 shifts) I am getting Error:System.LimitException: Too many SOQL queries:101.
Below is my script:
List<GW_Volunteers__Volunteer_Job__c> badJobs = new list<GW_Volunteers__Volunteer_Job__c>();
badJobs=[SELECT Id FROM GW_Volunteers__Volunteer_Job__c WHERE GW_Volunteers__First_Shift__c!=NULL AND GW_Volunteers__Number_of_Completed_Hours__c=0]; //jobs that have shift with no hr
List<GW_Volunteers__Volunteer_Shift__c> needsHr = new list <GW_Volunteers__Volunteer_Shift__c>();
needsHr=[SELECT Id, GW_Volunteers__Start_Date_Time__c,GW_Volunteers__Duration__c, GW_Volunteers__Volunteer_Job__r.Volunteer__c FROM GW_Volunteers__Volunteer_Shift__c WHERE GW_Volunteers__Volunteer_Job__c in :badJobs]; //shift records with no hrs
for (GW_Volunteers__Volunteer_Shift__c s :needsHr){
DateTime dT = s.GW_Volunteers__Start_Date_Time__c;
Date myDate = date.newinstance(dT.year(), dT.month(), dT.day());
GW_Volunteers__Volunteer_Hours__c hour = new GW_Volunteers__Volunteer_Hours__c(
GW_Volunteers__Contact__c=s.GW_Volunteers__Volunteer_Job__r.Volunteer__c,
GW_Volunteers__Status__c='Completed',
GW_Volunteers__Hours_Worked__c=s.GW_Volunteers__Duration__c,
GW_Volunteers__Volunteer_Job__c=s.GW_Volunteers__Volunteer_Job__c,
GW_Volunteers__Volunteer_Shift__c=s.Id,
GW_Volunteers__Start_Date__c=myDate,
GW_Volunteers__End_Date__c=myDate
);
insert hour;
}
I believe I need to adjust this so that it is calling fewer queries, but I am not entirely sure what I can do other than put a LIMIT which won't really work for me as I need to run through all of the shifts on a given job record in one go.
When I change the shift query to the following, the script will run fine and insert hours corresponding to the correct shift records:
needsHr=[SELECT Id, GW_Volunteers__Start_Date_Time__c,GW_Volunteers__Duration__c, GW_Volunteers__Volunteer_Job__r.Volunteer__c FROM GW_Volunteers__Volunteer_Shift__c WHERE GW_Volunteers__Volunteer_Job__c ='a0T3Y00000SLivnUAD' LIMIT 10];
I am grateful for any assistance on this.
Thank you.
El
You should write a one-time batch class that will process all the records in chunks in order to update all the records. Execute Anonymous window is used for less number of records and noy for all the records. You can find the help to create a batch class in the link given below:
https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_batch_interface.htm
Let me know if you still need any further help or information on this.
Thanks,
Abhishek Bansal.
Thank you! I will give that a try.
Many thanks,
Yael
try this :
List<GW_Volunteers__Volunteer_Job__c> badJobs = new list<GW_Volunteers__Volunteer_Job__c>();
badJobs=[SELECT Id FROM GW_Volunteers__Volunteer_Job__c WHERE GW_Volunteers__First_Shift__c!=NULL AND GW_Volunteers__Number_of_Completed_Hours__c=0]; //jobs that have shift with no hr
List<GW_Volunteers__Volunteer_Shift__c> needsHr = new list <GW_Volunteers__Volunteer_Shift__c>();
needsHr=[SELECT Id, GW_Volunteers__Start_Date_Time__c,GW_Volunteers__Duration__c, GW_Volunteers__Volunteer_Job__r.Volunteer__c FROM GW_Volunteers__Volunteer_Shift__c WHERE GW_Volunteers__Volunteer_Job__c in :badJobs]; //shift records with no hrs
list<GW_Volunteers__Volunteer_Hours__c> vl = new list<GW_Volunteers__Volunteer_Hours__c>();
for (GW_Volunteers__Volunteer_Shift__c s :needsHr)
{
DateTime dT = s.GW_Volunteers__Start_Date_Time__c;
Date myDate = date.newinstance(dT.year(), dT.month(), dT.day());
GW_Volunteers__Volunteer_Hours__c hour = new GW_Volunteers__Volunteer_Hours__c(
GW_Volunteers__Contact__c=s.GW_Volunteers__Volunteer_Job__r.Volunteer__c,
GW_Volunteers__Status__c='Completed',
GW_Volunteers__Hours_Worked__c=s.GW_Volunteers__Duration__c,
GW_Volunteers__Volunteer_Job__c=s.GW_Volunteers__Volunteer_Job__c,
GW_Volunteers__Volunteer_Shift__c=s.Id,
GW_Volunteers__Start_Date__c=myDate,
GW_Volunteers__End_Date__c=myDate);
vl.add(hour);
}
if (vl.size()>0)
insert vl;