0

I have developed following code for apex trigger and it will work when i am going to insert bulk data from the data loader,

Trigger: System.LimitException: Too many SOQL queries: 101

I read some post for bulkify and best practice to write apex trigger but in below code i mentioned i am not sure how can i bulkified it.

please guide me.

here is my code :

    List<SBQQ__QuoteLine__c> quoteLineList = new List<SBQQ__QuoteLine__c>();

for(opportunity oppAfter : trigger.New){
    List<Product2> prList = [select Id, Name from Product2 where Name =: oppAfter.Quote_line_Product_Name__c Limit 1];

    if(oppAfter.Coming_from_Portal__c == True){
        if(trigger.isAfter){
            SBQQ__Quote__c quote = new SBQQ__Quote__c();
            quote.SBQQ__Account__c = oppAfter.AccountId;
            quote.SBQQ__PriceBook__c = [Select Id from Pricebook2 where Name = 'Lista de Precios CPQ' Limit 1].Id;
            quote.Primary_Contact__c = [Select Id from Contact where AccountId =: oppAfter.AccountId Order By createdDate Limit 1].Id;

            insert quote;

            SBQQ__QuoteLine__c qliNew = new SBQQ__QuoteLine__c();
            qliNew.SBQQ__Product__c = [Select Id, Name from Product2 where Name =: 'DATA NETWORK SERVICES' Limit 1].Id;
            qliNew.SBQQ__Quote__c = quote.Id;
            insert qliNew;

            for(SBQQ__Quote__c q : [select Id from SBQQ__Quote__c where Id =: quote.Id]){
                if(oppAfter.Quote_line_Product_Name__c != Null){
                    SBQQ__QuoteLine__c qli = new SBQQ__QuoteLine__c();
                    for(Product2 p : prList){
                        qli.SBQQ__Product__c = p.Id;
                        if(oppAfter.Product_Special_Price_coming_from_portal__c != Null){
                            qli.SBQQ__SpecialPrice__c = Decimal.valueOf(oppAfter.Product_Special_Price_coming_from_portal__c);
                        }
                        qli.SBQQ__Quote__c = q.Id;
                        qli.SBQQ__SpecialPriceType__c = 'Custom';
                        if(oppAfter.Quote_line_TipoClearChanel_Velocidad__c != Null){
                            qli.TipoClearChanel__c = String.ValueOf(oppAfter.Quote_line_TipoClearChanel_Velocidad__c);    
                        }
                        quoteLineList.add(qli);
                    }
                }
            }
        }
    }
}
    if(quoteLineList.size() > 0)
        insert quoteLineList;
}
10
  • 1
    Try to avoid For loop inside a for loop and don't make any query inside a for loop..if u remove this both..then ur code will work fine
    – user48859
    Sep 7 '17 at 9:56
  • but i am going to use inserted quote in quote line. how can i do that ?
    – Nail David
    Sep 7 '17 at 9:58
  • see, for(SBQQ__Quote__c q : [select Id from SBQQ__Quote__c where Id =: quote.Id]){
    – Nail David
    Sep 7 '17 at 10:00
  • 2
    Here is the issue : > for(opportunity oppAfter : trigger.New){ List<Product2> prList = [select Id, Name from Product2 where Name =: oppAfter.Quote_line_Product_Name__c Limit 1]; Sep 7 '17 at 10:04
  • @SaumyaGaikwad Did not get you. can you please tell me what's wrong ?
    – Nail David
    Sep 7 '17 at 10:09
2

Certain corrections that will help:

  • In For loop on Opportunity, take the product associated with Opp in Set and then outside of the for loop do the query of Product that fetches the data from Set. Set will ensure that we have unique and collected data at once.

    Set<String> productSet = new Set<String>();
    for(opportunity oppAfter : trigger.New){
        productSet.add(oppAfter.Quote_line_Product_Name__c); 
    }
    List<Product2> prList = [select Id, Name from Product2 where Name IN: productSet];
    

In the similar manner, what all is needed in queries in further part of code, take that in set and do the queries outside of for loop.

  • Rather than inserting the quote in between, take the values in List or Map and use them further when inserting the Quote Line Items as well as when doing the for loop over the product. This way for within for that is, nested for can be avoided.
2
  • Do i need to create two for loop here ? one for fetching data and then use the certain list in another loop and assign value according to the data ? is that correct ?
    – Nail David
    Sep 7 '17 at 10:23
  • In one For loop of Opp, we can get data in set, can get the values of new Quotes in Map. At later part when doing the nested for, rather than that we can just do one For loop over Product and can fetch the value of Quote from the Map. Sep 7 '17 at 10:35
1

You need to populate a list of quotes. Then insert the list of quotes outside the FOR loop. Also, never use a SOQL query inside a FOR loop. Use maps to manipulate data. Here is a little help. I haven't tested it, I don't know if it compiles. I am also wondering why you don't use a lookup field to Product on the opportunity ?

 List<SBQQ__QuoteLine__c> quoteLineList = new List<SBQQ__QuoteLine__c>();
 List<SBQQ__Quote__c> quotesList = new List<SBQQ__Quote__c>();
 Id PriceBookId = [Select Id from Pricebook2 where Name = 'Lista de Precios CPQ' Limit 1].Id;
 Map<Opportunity,Id> opportunityAccount = new Map<Opportunity,Id> ();
 Map<Opportunity,Id> opportunityProduct = new Map<Opportunity,Id> ();
 Set<Id> accountIds = new Set<Id>(); 
 Map<Opportunity,Id> primaryContact = new Map<Opportunity,Id> ();
 Id datanetworkproductId = [Select Id, Name from Product2 where Name =: 'DATA NETWORK SERVICES' Limit 1].Id ;

 if(trigger.isAfter){
for(opportunity oppAfter : trigger.New){    

   if(oppAfter.Coming_from_Portal__c == true){
        oppsToUpdate.add(oppAfter);
        opportunityAccount.put(oppAfter,oppAfter.accountId);
        opportunityProduct.put(oppAfter,oppAfter.product__c ); // why don't you use the a lookup to Product rather than a name ?
        accountIds.add(oppAfter.accountId); 
        ProductIds.add(oppAfter.product__c);
   }

}
}
map<Id,Account> accounts = new Map<Id,Account>([select Id,PrimaryContact__c FROM Account WHERE AccountId IN : accountIds]);
for(Opportunity opp: opportunityAccount){
 if(accounts.containsKey(opp.AccountId)
    primaryContact.put(opp,accounts.get(opp.AccountId).PrimaryContact__c);
}
 List<Opportunity> oppsToUpdate = new List<Opportunity>();


 for(opportunity opp : oppsToUpdate){

quotesList.add(new SBQQ__Quote__c(
        SBQQ__Account__c = oppAfter.AccountId,
        SBQQ__PriceBook__c = PriceBookId;
        Primary_Contact__c = primaryContact.get(oppAfter)));

quotesList.add(quote);  

 }
insert quotesList ;

for(SBQQ__Quote__c quote : quotesList){         

        quoteLineList.add(new SBQQ__QuoteLine__c(
        SBQQ__Product__c = datanetworkproductId,
        BQQ__Quote__c = quote.Id));

  /**** add following code to adapt here ***/
 }
insert quoteLineList;
2
  • yes, i tried to insert quoteList but when i use this dml outside of the for loop then it says that qli.SBQQ__Quote__c = q.Id; is not assigned before inserting quote.
    – Nail David
    Sep 7 '17 at 10:08
  • see my edited comment above for more help
    – sfdcGest
    Sep 7 '17 at 12:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.