function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Alex Waddell 18Alex Waddell 18 

Running into SOQL Query limit 101 when importing data using data loader

I am running into an error when mass creating assessments using dataloader. 

I have a feeling that I running 3 queries inside of the For() is the cause of my issue. Can someone help me rework this code to avoid these issues moving forward?
 
trigger closeCaseOnIcmDischarge on Patient_Ass__c (After insert) {
    for(Patient_ass__c pAss : trigger.new){
         
         Patient_Assessment_View__c pView = [Select id,Account__c
                                             From Patient_Assessment_View__C
                                             Where id = :pAss.Patient_Assessment_View__c]; 
            
      List <Case> c = [Select id, Discharge_Reason__c, Discharge_Date__c, Accountid
                   From Case
                   Where status IN('New','On Service')
                   AND RecordTypeId = '01236000000OJLq'
                   And Accountid = :Pview.Account__c
                   Limit 1];   
        
     List <TeleWell_Care_Plan__c> TW = [Select id,Use_Question_1__c
                                  From Telewell_Care_Plan__c
                                  Where Patient_Assessment_and_Care_Plan__c = :Pview.id
                                  LIMIT 1] ;
        
        
         if(c.size() > 0 && pAss.RecordTypeId == '01236000000nufHAAQ'){
        c.get(0).status ='Closed';
        c.get(0).Discharge_Reason__c = pAss.Discharge_Reason__c;
        c.get(0).Discharge_Date__c = System.today();
            Update c;
             
        TW.get(0).Use_question_1__c = 'No';
            Update TW; 

        }    
        
                Else{
            
        }
    }
}

 
Best Answer chosen by Alex Waddell 18
Andrew GAndrew G
You are correct, the SQLs in the FOR loop is the issue.

Its a little complex, but basically you need to get the record Lists using the Ids.  Basically, loop the records, get the Ids, do a query (outside the loop) using Ids IN :listOfIds, then loop that list to create another list of Ids, and do the next query, outside the loop.

The following is uncompiled and not tested, but I think it should get you on the correct path:
trigger closeCaseOnIcmDischarge on Patient_Ass__c (After insert) {
    
    Set<Id> pavIds = new Set<Id>();
    List<Patient_Assessment_View__c> pavList = new List<Patient_Assessment_View__c>();

    for(Patient_ass__c pAss : trigger.new){
        pavIds.add(pAss.Patient_Assessment_View__c);
    }
    
    Patient_Assessment_View__c pView = [Select id,Account__c
                                             From Patient_Assessment_View__C
                                             Where id IN :pavIds]; 
    Map<Id,Patient_Assessment_View__c> pViewMap = new Map<Id, Patient_Assessment_View__c>();
    Set<Id> AccountIds = new Set<Id>();
    for( Patient_Assessment_View__c pv : pView ){
        pViewMap.put(pv.Id, pv);
        AccountIds.add(pv.Account__c);
    }

     List <Case> cases = [Select id, Discharge_Reason__c, Discharge_Date__c, Accountid
                   From Case
                   Where status IN('New','On Service')
                   AND RecordTypeId = '01236000000OJLq'
                   And Accountid IN :AccountIds];   
    Map<Id,Case> caseByAccIdMap = new Map<Id,Case>();
    for (Case c : cases ){
        caseByAccIdMap.put(c.AccountId, c);
    }
        
     List <TeleWell_Care_Plan__c> TWs = [Select id,Use_Question_1__c
                                  From Telewell_Care_Plan__c
                                  Where Patient_Assessment_and_Care_Plan__c IN :pavIds] ;

    Map<Id,TeleWell_Care_Plan__c> carePlanByPAVidMap = new Map<Id,TeleWell_Care_Plan__c>();
    for(TeleWell_Care_Plan__c tw : TWs ) {
      carePlanByPAVidMap.put(tw.Patient_Assessment_and_Care_Plan__c, tw);
    }

	List<Case> updateCases = new List<Case>();
    List<TeleWell_Care_Plan__c> updateTWs = new List<TeleWell_Care_Plan__c>();

    for(Patient_ass__c pAss : trigger.new){
	String accId = pViewMap.get(pAss.Id).Account__c;

        Case tempCase = caseByAccIdMap.get(accId);

        if(!Isnull(tempCase) && pAss.RecordTypeId == '01236000000nufHAAQ'){
        tempCase.get(0).status ='Closed';
        tempCase.get(0).Discharge_Reason__c = pAss.Discharge_Reason__c;
        tempCase.get(0).Discharge_Date__c = System.today();
	updateCases.add(tempCase);
             
        }    
    }
    update updateCases;

    for(TeleWell_Care_Plan__c tw: TWs){
        tw.User_question_1__c = 'No';
        updateTWs.add(tw);
    }
    update updateTWs;
}

