+ Start a Discussion
madhu reddy 93madhu reddy 93 

Error: System.LimitException: Too many SOQL queries: 101 Triggers

 for(WorkOrderLineItem workLine : [select id, workOrderId from workOrderlineItem where workOrderId IN :WorkOrderIds]){
        mapWorkOrderLineItem.put(workLine.workOrderId, workLine);
    }
    Id ProdId = [select Id, Product2Id, Product2.ProductCode from PriceBookEntry where Name = 'Service Fee' and Id = '01u7F000007Xf5XQAS'].Id;
     system.debug('**********Product2Id*********    '+ProdId);
List<ProductConsumed> prodConsumedList = new List<ProductConsumed>();
    if(Trigger.isupdate && trigger.isafter && firstAfterUpdate)
    {
        
        for(ServiceAppointment svc : [select id, status, Actual_Duration_Hours__c,ParentRecordId from ServiceAppointment where id IN :trigger.new]){
            if(WorkOrderIds.contains(svc.ParentRecordId) && svc.Status =='Completed'){
                system.debug('**********svc1*********    '+svc);
                mapWorkOrderLineItem.get(svc.ParentRecordId).Asset_Title__c='Service Fee';
                    system.debug('**********svc2*********    '+svc);
               ProductConsumed pc = new ProductConsumed();
                pc.QuantityConsumed = svc.Actual_Duration_Hours__c;
                pc.PricebookEntryId = ProdId;
                pc.WorkOrderId=svc.ParentRecordId;
                pc.WorkOrderLineItemId=mapWorkOrderLineItem.get(svc.ParentRecordId).id;
                prodConsumedList.add(pc);
            }
            
        }
         system.debug('**********svc3*********    ');
             update mapWorkOrderLineItem.values(); 
      insert prodConsumedList;
         system.debug('**********svc4*********    ');
        
        
    } 
Danny Sk 6Danny Sk 6
Andrew GAndrew G
Hi Madhu

That chunk of code looks ok structurally.  i.e. no SQL queries in FOR loops. 
Are you able to post the full trigger and/or handler to help troubleshoot?

As an aside, i'm surprised that your query works.
select id, status, Actual_Duration_Hours__c,ParentRecordId from ServiceAppointment where id IN :trigger.new
My understanding is that trigger.new is a list of Objects (related to the trigger) and that matching the Id against a list of objects would not work.
Something like below, may work better.
WHERE Id in :trigger.newMap.keySet()
trigger.map.keySet() will provide a List of Ids for the records invoking the trigger.


Regards

