function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
sparc1.3696571782628958E12sparc1.3696571782628958E12 

System.LimitException: Too many SOQL queries: 101

Hi All

 

I have a custom field named "Unit__c" in Accounts (standard object). I have a similar field "Unit__c" in Opportunity as well.

 

I now have a custom field named "Account_Name__c" in Opportunity (Not using the standard lookup, relating my Opportunity to Accounts for the fact that, Apex Data Loader requires the 15 digit ID while inserting, which I dont have in my .CSV file before hand).

 

What I want to do is that once I get the Account Name in my custom text field "Account_Name__c" in the Opportunity object, I wish to compare the text value with the Account Name value for those present inside the Standard Account Object. If the value is found, I want to return the corresponding "Unit__c" value for that account to the "Unit__c" value inside the opportunity. 

 

For this I have written the following trigger code:

 

trigger setUnit on Opportunity (Before Insert, Before Update) {

for(Opportunity o:trigger.new)
    {
    string Acct_Name = o.Account_Name__c;
    
    List<Account> acc = [SELECT Name,Unit__c FROM Account WHERE Name=:Acct_Name];
        
    if(acc.size() > 0 && acc[0].Unit__c <> NULL)
            o.Unit__c = acc[0].Unit__c;        
    }

}

The code for the trigger works fine when I am creating new opportunities inside salesforce.

 

But, when I insert multiple records through APEX Data Loader, it throws System.LimitException: Too many SOQL queries: 101 error.

 

Please help asap.

Best Answer chosen by Admin (Salesforce Developers) 
Avidev9Avidev9

The problem is you are having an SOQL inside a FOR loop.

This is not a good practice to follow.

 

I will suggest you to tweak ur code a lil. Something like this

 

trigger setUnit on Opportunity(Before Insert, Before Update) {
    Set < String > accountNames = new Set < String > ();
    Map < String, Account > accMap = new Map < String, Account > ();
    //hold all the acc names in a set  
    for (Opportunity o: trigger.new) {
        if (o.Account_Name__c != NULL) {
            accountNames.add(o.Account_Name__c)
        }
    }
    //query all the related accounts and create a map
    for (Account acc: [SELECT Id, Name FROM Account WHERE name IN: accountNames]) {
        accMap.put(acc.Name, acc);
    }
    //relate opps
    for (Opportunity o: trigger.new) {
        Account acc = accMap.get(o.Account_Name__c);
        if (acc != NULL && acc.Unit__c != NULL) {
            o.Unit__c = acc.Unit__c;
        }
    }

}

 

 

 

All Answers

Avidev9Avidev9

The problem is you are having an SOQL inside a FOR loop.

This is not a good practice to follow.

 

I will suggest you to tweak ur code a lil. Something like this

 

trigger setUnit on Opportunity(Before Insert, Before Update) {
    Set < String > accountNames = new Set < String > ();
    Map < String, Account > accMap = new Map < String, Account > ();
    //hold all the acc names in a set  
    for (Opportunity o: trigger.new) {
        if (o.Account_Name__c != NULL) {
            accountNames.add(o.Account_Name__c)
        }
    }
    //query all the related accounts and create a map
    for (Account acc: [SELECT Id, Name FROM Account WHERE name IN: accountNames]) {
        accMap.put(acc.Name, acc);
    }
    //relate opps
    for (Opportunity o: trigger.new) {
        Account acc = accMap.get(o.Account_Name__c);
        if (acc != NULL && acc.Unit__c != NULL) {
            o.Unit__c = acc.Unit__c;
        }
    }

}

 

 

 

This was selected as the best answer
sparc1.3696571782628958E12sparc1.3696571782628958E12

Hey Avi!

 

Thanx a ton mate!!! The code snippet was "smooth as a baby's bottom"... Its working all fine as if there wasnt any problem ever.

 

Thanks again... Kudos!!!

Shiv
sparc1.3696571782628958E12sparc1.3696571782628958E12

Hey Avi

 

Can you also please help on this one. Kindly follow the link:

 

http://boards.developerforce.com/t5/Apex-Code-Development/Scheduled-the-deletion-of-records-based-on-a-specific-Criteria/td-p/632573

 

Thanks

Shiv