Note that also you were doing DML events inside the Loops also, which would also breach the governor limits.  For those, add the records to be updated to a list of records, and then update that entire list in one call outside the loop.

Regards

Andrew

All Answers

Andrew GAndrew G
You are correct, the SQLs in the FOR loop is the issue.

Its a little complex, but basically you need to get the record Lists using the Ids.  Basically, loop the records, get the Ids, do a query (outside the loop) using Ids IN :listOfIds, then loop that list to create another list of Ids, and do the next query, outside the loop.

The following is uncompiled and not tested, but I think it should get you on the correct path:
trigger closeCaseOnIcmDischarge on Patient_Ass__c (After insert) {
    
    Set<Id> pavIds = new Set<Id>();
    List<Patient_Assessment_View__c> pavList = new List<Patient_Assessment_View__c>();

    for(Patient_ass__c pAss : trigger.new){
        pavIds.add(pAss.Patient_Assessment_View__c);
    }
    
    Patient_Assessment_View__c pView = [Select id,Account__c
                                             From Patient_Assessment_View__C
                                             Where id IN :pavIds]; 
    Map<Id,Patient_Assessment_View__c> pViewMap = new Map<Id, Patient_Assessment_View__c>();
    Set<Id> AccountIds = new Set<Id>();
    for( Patient_Assessment_View__c pv : pView ){
        pViewMap.put(pv.Id, pv);
        AccountIds.add(pv.Account__c);
    }

     List <Case> cases = [Select id, Discharge_Reason__c, Discharge_Date__c, Accountid
                   From Case
                   Where status IN('New','On Service')
                   AND RecordTypeId = '01236000000OJLq'
                   And Accountid IN :AccountIds];   
    Map<Id,Case> caseByAccIdMap = new Map<Id,Case>();
    for (Case c : cases ){
        caseByAccIdMap.put(c.AccountId, c);
    }
        
     List <TeleWell_Care_Plan__c> TWs = [Select id,Use_Question_1__c
                                  From Telewell_Care_Plan__c
                                  Where Patient_Assessment_and_Care_Plan__c IN :pavIds] ;

    Map<Id,TeleWell_Care_Plan__c> carePlanByPAVidMap = new Map<Id,TeleWell_Care_Plan__c>();
    for(TeleWell_Care_Plan__c tw : TWs ) {
      carePlanByPAVidMap.put(tw.Patient_Assessment_and_Care_Plan__c, tw);
    }

	List<Case> updateCases = new List<Case>();
    List<TeleWell_Care_Plan__c> updateTWs = new List<TeleWell_Care_Plan__c>();

    for(Patient_ass__c pAss : trigger.new){
	String accId = pViewMap.get(pAss.Id).Account__c;

        Case tempCase = caseByAccIdMap.get(accId);

        if(!Isnull(tempCase) && pAss.RecordTypeId == '01236000000nufHAAQ'){
        tempCase.get(0).status ='Closed';
        tempCase.get(0).Discharge_Reason__c = pAss.Discharge_Reason__c;
        tempCase.get(0).Discharge_Date__c = System.today();
	updateCases.add(tempCase);
             
        }    
    }
    update updateCases;

    for(TeleWell_Care_Plan__c tw: TWs){
        tw.User_question_1__c = 'No';
        updateTWs.add(tw);
    }
    update updateTWs;
}

Note that also you were doing DML events inside the Loops also, which would also breach the governor limits.  For those, add the records to be updated to a list of records, and then update that entire list in one call outside the loop.

Regards

Andrew
This was selected as the best answer
Alex Waddell 18Alex Waddell 18
Thank you Andrew! This was very helpful. It gets me on the right track! Alex Waddell Vice President Of Technology 480 720 5699