Andrew

 
madhu reddy 93madhu reddy 93
trigger SJAAFSL_ServiceAppointmentTrigger on ServiceAppointment (after insert, after update) {
    
    
    /*if(trigger.isbefore && trigger.isInsert)
* 
{
Trigger.new[0].FSL__Auto_Schedule__c =True;
}*/ 
    boolean firstAfterUpdate= true;
    map<id,WorkOrder> mapWorkOrder = new map<id,WorkOrder>();
    set<id> WorkOrderIds = new set<id>();
    String workOrder_prefix = Schema.SObjectType.WorkOrder.getKeyPrefix();
    map<id,workorderlineitem> mapWorkOrderLineItem = new map<id,workorderlineitem>();

    for(ServiceAppointment objApp : trigger.new)
    {
        if(((String)objApp.ParentRecordId).startsWith(workOrder_prefix))
            WorkOrderIds.add(objApp.ParentRecordId);
    }
    if(trigger.isbefore && trigger.isInsert)
    {
        if(WorkOrderIds!=null && WorkOrderIds.size()>0)
        {
            mapWorkOrder=new map<id,WorkOrder>( [select id,case.no_of_kits__c,case.account.required_for_service__c,case.Earliest_Start_Permitted__c from WorkOrder where id in:WorkOrderIds]);
            if(Trigger.isBefore && trigger.isInsert)
            {
                if(mapWorkOrder!=null)
                {
                    for(ServiceAppointment objApp : trigger.new)
                    {
                        if(mapWorkOrder.get(objApp.ParentRecordId)!=null && mapWorkOrder.get(objApp.ParentRecordId).case!=null)
                        {
                            //Nikhil - Commented as Duration is being calculated on parent WorkOrder & flown on SA record
                            /*Decimal avgDuration = (Decimal)(mapWorkOrder.get(objApp.ParentRecordId).case.no_of_kits__c*15)/60;
if(objApp.duration <avgDuration)
{
objApp.duration=avgDuration;
}*/
                            
                            if(mapWorkOrder.get(objApp.ParentRecordId).case.Earliest_Start_Permitted__c!=null)
                            {
                                objApp.EarliestStartTime = mapWorkOrder.get(objApp.ParentRecordId).case.Earliest_Start_Permitted__c;
                                objApp.DueDate = objApp.EarliestStartTime.addDays(3);
                            }
                        }
                        if(mapWorkOrder.get(objApp.ParentRecordId)!=null && mapWorkOrder.get(objApp.ParentRecordId).case!=null && mapWorkOrder.get(objApp.ParentRecordId).case.account!=null && mapWorkOrder.get(objApp.ParentRecordId).case.account.required_for_service__c!=null && mapWorkOrder.get(objApp.ParentRecordId).case.account.required_for_service__c.contains('Access Form'))
                        {
                            objApp.access_form__c=true;
                        }
                    }
                }
            }
        }
    }
    if(WorkOrderIds!=null && WorkOrderIds.size()>0)
    {
        if(Trigger.isupdate && trigger.isafter && firstAfterUpdate)
        {
            //Nikhil [3/20/2019]- added for passing SA list to send SR emails
            List<ServiceAppointment> saLst = new List<ServiceAppointment>();
            mapWorkOrder=new map<id,WorkOrder>( [select id,case.no_of_kits__c,case.account.required_for_service__c from WorkOrder where id in:WorkOrderIds]);
            if(mapWorkOrder!=null)
            {
                for(ServiceAppointment objApp : trigger.new)
                {
                    if(mapWorkOrder.get(objApp.ParentRecordId)!=null && mapWorkOrder.get(objApp.ParentRecordId).case!=null && mapWorkOrder.get(objApp.ParentRecordId).case.account!=null && mapWorkOrder.get(objApp.ParentRecordId).case.account.required_for_service__c!=null && mapWorkOrder.get(objApp.ParentRecordId).case.account.required_for_service__c.contains('Access Form'))
                    {
                        if(trigger.OldMap.get(objApp.id).status!='Dispatched' && objApp.status =='Dispatched')
                        {
                            if(!objApp.access_form_completed__c)
                            {
                                objApp.Status.addError('Please complete the Access Form');
                            }
                        }
                    }
                    //Nikhil [3/20/2019]- added for passing SA list to send SR emails
                    if(objApp.EmailServiceReport__c){
                        saLst.add(objApp);
                    }
                }
                if(saLst != null && saLst.size() > 0)
                    System.enqueueJob( new SJAAFSL_ServiceReportSendEmail(saLst));
            }
            
            /* List<task> lstTask =[select id,whatid, status from task where whatid in :WorkOrderIds and subject ='Complete Access Form'];
map<id, task> mapTask = new map<id,task>();
List<ServiceAppointment> lstSA = new list<ServiceAppointment>();
for(task taskObj : lstTask)
{
mapTask.put(taskObj.whatid,taskObj);
}
for(ServiceAppointment objApp : trigger.new)
{

if(trigger.OldMap.get(objApp.id).status!='Dispatched' && objApp.status =='Dispatched')
{
if(mapTask!=null && mapTask.get(objApp.ParentRecordId)!=null && mapTask.get(objApp.ParentRecordId).status !='Completed')
{
objApp.Status.addError('Please complete the Access Form');
}
}

}*/
            
             
        }
    }
    
    
    /* SA Closed   */  
    
    /* Madhu [01/01/2019]  update service free and hour in product consumed*/

      for(WorkOrderLineItem workLine : [select id, workOrderId from workOrderlineItem where workOrderId IN :WorkOrderIds]){
        mapWorkOrderLineItem.put(workLine.workOrderId, workLine);
    }
    Id ProdId = [select Id, Product2Id, Product2.ProductCode from PriceBookEntry where Name = 'Service Fee' and Id = '01u7F000007Xf5XQAS'].Id;
     system.debug('**********Product2Id*********    '+ProdId);
List<ProductConsumed> prodConsumedList = new List<ProductConsumed>();
    if(Trigger.isupdate && trigger.isafter && firstAfterUpdate)
    {
        
        for(ServiceAppointment svc : [select id, status, Actual_Duration_Hours__c,ParentRecordId from ServiceAppointment where id IN :trigger.new]){
            if(WorkOrderIds.contains(svc.ParentRecordId) && svc.Status =='Completed'){
                system.debug('**********svc1*********    '+svc);
                mapWorkOrderLineItem.get(svc.ParentRecordId).Asset_Title__c='Service Fee';
                    system.debug('**********svc2*********    '+svc);
               ProductConsumed pc = new ProductConsumed();
                pc.QuantityConsumed = svc.Actual_Duration_Hours__c;
                pc.PricebookEntryId = ProdId;
                pc.WorkOrderId=svc.ParentRecordId;
                pc.WorkOrderLineItemId=mapWorkOrderLineItem.get(svc.ParentRecordId).id;
                prodConsumedList.add(pc);
            }
            
        }
         system.debug('**********svc3*********    ');
             update mapWorkOrderLineItem.values(); 
      insert prodConsumedList;
         system.debug('**********svc4*********    ');
        
        
    } 
   firstAfterUpdate = false;    
}
 
