Apex Code Development You need to sign in to do that
Don't have an account?
Churchill19 20 Query Limit error
Hi,
wonder if you could help with the code below? we have a trigger that works unitl it hit the 20 query limit. I know that the logic need to be done out the for loop but not sure how to code it... can anyone help?
104 trigger FieldVisitTrigger on Field_Visit__c (before update) { if(trigger.isUpdate){ for(Field_Visit__c fieldVisit : trigger.new){ system.debug('############# Field Visit Before Update Trigger'); system.debug('############# Field Visit Validated Field = ' + fieldVisit.Validated__c + '#############'); system.debug('############# Field Visit ID = ' + fieldVisit.Id); String errMsg=''; List <Group> postcode_group_set=new List <Group>(); // Initialise list to contain any matches List <Group> postcode_matches=new List <Group>(); // Process only if Validated and Unallocated if (fieldvisit.validated__c && fieldvisit.job_status__c=='New PDV') { // Check for Site Postcode if (fieldvisit.site_postcode__c==null) { fieldvisit.addError(' No Site Postcode - Unable to assign to queue'); } else { // Calculate postcode prefix name details String postcode_prefix=fieldVisit.Site_Postcode__c.toUpperCase().replaceAll(' ',''); postcode_prefix=postcode_prefix.replaceAll('[0-9][A-Z]{2}$',''); // e.g. B23 String postcode_region=postcode_prefix.replaceAll('[^A-Z]',''); // e.g B // Set starting postcode e.g. B23_postcode_group String postcode_group_name=postcode_prefix+ '_postcode_group'; // e.g. B23_postcode_group // Set selection criteria e.g. All B postcodes with B%_postcode_group String postcode_group_select= postcode_region + '%_postcode_group'; try { // Get postcode list of all possibles postcode_group_set = [select id, name from Group WHERE type='Regular' and name like :postcode_group_select]; // while there are no matches (and there is a group set to check) while (postcode_matches.isEmpty() && postcode_group_set.size()>0) { // Search possible groups adjusting current postcode name postcode_group_name=postcode_prefix+ '_postcode_group'; // iterate group set looking for matches to current name for (group grp : postcode_group_set) { if (grp.name.equals(postcode_group_name)) { postcode_matches.add(grp); } } if (postcode_prefix.equals(postcode_region)) { break; } // reduce prefix e.g. B23 to B2 postcode_prefix=postcode_prefix.replaceAll('[0-9]$',''); } // Error checking if (postcode_matches.size()>1) { errMsg+=' Postcode group "' + postcode_group_name + '" - Duplicate group found.'; } if (postcode_matches.size()==0) { errMsg+=' Postcode group "' + postcode_group_select + '" - Not found.'; } // Get queue id for postcode group List<GroupMember> pg_member = [select GroupId from GroupMember WHERE UserOrGroupId =:postcode_matches.get(0).id]; // Error checking if (pg_member.size()>1) { errMsg+=' Postcode group "' + postcode_matches.get(0).name + '" - Assigned to more than one queue or group.'; } if (pg_member.size()==0) { errMsg+=' Postcode group "' + postcode_matches.get(0).name + '" - Not assigned to queue.'; } if (!errMsg.equals('')) pg_member=null; // Force error if error message // Get queue using queue id Group region_queue; for (GroupMember gm : pg_member) { Group g=[select id, name from Group where type='Queue' and id=:gm.GroupId]; if (g<>null) { region_queue=g; break; } } // Assign record to queue fieldVisit.OwnerId=region_queue.id; System.debug('############# Field Visit Owner Id Updated to ' + fieldVisit.OwnerId); // Move on job status (so future record updates will not trigger) fieldvisit.job_status__c='Issued to Field'; system.debug('############# Field Visit Job Status = ' + fieldvisit.job_status__c); } catch (Exception e) { fieldvisit.addError(errMsg + ' [Error for Site Postcode "' + fieldVisit.Site_Postcode__c + '"]'); /* + ' prefix:'+postcode_prefix + ' grp name:' + postcode_group_name + ' select:'+postcode_group_select + ' matches:'+postcode_matches + ' grp set:' +postcode_group_set);*/ } } } } } }
You need to code your trigger so it processes data and does queries in a batch.
That is, you should avoid putting query and DML calls in any type of loop.
Use loops to organize the data that you need, putting them into collections. So that you can execute your queries to fetch the data for all records in the trigger batch at once.
Here's a simple example of what I mean:
// Don't do it this way. Here I am fetching data for every record in the trigger using a loop.
for (Field_Visit__c fieldvisit : trigger.new) {
// get this object's related data
List<Related_Data__c> relatedDatas = [SELECT Id, Name, Data__c FROM Related_Data__c WHERE Field_Visit__c =:fieldvisit.Id];
// process and do necessary logic
for (Related_Data__c relatedData : relatedDatas) {
relatedData.Data__c = some logic;
}
// update the related data for this field visit
update relatedDatas;
}
Instead do something like this:
// Do it this way instead. Here I am organizing the data I need, then fetching everything I need in one query.
List<Id> fieldvisitIds = new List<Id>();
for (Field_Visit__c fieldvisit : trigger.new) {
// get all the fieldvisit ids and put them in the collection
fieldvisitIds.add(fieldvisit.Id);
}
// get all object's related data in one shot
List<Related_Data__c> relatedDatas = [SELECT Id, Name, Data__c FROM Related_Data__c WHERE Field_Visit__c IN fieldvisitIds];
// process and do necessary logic
for (Related_Data__c relatedData : relatedDatas) {
// logic
relatedData.Data__c = some logic;
// more logic
}
// update all the related data in one shot
update relatedDatas;