+ Start a Discussion
DMario  LewisDMario Lewis 

How to Avoid DML 50000 LIMIT please help

Im currently writing a trigger handler to add a parent id to an account based on a variable called DUNS Number. When I add a new record I run into a DML Error 50001 limit reached.  

/******************************************************************************************************
 * @NAME         : OnBeforeInsert
 * @DESCRIPTION  : Catch any missing data before inserting a Account record
 * @PARAMETER    : List of Accounts to affect
 ******************************************************************************************************/
    public void BeforeInsert(Account[] newAccounts){
        Map<String, Id> dunsMap = getDUNSMap(newAccounts);
        try{
            FOR(Account acct : newAccounts){
                //acct.Sales_Region__c = region.getCorrectAccountRegionSingle(acct);
                if(acct.Parent_DUNS_Number__c != null){
                    acct.ParentId = dunsMap.get(acct.Parent_DUNS_Number__c);
                }
            }
        }catch(Exception ex){}
    }

 /******************************************************************************************************
 * @NAME         : getDUNSMap
 * @DESCRIPTION  : Gets the Id of the Parent Account based on the DUNS Number
 * @PARAMETER    : Account List to search for
 * @RETURN       : Map<String, Id> of the Queried Parent Account with their duns numbers
 ******************************************************************************************************/   
    private Map<String, Id> getDUNSMap(List<Account> accountList){
        Map<String, Id> dunsMap = new Map<String, Id>();
        
        List<String> parentDunsList = new List<String>();
        
        For(Account a : accountList){ 
            parentDunsList.add(a.Parent_DUNS_Number__c);
        }
        List<Account> parentList =[SELECT id, DUNS_Number__c FROM Account WHERE DUNS_Number__c In : parentDunsList];
        
        FOR(Account a1 : parentList){
            dunsMap.put(a1.DUNS_Number__c, a1.Id);
        }
        
        return dunsMap;
    }
Best Answer chosen by DMario Lewis
Lalit Mistry 21Lalit Mistry 21
Hi DMario,

Looks like you are creating a list of parentDuns without adding a pre-check to it so it could be the case the Parent_DUNS_Number is null and hence when you query for account with matching DUNS_Number you get most which have blank DUNS_Number.

Suggested soln:
Add a null/blank check to Parent_DUNS_Number before adding it to the list.
    if(!String.isBlank(a.Parent_DUNS_Number__c)){
        parentDunsList.add(a.Parent_DUNS_Number__c);
    }
Then add a limit clause to SOQL
List<Account> parentList =[SELECT id, DUNS_Number__c FROM Account WHERE DUNS_Number__c In : parentDunsList LIMIT 50000];

Note: SOQL row limit of 50000 is not specific to one SOQL query but is cummulative of all the rows returned during the execution. So if you have 5 SOQL executing in one transaction and each returns 12000 records, you will still encounter max number of rows 50000 retrieved error.

Mark this as an answer if it solves your problem.

All Answers

NForceNForce
Hi DMario,

# Try to add more filters to the query to get result set less than 50000
OR
# Add 'LIMIT 50000'

Thanks,
NForce
Paul S.Paul S.
DMario - how many records were you attempting to insert when the error occurred?  You should only be getting, at most, one account record returned for each account record in your trigger, right?  Unless there are accounts that share the same DUNS number?
DMario  LewisDMario Lewis
Paul, The error occured when I tried to insert 1 account record. The DUNS number is a unique field. However, there could be multiple accounts with the same parent duns number. 
Paul S.Paul S.
In a vacuum, what you have couldn't have possibly returned more than 50,000 query rows if all you were trying to do was insert one account record.  That one record would have a single value in the Parent_DUNS_Number__c field, which means you'd have only one value in your parentDunsList, which should then only allow you to return one account record given the DUNS number is unique.

My guess is that something elsewhere in the trigger/handler is causing your issue.  Any possibilities there?
Lalit Mistry 21Lalit Mistry 21
Hi DMario,

Looks like you are creating a list of parentDuns without adding a pre-check to it so it could be the case the Parent_DUNS_Number is null and hence when you query for account with matching DUNS_Number you get most which have blank DUNS_Number.

Suggested soln:
Add a null/blank check to Parent_DUNS_Number before adding it to the list.
    if(!String.isBlank(a.Parent_DUNS_Number__c)){
        parentDunsList.add(a.Parent_DUNS_Number__c);
    }
Then add a limit clause to SOQL
List<Account> parentList =[SELECT id, DUNS_Number__c FROM Account WHERE DUNS_Number__c In : parentDunsList LIMIT 50000];

Note: SOQL row limit of 50000 is not specific to one SOQL query but is cummulative of all the rows returned during the execution. So if you have 5 SOQL executing in one transaction and each returns 12000 records, you will still encounter max number of rows 50000 retrieved error.

Mark this as an answer if it solves your problem.
This was selected as the best answer
DMario  LewisDMario Lewis
Thanks Lalit Mistry 21 that solved the problem