/**
* @author           - Persistent
* @date             - 01/01/2020
* @description      - Trigger Handler for ServiceAppointment which handles all the events
* @modifiedBy       - 
* @modifiedDate     - 
*/
public without sharing class ServiceAppointmentTriggerHandler extends TriggerHandler
{
    /**
    * @author           - Persistent
    * @date             - 01/01/2020
    * @description      - This  handles the after insert trigger event.
    * @param            - None
    * @return           - void
    * @modifiedBy       -  
    * @modifiedDate     - 
    */
    public override void AfterInsert()
    {        
        try
        {
            ServiceAppointmentHelper.createTimeSheetEntry((List<ServiceAppointment>) this.newList);
        }
        catch(Exception ex){this.newList[0].addError('A system error occurred. Please contact your administrator.');}
    }
    
    /**
    * @author           - Persistent
    * @date             - 01/01/2020
    * @description      - This  handles the after update trigger event.
    * @param            - None
    * @return           - void
    * @modifiedBy       - 
    * @modifiedDate     - 
    */
    public override void AfterUpdate()
    {
        try
        {
			ServiceAppointmentHelper.createTimeSheetEntry((List<ServiceAppointment>) this.newList);
        }
        catch(Exception ex){this.newList[0].addError('A system error occurred. Please contact your administrator.' + ex);}
    }
    
    /**
    * @author           - Persistent
    * @date             - 01/01/2020
    * @description      - This  handles the before insert trigger event.
    * @param            - None
    * @return           - void
    * @modifiedBy       - 
    * @modifiedDate     - 
    */
    public override void BeforeInsert()
    {
        try
        {
			
        }
        catch(Exception ex){this.newList[0].addError('A system error occurred. Please contact your administrator.');}
    }
    
    /**
    * @author           - Persistent
    * @date             - 01/01/2020
    * @description      - This  handles the before update trigger event.
    * @param            - None
    * @return           - void
    * @modifiedBy       - 
    * @modifiedDate     - 
    */
    public override void BeforeUpdate()
    {
        try
        {
			
        }
        catch(Exception ex){this.newList[0].addError('A system error occurred. Please contact your administrator.');}
    }
}
public class ServiceAppointmentHelper 
{
    public static void createTimeSheetEntry(List<ServiceAppointment> serviceAppointmentList)
    {
        List<ServiceAppointment> completedAppointmentsList = new List<ServiceAppointment>();
        List<TimeSheet> toBeInserted_TimeSheet = new List<TimeSheet>();
        List<TimeSheetEntry> toBeInserted_TimeSheetEntry = new List<TimeSheetEntry>();
        List<AssignedResource> assignedResourceList = [Select Id, ServiceAppointmentId, ServiceResourceId From AssignedResource Where ServiceAppointmentId in: serviceAppointmentList];
        
        // ServiceAppointment to List of ServiceResource map
        Map<Id, List<Id>> serviceResourceMap = new Map<Id, List<Id>>();
        
        for(AssignedResource assignedResource: assignedResourceList)
        {
            if(!serviceResourceMap.containsKey(assignedResource.ServiceAppointmentId))
            {
                serviceResourceMap.put(assignedResource.ServiceAppointmentId, new List<Id>());
            }
            
            serviceResourceMap.get(assignedResource.ServiceAppointmentId).add(assignedResource.ServiceResourceId);
        }
        
        // Fetching all completed ServiceAppointments
        for(ServiceAppointment serviceAppointment: serviceAppointmentList)
        {
            if(serviceAppointment.Status == 'Completed')
            {
                completedAppointmentsList.add(serviceAppointment);
            }
        }
        
        for(ServiceAppointment serviceAppointment: completedAppointmentsList)
        {
            if(serviceAppointment.Time_Sheet__c != null)
            {
                TimeSheetEntry timeSheetEntry = new TimeSheetEntry();
                timeSheetEntry.StartTime = serviceAppointment.ActualStartTime;
                timeSheetEntry.EndTime = serviceAppointment.ActualEndTime;
                timeSheetEntry.TimeSheetId = serviceAppointment.Time_Sheet__c;
                
                toBeInserted_TimeSheetEntry.add(timeSheetEntry);
            }
            else
            {
                for(Id serviceResource: serviceResourceMap.get(serviceAppointment.Id))
                {
                    TimeSheet timeSheet = new TimeSheet();
                    timeSheet.StartDate = Date.today();
                    timeSheet.EndDate = Date.today();
                    timeSheet.Status = 'New';
                    timeSheet.ServiceResourceId = serviceResource;
                    
                    toBeInserted_TimeSheet.add(timeSheet);
                }                
            }
        }
        
        insert toBeInserted_TimeSheet;
        
        for(ServiceAppointment serviceAppointment: completedAppointmentsList)
        {
            if(serviceAppointment.Time_Sheet__c == null)
            {
                for(TimeSheet timeSheet: toBeInserted_TimeSheet)
                {
                    TimeSheetEntry timeSheetEntry = new TimeSheetEntry();
                    timeSheetEntry.StartTime = serviceAppointment.ActualStartTime;
                    timeSheetEntry.EndTime = serviceAppointment.ActualEndTime;
                    timeSheetEntry.TimeSheetId = timeSheet.Id;
                    
                    toBeInserted_TimeSheetEntry.add(timeSheetEntry);
                }
            }
        }
        
        insert toBeInserted_TimeSheetEntry;
    }
}


