You need to sign in to do that
Don't have an account?
Cris9931
too many soql queries : 201
Can anyone help me why I have this error: Too many soql queries inside my code?
for(SVMXC__SVMX_Event__c svEv: listSvmxEvents ) { String key = '' + svEv.SVMXC__Technician__r.SVMXC__Salesforce_User__c + svEv.SVMXC__StartDateTime__c.date(); SVMXC__Timesheet_Day_Entry__c tde = dayEntries.get(key); Map<ID, ID> technicianTImesheet = new Map<Id,ID>(); technicianTImesheet.put(svEv.Id, svEv.SVMXC__Technician__r.SVMXC__Salesforce_User__c); //if there are no timesheet daily summary create them -> Start if(tde == null) { List<SVMXC__Timesheet__c> latestTimesheet = [SELECT Id,SVMXC__User__c,Name,SVMXC__Start_Date__c, Day_of_the_week__c FROM SVMXC__Timesheet__c WHERE SVMXC__User__c =: technicianTImesheet.values() ORDER BY SVMXC__Start_Date__c DESC LIMIT 1]; String technicianSalesforceId = svEv.SVMXC__Technician__r.SVMXC__Salesforce_User__c; Date dateServiceMaxEvent = svEv.SVMX_PS_Customer_Start_Date__c; String dayOftheWeek = svEv.Day_of_the_week__c; for(SVMXC__Timesheet__c tm : latestTimesheet){ if(dayOftheWeek == 'Monday' && tm.Day_of_the_week__c == 'Monday') { System.debug('Monday'); SVMXC__Timesheet__c newTimesheet = new SVMXC__Timesheet__c(); newTimesheet.SVMXC__User__c = technicianSalesforceId; newTimesheet.SVMXC__Start_Date__c = dateServiceMaxEvent; newTimesheet.SVMXC__End_Date__c = newTimesheet.SVMXC__Start_Date__c +6; List<SVMXC__Timesheet__c> existingTimesheet = [SELECT Id,SVMXC__Start_Date__c, SVMXC__End_Date__c FROM SVMXC__Timesheet__c WHERE SVMXC__Start_Date__c =: newTimesheet.SVMXC__Start_Date__c AND SVMXC__User__c =: technicianSalesforceId] if(existingTimesheet.size() == 0) { insert newTimesheet; } } } for(SVMXC__Timesheet__c tm : latestTimesheet){ if(dayOftheWeek == 'Tuesday' && tm .Day_of_the_week__c == 'Monday') { System.debug('Tuesday ' +svEv.Id ); SVMXC__Timesheet__c newTimesheet = new SVMXC__Timesheet__c(); newTimesheet.SVMXC__User__c = technicianSalesforceId; newTimesheet.SVMXC__Start_Date__c = dateServiceMaxEvent - 1; System.debug('newTimesheet.SVMXC__Start_Date__c dateServiceMaxEvent - 1' + newTimesheet.SVMXC__Start_Date__c); newTimesheet.SVMXC__End_Date__c = newTimesheet.SVMXC__Start_Date__c +6; List<SVMXC__Timesheet__c> existingTimesheet = [SELECT Id,SVMXC__Start_Date__c, SVMXC__End_Date__c FROM SVMXC__Timesheet__c WHERE SVMXC__Start_Date__c =: newTimesheet.SVMXC__Start_Date__c AND SVMXC__User__c =: technicianSalesforceId]; if(existingTimesheet.size() == 0) { insert newTimesheet; } } } for(SVMXC__Timesheet__c tm : latestTimesheet){ if(dayOftheWeek == 'Wednesday' && tm .Day_of_the_week__c == 'Monday') { SVMXC__Timesheet__c newTimesheet = new SVMXC__Timesheet__c(); newTimesheet.SVMXC__User__c = technicianSalesforceId; newTimesheet.SVMXC__Start_Date__c = dateServiceMaxEvent - 2; newTimesheet.SVMXC__End_Date__c = newTimesheet.SVMXC__Start_Date__c +6; List<SVMXC__Timesheet__c> existingTimesheet = [SELECT Id,SVMXC__Start_Date__c, SVMXC__End_Date__c FROM SVMXC__Timesheet__c WHERE SVMXC__Start_Date__c =: newTimesheet.SVMXC__Start_Date__c AND SVMXC__User__c =: technicianSalesforceId]; if(existingTimesheet.size() == 0) { insert newTimesheet; } } } for(SVMXC__Timesheet__c tm : latestTimesheet){ if(dayOftheWeek == 'Thursday' && tm .Day_of_the_week__c == 'Monday') { SVMXC__Timesheet__c newTimesheet = new SVMXC__Timesheet__c(); newTimesheet.SVMXC__User__c = technicianSalesforceId; newTimesheet.SVMXC__Start_Date__c = dateServiceMaxEvent - 3; newTimesheet.SVMXC__End_Date__c = newTimesheet.SVMXC__Start_Date__c +6; List<SVMXC__Timesheet__c> existingTimesheet = [SELECT Id,SVMXC__Start_Date__c, SVMXC__End_Date__c FROM SVMXC__Timesheet__c WHERE SVMXC__Start_Date__c =: newTimesheet.SVMXC__Start_Date__c AND SVMXC__User__c =: technicianSalesforceId]; if(existingTimesheet.size() == 0) { insert newTimesheet; } } } for(SVMXC__Timesheet__c tm: latestTimesheet){ if(dayOftheWeek == 'Friday' && tm.Day_of_the_week__c == 'Monday') { SVMXC__Timesheet__c newTimesheet = new SVMXC__Timesheet__c(); newTimesheet.SVMXC__User__c = technicianSalesforceId; newTimesheet.SVMXC__Start_Date__c = dateServiceMaxEvent - 4; newTimesheet.SVMXC__End_Date__c = newTimesheet.SVMXC__Start_Date__c +6; List<SVMXC__Timesheet__c> existingTimesheet = [SELECT Id,SVMXC__Start_Date__c, SVMXC__End_Date__c FROM SVMXC__Timesheet__c WHERE SVMXC__Start_Date__c =: newTimesheet.SVMXC__Start_Date__c AND SVMXC__User__c =: technicianSalesforceId] if(existingTimesheet.size() == 0) { insert newTimesheet; } } } for(SVMXC__Timesheet__c tm : latestTimesheet){ if(dayOftheWeek == 'Saturday' && tm .Day_of_the_week__c == 'Monday') { SVMXC__Timesheet__c newTimesheet = new SVMXC__Timesheet__c(); newTimesheet.SVMXC__User__c = technicianSalesforceId; newTimesheet.SVMXC__Start_Date__c = dateServiceMaxEvent - 5; newTimesheet.SVMXC__End_Date__c = newTimesheet.SVMXC__Start_Date__c +6; List<SVMXC__Timesheet__c> existingTimesheet = [SELECT Id,SVMXC__Start_Date__c, SVMXC__End_Date__c FROM SVMXC__Timesheet__c WHERE SVMXC__Start_Date__c =: newTimesheet.SVMXC__Start_Date__c AND SVMXC__User__c =: technicianSalesforceId] if(existingTimesheet.size() == 0) { insert newTimesheet; } } } for(SVMXC__Timesheet__c tm : latestTimesheet){ if(dayOftheWeek == 'Sunday' && tm.Day_of_the_week__c == 'Monday') { SVMXC__Timesheet__c newTimesheet = new SVMXC__Timesheet__c(); newTimesheet.SVMXC__User__c = technicianSalesforceId; newTimesheet.SVMXC__Start_Date__c = dateServiceMaxEvent - 6; newTimesheet.SVMXC__End_Date__c = newTimesheet.SVMXC__Start_Date__c +6; List<SVMXC__Timesheet__c> existingTimesheet = [SELECT Id,SVMXC__Start_Date__c, SVMXC__End_Date__c FROM SVMXC__Timesheet__c WHERE SVMXC__Start_Date__c =: newTimesheet.SVMXC__Start_Date__c AND SVMXC__User__c =: technicianSalesforceId]; if(existingTimesheet.size() == 0) { insert newTimesheet; } } } }
The error code is happening here:
List<SVMXC__Timesheet__c> existingTimesheet = [SELECT Id,SVMXC__Start_Date__c, SVMXC__End_Date__c FROM SVMXC__Timesheet__c WHERE SVMXC__Start_Date__c =: newTimesheet.SVMXC__Start_Date__c AND SVMXC__User__c =: technicianSalesforceId];
You need to collect all the criteria for the SOQL queries up front, then run the queries one time and deal with the results within your loop.
Also, you should not perform an insert within the loop either. Collect all the new records in a list and insert the whole list at the end of the process.
Frankly, I'm not sure of the whole picture or structure of your timesheets and day entries is here, the relationships between the Start_Date, Customer_Start_Date, and Day_of_the_Week fields. A key to understanding how to rewrite this may be in how you're building the "dayEntries" map.
Maybe start off with something like this to build a map of timesheets by user (latest only) & user + start date: (!!! untested code !!!)
Then as your processing listSvmxEvents you can test for the latest timesheet existing for a user with this:
And before inserting a new timesheet you can see if it exists already with something like this, and add it to the list of new timesheets to insert:
Then at the end, insert all the new timesheets at once:
Good luck!
Question, what you mean by ts o this line?