+ Start a Discussion
dbimndbimn 

Hitting Too many query rows: 50001 only when converting lead to company AND opportunity

Hi, could use some thoughts here.  Trying to get a trigger to populate a lookup field when an account record is created or updated.  I have a custom object, Sales_Grid_Assignment__c, for storing zipcodes and related data.  The intent of the trigger is to match the account's zipcode to a custom object record and populate the lookup field with the custom object record id.   Code works great in Sandbox.  In production, if the account record creation is by lead conversion, I get following error, but only if the user wants to create an opportunity upon conversion as well as a new company record.

 

Error: System.LimitException: Too many query rows: 50001 (System Code) Class.AssignSalesGrid.getGrid: line 6, column 1 Trigger.PrimaryAccountTrigger: line 4, column 1

 

I don't have anywhere near 50,000 custom object records, but I tried putting a LIMIT on the query anyway.  No joy.  Anyone see any glaring omissions or mistakes?  Thanks!

 

Here is the code for Trigger and Class...

 

TRIGGER:

trigger PrimaryAccountTrigger on Account (before insert, before update) {
    
 LIST<Account> company = Trigger.new;
 AssignSalesGrid.getGrid(company);

}

 

CLASS:

public class AssignSalesGrid {

    public static void getGrid(LIST<Account> company){
        
        List<Sales_Grid_Assignment__c> salesgrids = new List<Sales_Grid_Assignment__c>();
        salesgrids = [Select Id, Name
                from Sales_Grid_Assignment__c LIMIT 50000];
        
        Map <string,Id> mapGrids = new Map<string,Id>();
            for (Sales_Grid_Assignment__c sg: salesgrids)  {
          
                mapGrids.put(sg.Name,sg.Id);    
            }
    for (Account a :company){
            a.Sales_Grid_Assignment__c = mapGrids.get(a.BillingPostalCode);
        }
        
    }
}

Best Answer chosen by Admin (Salesforce Developers) 
Avidev9Avidev9

Well you were close...

 

public class AssignSalesGrid {

    public static void getGrid(LIST < Account > company) {
        Set < String > codes = new Set < String > ();
        for (Account a: company) {
            codes.add(a.BillingPostalCode);
        }
        List < Sales_Grid_Assignment__c > salesgrids = [Select Id, Name from Sales_Grid_Assignment__c IN: codes];
        Map < string, Id > mapGrids = new Map < string, Id > ();
        for (Sales_Grid_Assignment__c sg: salesgrids) {

            mapGrids.put(sg.Name, sg.Id);
        }
        for (Account a: company) {
            a.Sales_Grid_Assignment__c = mapGrids.get(a.BillingPostalCode);
        }

    }
}

 

All Answers

Avidev9Avidev9

Well you were close...

 

public class AssignSalesGrid {

    public static void getGrid(LIST < Account > company) {
        Set < String > codes = new Set < String > ();
        for (Account a: company) {
            codes.add(a.BillingPostalCode);
        }
        List < Sales_Grid_Assignment__c > salesgrids = [Select Id, Name from Sales_Grid_Assignment__c IN: codes];
        Map < string, Id > mapGrids = new Map < string, Id > ();
        for (Sales_Grid_Assignment__c sg: salesgrids) {

            mapGrids.put(sg.Name, sg.Id);
        }
        for (Account a: company) {
            a.Sales_Grid_Assignment__c = mapGrids.get(a.BillingPostalCode);
        }

    }
}

 

This was selected as the best answer
dbimndbimn

Thanks, Avidev9!  I see, I went at it a bit backwards.  Let me play with your solution and I'll let you know if that did the trick.

Imran MohammedImran Mohammed
I would recommend that still the limit clause be part of the soql on top of what avidev9 mentioned.
dbimndbimn

Yep, did the trick. Just had to add the WHERE clause before the IN.  Thanks!!