Hi Andrew Please check above code thanks for advance for helping
Andrew GAndrew G
Hi

Thanks for the full code.  Its interesting that you have a Trigger Handler class, but don't seem to be invoking it in the trigger.
Anyway, reading the code, the only thing is that as noted yesterday, the SELECT statement using a list of objects.

Try adjusting the code as follows:
 
/* Madhu [01/01/2019]  update service free and hour in product consumed*/

    for(WorkOrderLineItem workLine : [SELECT id, workOrderId FROM workOrderlineItem WHERE workOrderId IN :WorkOrderIds]){
        mapWorkOrderLineItem.put(workLine.workOrderId, workLine);
    }
    Id ProdId = [SELECT Id, Product2Id, Product2.ProductCode FROM PricebookEntry WHERE Name = 'Service Fee' AND Id = '01u7F000007Xf5XQAS'].Id;
    System.debug('**********Product2Id*********    '+ProdId);
    List<Id> saIds = new List<Id>();
    List<ProductConsumed> prodConsumedList = new List<ProductConsumed>();
    if(Trigger.isUpdate && Trigger.isAfter && firstAfterUpdate)
    {
        for(ServiceAppointment sa : Trigger.new){
            saIds.add(sa.Id);
        }
        for(ServiceAppointment svc : [SELECT id, status, Actual_Duration_Hours__c,ParentRecordId FROM ServiceAppointment WHERE id IN :saIds]){
            if(WorkOrderIds.contains(svc.ParentRecordId) && svc.Status =='Completed'){
                System.debug('**********svc1*********    '+svc);
                mapWorkOrderLineItem.get(svc.ParentRecordId).Asset_Title__c='Service Fee';
                System.debug('**********svc2*********    '+svc);
                ProductConsumed pc = new ProductConsumed();
                pc.QuantityConsumed = svc.Actual_Duration_Hours__c;
                pc.PricebookEntryId = ProdId;
                pc.WorkOrderId=svc.ParentRecordId;
                pc.WorkOrderLineItemId=mapWorkOrderLineItem.get(svc.ParentRecordId).id;
                prodConsumedList.add(pc);
            }

        }
        System.debug('**********svc3*********    ');
        update mapWorkOrderLineItem.values();
        insert prodConsumedList;
        System.debug('**********svc4*********    ');


    }
    firstAfterUpdate = false;

Regards
Andrew​​​​​​​