+ Start a Discussion
Ajith Selvaraj 2Ajith Selvaraj 2 

When I try to update more than 100 records I'm getting error like this Error: System.LimitException: Too many SOQL queries: 101

When I try to update more than 100 records in CommercientsF21__Invoice__c I'm getting error like this Error: System.LimitException: Too many SOQL queries: 101
 and my code is 
public class CommercientSF21InvoiceTriggerHandler1 {
    public static void accountUpdate(List<CommercientSF21__Invoice__c> currentRecordId){
        System.debug(currentRecordId);
        List<Account> accUpdate = new List<Account>();
        List<Account> accountWithoutInvoice = new List<Account>();
        List<CommercientSF21__Invoice__c> invoiceList = [select Id,
                                                         CommercientSF21__IVM_InvoiceDate__c from CommercientSF21__Invoice__c where CommercientSF21__Account__c=:currentRecordId[0].CommercientSF21__Account__c];
        for(Account acc : [select id,Current_Year_Revenue__c,Last_Order_Date__c,
                           Previous_Year_Revenue__c from Account where Id =:currentRecordId[0].CommercientSF21__Account__c]){
                               if(invoiceList.size() == 0){
                                   acc.Current_Year_Revenue__c = NULL;
                                   acc.Lifetime_Value_LTV__c = NULL;
                                   acc.Previous_Year_Revenue__c = NULL;
                                   accountWithoutInvoice.add(acc);
                               }
                               
                           }
        map<id,account> accmap = new map<id,account>();
        accmap.putall(accountWithoutInvoice);
        if(accmap.size()>0){
            update accmap.values();
        }
        Set<Id> currentYearRecordIds = new Set<Id>();
        Set<Id> previousYearRecordIds = new Set<Id>();
        for(integer i=0; i<invoiceList.size(); i++){ 
            Datetime record = invoiceList[i].CommercientSF21__IVM_InvoiceDate__c;
            if(record != NULL){ 
                String yearoftherecord = String.valueOf(record.Year());
                Integer curentYear = Date.Today().Year();
                String currentYearInteger = String.valueOf(curentYear);
                Integer previousyear = curentYear -1;
                String previousyearInteger = String.valueOf(previousyear);
                if(currentYearInteger == yearoftherecord ){
                    currentYearRecordIds.add(invoiceList[i].Id);
                }
                else if(previousyearInteger == yearoftherecord){
                    previousYearRecordIds.add(invoiceList[i].Id);
                }
            }
        }
     //   List<CommercientSF21__Invoice__c> dummy = [SELECT id,CommercientSF21__IVM_InvoiceAmt__c from CommercientSF21__Invoice__c
       //                                            where Id =:currentRecordId[0].CommercientSF21__Account__c];
        List<AggregateResult> currentYearAmountList = [SELECT SUM(CommercientSF21__IVM_InvoiceAmt__c )tot FROM CommercientSF21__Invoice__c WHERE Id= :currentYearRecordIds];
        System.debug(currentYearAmountList);
        List<AggregateResult> previousYearAmountList = [SELECT SUM(CommercientSF21__IVM_InvoiceAmt__c )tot FROM CommercientSF21__Invoice__c WHERE Id= :previousYearRecordIds];
        System.debug(previousYearAmountList);
        List<AggregateResult> totalAmountList = [SELECT SUM(CommercientSF21__IVM_InvoiceAmt__c )tot FROM CommercientSF21__Invoice__c WHERE Id IN : currentRecordId ];
        System.debug(totalAmountList);
        Object sumAmountCurrentYear = currentYearAmountList[0].get('tot');
        Object sumAmountPreviousYear = previousYearAmountList[0].get('tot');
        Object sumAmountTotalYear = totalAmountList[0].get('tot');
        for(Account acc: [select id,Current_Year_Revenue__c,Last_Order_Date__c,
                          Previous_Year_Revenue__c from Account where Id =:currentRecordId[0].CommercientSF21__Account__c] ){
                              acc.Previous_Year_Revenue__c = Integer.valueOf(sumAmountPreviousYear);
                              acc.Current_Year_Revenue__c = Integer.valueOf(sumAmountCurrentYear);
                              acc.Lifetime_Value_LTV__c = Integer.valueOf(sumAmountTotalYear);
                              accUpdate.add(acc);
                          }
        map<id,account> accMapToUpdate = new map<id,account>();
        accMapToUpdate.putall(accUpdate);
        if(accMapToUpdate.size()>0){
            update accMapToUpdate.values();
        }
    
    }
}
ShirishaShirisha (Salesforce Developers) 
Hi Ajith,

Greetings!

I can see that you are using the correct format of SOQL For Loops as suggested here (https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_loops_for_SOQL.htm).

However,I would suggest you to enable the debug logs and capture the code execution to see which part of the code is causing the issue here.Also,please bulkify the Trigger when you are handling with the large data as suggested here (https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_triggers_bestpract.htm).

Kindly mark it as best answer if it helps so that it can help others in the future.

Warm Regards,
Shirisha Pathuri