function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Sudhir_MeruSudhir_Meru 

How to Change Query Inside loop

Hi,

  I wrote a trigger to insert opportunity and opportunityline items.  There are too many SOQL queries inside for loop how to modify please suggest. 

Hi,

  I wrote a trigger to insert opportunity and opportunityline items.  There are too many SOQL queries inside for loop how to modify please suggest.

trigger Temp_Asset2_Opp on Temp_Assets__c (after insert )
{
    List<OpportunityLineItem> OppLineItems = new List<OpportunityLineItem>();
  
    List<Temp_Assets__c> CurrentAsset = [Select Id, Name, Serial_Number__c, AccountId__c, Product__c, Service_Start_Date__c,
                                              Service_End_Date__c ,Install_Date__c ,Reseller__c,Distributor__c ,
                                              Incumbent_Reseller__c,Education__c,Expiry_Date__c,Existing_Opportunity__c,
                                              New_Opportunity__c,Expiry_Term__c,Bundle_Support__c, X5_Year_SKU_Code__c, X3_Year_SKU_Code__c,
                                              X1_Year_SKU_Code__c, Monthly_SKU_Code__c, X5_year_SKU__c ,X3_year_SKU__c, X1_year_SKU__c,
                                              Total_in_Months__c, Support_Only_5_Year_SKU__c, Support_Only_3_Year_SKU__c, Support_Only_1_Year_SKU__c,
                                              Support_Only_Monthly_SKU__c
                                         From Temp_Assets__c
                                         Where CreatedById = :userinfo.getUserId()];
  
    Pricebook2 prBook= [select id from Pricebook2 where Name=: 'NAM Price Book'];

    for ( Temp_Assets__c TA : CurrentAsset )
    {
         // If New Opportunity is Created
         if ( TA.Existing_Opportunity__c == NULL && TA.New_Opportunity__c != NULL )  
          {
             // Insert Opportunity with all mandatory fields
             Opportunity Opp = new Opportunity();
             Opp.Name      = TA.New_Opportunity__c;           
             Opp.Type      = 'Existing Customer';
             Opp.AccountId = TA.AccountId__c;
             Opp.CloseDate = TA.Expiry_Date__c;
             Opp.Government_Contract__c = 'None';
             Opp.Renewal_Opportunity__c = 'Yes';
             Opp.StageName = 'Renewal';
             Opp.Lost_Reason__c = 'Other';
             Opp.Primary_Competitor__c = 'No Competitor';
             Opp.ForecastCategoryName = 'Pipeline';
             Opp.LeadSource = 'Renewal';
             Opp.Primary_Reseller__c    = TA.Reseller__c;
             Opp.Primary_Distributor__c = TA.Distributor__c;
             Opp.Renewal_Incumbant_Reseller__c = TA.Incumbent_Reseller__c;
             Opp.Renewal_K_12__c   =  TA.Education__c;
           
             Insert Opp;
           //Check if the Renewals need 5 years renewals
             if (TA.X5_year_SKU__c > 0 && (TA.Support_Only_5_Year_SKU__c!=null || TA.X5_Year_SKU_Code__c!=null)) {
 
                //Insert into Opportunity Lines  
                OpportunityLineItem OppL_5yr = new OpportunityLineItem();
                    OppL_5yr.OpportunityId = Opp.Id;
                    //check if the Renew is for Bundle Support or Support only
                    if (TA.Bundle_Support__c == true && TA.X5_Year_SKU_Code__c!= null) {
                        //Retrieve the priceBookEntryId for Bundle support 5 years Renewals SKU Code
                        OppL_5yr.PricebookEntryId = [Select id from PriceBookEntry where product2.Name =:TA.X5_Year_SKU_Code__c AND priceBook2Id=:prBook.id].Id;
                    }
                    else if (TA.Bundle_Support__c != true && TA.Support_Only_5_Year_SKU__c!= null) {
                        //Retrieve the priceBookEntryId for CO or SO support 5 years Renewals SKU Code
                        OppL_5yr.PricebookEntryId = [Select id from PriceBookEntry where product2.Name =:TA.Support_Only_5_Year_SKU__c AND priceBook2Id=:prBook.id].Id;
                    }                 
                    //OppL_5yr.PricebookEntryId = prBookEntry.Id;
                    OppL_5yr.Quantity = TA.X5_year_SKU__c;              
                
                //Adding to the list
                OppLineItems.add(OppL_5yr);
               
                 //Insert OppL_5yr; 
              }     
            if (TA.X3_year_SKU__c > 0 && (TA.Support_Only_3_Year_SKU__c!=null || TA.X3_Year_SKU_Code__c!=null)) {
              
                //Insert into Opportunity Lines
                OpportunityLineItem OppL_3yr = new OpportunityLineItem();
                OppL_3yr.OpportunityId = Opp.Id;
                //check if the Renew is for Bundle Support or Support only
                if (TA.Bundle_Support__c == true && TA.X3_Year_SKU_Code__c!= null) {
                    //Retrieve the priceBookEntryId for Bundle support 3 years Renewals SKU Code
                    OppL_3yr.PricebookEntryId = [Select id from PriceBookEntry where product2.Name =:TA.X3_Year_SKU_Code__c AND priceBook2Id=:prBook.id].Id;
                }
                else if (TA.Bundle_Support__c != true && TA.Support_Only_3_Year_SKU__c!= null) {
                    //Retrieve the priceBookEntryId for CO or SO support 3 years Renewals SKU Code
                    OppL_3yr.PricebookEntryId = [Select id from PriceBookEntry where product2.Name =:TA.Support_Only_3_Year_SKU__c AND priceBook2Id=:prBook.id].Id;
                }
                //OppL_3yr.PricebookEntryId = prBookEntry.Id;
                OppL_3yr.Quantity=TA.X3_year_SKU__c;
                  
                
                OppLineItems.add(OppL_3yr);
                //Insert OppL_3yr; 
            }
            if (TA.X1_year_SKU__c > 0 && (TA.Support_Only_1_Year_SKU__c!=null || TA.X1_Year_SKU_Code__c!=null)) {

                //Insert into Opportunity Lines
                OpportunityLineItem OppL_1yr = new OpportunityLineItem();
                OppL_1yr.OpportunityId = Opp.Id;
                //check if the Renew is for Bundle Support or Support only
                if (TA.Bundle_Support__c == true && TA.X1_Year_SKU_Code__c!= null) {
                    //Retrieve the priceBookEntryId for Bundle support 1 year Renewals SKU Code
                    OppL_1yr.PricebookEntryId = [Select id from PriceBookEntry where product2.Name =:TA.X1_Year_SKU_Code__c AND priceBook2Id=:prBook.id].Id;
                }
                else if (TA.Bundle_Support__c != true && TA.Support_Only_1_Year_SKU__c!= null) {
                    //Retrieve the priceBookEntryId for CO or SO support 1 years Renewals SKU Code
                    OppL_1yr.PricebookEntryId = [Select id from PriceBookEntry where product2.Name =:TA.Support_Only_1_Year_SKU__c AND priceBook2Id=:prBook.id].Id;
                }
                //OppL_1yr.PricebookEntryId = prBookEntry.Id;
                OppL_1yr.Quantity=TA.X1_year_SKU__c;
              
              
                OppLineItems.add(OppL_1yr);
                //Insert OppL_1yr; 
            }
            if (TA.Total_in_Months__c > 0 && (TA.Support_Only_Monthly_SKU__c!=null || TA.Monthly_SKU_Code__c!=null)) {

                //Insert into Opportunity Lines
                OpportunityLineItem OppL_Months = new OpportunityLineItem();
                OppL_Months.OpportunityId = Opp.Id;
                //check if the Renew is for Bundle Support or Support only
                if (TA.Bundle_Support__c == true && TA.Monthly_SKU_Code__c!= null) {
                    //Retrieve the priceBookEntryId for Bundle support Monthly Renewals SKU Code
                    OppL_Months.PricebookEntryId = [Select id from PriceBookEntry where product2.Name =:TA.Monthly_SKU_Code__c AND priceBook2Id=:prBook.id].Id;
                }
                else if (TA.Bundle_Support__c != true && TA.Support_Only_Monthly_SKU__c!= null) {
                    //Retrieve the priceBookEntryId for CO or SO support Monthly Renewals SKU Code
                    OppL_Months.PricebookEntryId = [Select id from PriceBookEntry where product2.Name =:TA.Support_Only_Monthly_SKU__c AND priceBook2Id=:prBook.id].Id;
                }
                //OppL_Months.PricebookEntryId = prBookEntry.Id;
                OppL_Months.Quantity=TA.Total_in_Months__c;        
              
                OppLineItems.add(OppL_Months);
                //Insert OppL_Months; 
            }          
        }
          
       
  List<Temp_Assets__c> TempAsset = [SELECT Id From Temp_Assets__c Where CreatedById = :userinfo.getUserId()];
  delete TempAsset ;

}

Thanks
Sudhir

 

Best Answer chosen by Sudhir_Meru
Sudhir_MeruSudhir_Meru

Hi Ramu,

    I was able to fix this using function returning value by passing parameters 
 

Thanks

Sudhir

All Answers

Ramu_SFDCRamu_SFDC
Hi Sudhir, Try using map collection variables which you can later use to loop through and get the data based on key value. This is the only way you can get rid of soql queries inside for loops. 
Sudhir_MeruSudhir_Meru
Hi Ramu, 

  Can you give me a example 

Thanks
Sudhir
Sudhir_MeruSudhir_Meru

Hi Ramu,

    I was able to fix this using function returning value by passing parameters 
 

Thanks

Sudhir

This was selected as the best answer