Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
AnnaTAnnaT 

System.LimitException: Too many SOQL queries: 101

Could someone help me?

 

I created the following trigger.

 

trigger RFTSetPDStatus on RFT_Category__c (before insert, before update) {
  if (Trigger.isInsert)
  { 
    for(RFT_Category__c pd:Trigger.new)
    {
        pd.PD_Status__c='Open';      
    }
  }
  if (Trigger.isUpdate)
  {
    Integer msExist=null;
    Integer msCnt=null;  
    for(RFT_Category__c pd:Trigger.new)
    {
        msExist=[SELECT COUNT() FROM Ichthys_RFT__c WHERE RFT_Category__r.Id = :pd.Id];
        if(msExist==0)
        {
          pd.PD_Status__c ='Open';
        }
        else
        {
          msCnt = [SELECT COUNT() FROM Ichthys_RFT__c WHERE RFT_Category__r.Id = :pd.Id AND Status__c <>'Completed'];
          if(msCnt==0)
          {
            pd.PD_Status__c ='Close';
          }
          else if(msCnt>0)
          {
            pd.PD_Status__c ='Open';
          }
        }
    }
  }
}

 

When I try to update over 1500 data from Apex Dataloader,

the error "System.LimitException: Too many SOQL queries: 101" occurred.

So I split data into 50 and updated them. 

Is there any way to update over 1500 data by changing this trigger code?

 

Thanks in advance for your support!

 

Anna

Best Answer chosen by Admin (Salesforce Developers) 
jbroquistjbroquist

Since you we're only checking to see if there were any Ichthys_RFT__c records related to the RFT_Category__c this should help you out.

 

trigger RFTSetPDStatus on RFT_Category__c (before insert, before update) 
{
    //instantiate map to hold ichthys records mapped to their related RFT_Category
    Map<Id, Ichthys_RFT__c> ichthysMap = new Map<Id, Ichthys_RFT__c>();
    
    //instantiate map to hold completed ichthys records mapped to their related RFT_Category
    Map<Id, Ichthys_RFT__c> ichthysCompletedMap = new Map<Id, Ichthys_RFT__c>();

    //iterate through all ichthys records and add them to the appropriate maps
    for(Ichthys_RFT__c i : [SELECT Id, Status__c, RFT_Category__c FROM Ichthys_RFT__c WHERE RFT_Category__r.Id IN : Trigger.new.keySet()])
    {
        ichthysMap.put(i.RFT_Category__c, i);

        if(i.Status__c == 'Completed')
            ichthysCompletedMap.put(i.RFT_Category__c, i);
    }

    for(RFT_Category__c pd:Trigger.new)
    {
        //mark the record as open if it has a ichthy record or is newly created
        if(!ichthysMap.containsKey(pd.Id) || Trigger.isInsert)
        {
            pd.PD_Status__c ='Open';
        }
        else
        {
            if(!ichthysCompletedMap.containsKey(pd.Id))
            {
                pd.PD_Status__c ='Close';
            }
            else if(ichthysCompletedMap.containsKey(pd.Id))
            {
                pd.PD_Status__c ='Open';
            }
        }
    }
}

 

Also, its a good rule of thumb to never put a query within a loop. Check out this article from Salesforce on bulkifying your code so you don't run into this problem in the future. http://wiki.developerforce.com/page/Best_Practice:_Bulkify_Your_Code

All Answers

BlobmobBlobmob

You gotta get that SOQL out that for loop.  You ain't gonna be able to do no bulk operations till you do that.  I can't tell all what u doin exactly but maybe be a better way to do them aggregates.
jbroquistjbroquist

Since you we're only checking to see if there were any Ichthys_RFT__c records related to the RFT_Category__c this should help you out.

 

trigger RFTSetPDStatus on RFT_Category__c (before insert, before update) 
{
    //instantiate map to hold ichthys records mapped to their related RFT_Category
    Map<Id, Ichthys_RFT__c> ichthysMap = new Map<Id, Ichthys_RFT__c>();
    
    //instantiate map to hold completed ichthys records mapped to their related RFT_Category
    Map<Id, Ichthys_RFT__c> ichthysCompletedMap = new Map<Id, Ichthys_RFT__c>();

    //iterate through all ichthys records and add them to the appropriate maps
    for(Ichthys_RFT__c i : [SELECT Id, Status__c, RFT_Category__c FROM Ichthys_RFT__c WHERE RFT_Category__r.Id IN : Trigger.new.keySet()])
    {
        ichthysMap.put(i.RFT_Category__c, i);

        if(i.Status__c == 'Completed')
            ichthysCompletedMap.put(i.RFT_Category__c, i);
    }

    for(RFT_Category__c pd:Trigger.new)
    {
        //mark the record as open if it has a ichthy record or is newly created
        if(!ichthysMap.containsKey(pd.Id) || Trigger.isInsert)
        {
            pd.PD_Status__c ='Open';
        }
        else
        {
            if(!ichthysCompletedMap.containsKey(pd.Id))
            {
                pd.PD_Status__c ='Close';
            }
            else if(ichthysCompletedMap.containsKey(pd.Id))
            {
                pd.PD_Status__c ='Open';
            }
        }
    }
}

 

Also, its a good rule of thumb to never put a query within a loop. Check out this article from Salesforce on bulkifying your code so you don't run into this problem in the future. http://wiki.developerforce.com/page/Best_Practice:_Bulkify_Your_Code

This was selected as the best answer
AnnaTAnnaT

Blobmob and jbroquist,

Thank you very much for your support !!!

 

I'll try to write SOQL outside loop from now on.