0

I apologise in advance as i know this should be simple and other people ask this same question but no matter how i try to re-write my code i keep getting errors so ive given in and thought id ask for some help.

For each price book entry against the standard price book, i need to find matching non-standard price book entrys that have the same Product2Id and update them. My code is below and obviously im hitting the SOQL query limit since its inside the for loop.

    pricebook2 SPB = [SELECT Id FROM Pricebook2 WHERE IsActive = true AND IsStandard = true];   
    String SPBId = SPB.Id;  

 //get book entries for the standard price book
    List<pricebookEntry> StandardPBEntries = [SELECT Id,Product2Id FROM pricebookEntry WHERE priceBook2Id = :SPBId];  

 //loop around standard price book entries looking for matches against non standard entries
    for (pricebookEntry StandardEntry : StandardPBEntries) 
           {              
            String prod2Id  = StandardEntry.Product2Id;

             //look for matches
             List<pricebookEntry> Matches = [SELECT Id, Name, Pricebook2Id, Product2Id, IsActive, Is_Negotiated__c FROM PricebookEntry 
                                            where product2id = :prod2Id and isactive = true 
                                            and priceBook2Id IN (SELECT Id FROM Pricebook2 WHERE IsActive = true AND IsStandard = false)];

                 if(Matches.size() > 0)                    
                 {
                    for (pricebookEntry aMatch : Matches)
                    {
                        //update required data
                    }                           
                 }       

            }
  • Here you can move the SOQL outside the loop and store prod2Id value in a Set<Id> and use this set in the SOQL query with IN: idsSet. – itzmukeshy7 Jun 30 '17 at 13:46
5

Do it like this:

pricebook2 SPB = [SELECT Id FROM Pricebook2 WHERE IsActive = true AND IsStandard = true];   
String SPBId = SPB.Id;  

//get book entries for the standard price book
List<pricebookEntry> StandardPBEntries = [SELECT Id,Product2Id FROM pricebookEntry WHERE priceBook2Id = :SPBId];  

 //loop around standard price book entries looking for matches against non standard entries
Set<Id> product2IdsSet = new Set<Id>();
for(pricebookEntry StandardEntry : StandardPBEntries){            
    product2IdsSet.add(StandardEntry.Product2Id);                
}


//look for matches
List<pricebookEntry> Matches = [SELECT Id, Name, Pricebook2Id, Product2Id, IsActive, Is_Negotiated__c FROM PricebookEntry 
                                where product2id IN: product2IdsSet and isactive = true 
                                AND priceBook2Id IN (SELECT Id FROM Pricebook2 WHERE IsActive = true AND IsStandard = false)];

for(pricebookEntry aMatch : Matches){
    //update required data
}        

There is no need to add the size() check, foreach will handle this even if there is no record returned by the query.

  • Just realised another issue i have with this. During the loop, i need to set the values against the non-standard entry to match is corresponding standard entry. In order to do this is a Set<> no longer appropriate? Could i use a map for this? – AndrewG Jul 3 '17 at 10:02
  • Sure, we can modify the collection as per the need. – itzmukeshy7 Jul 3 '17 at 12:26
  • So how would i modify the collection and the loop so for each aMatch i can set all the fields against that priceBookEntry to the values from the same fields against the standard price book entry with that matching product2Id? – AndrewG Jul 5 '17 at 6:31
  • If you want to update some other fields of pricebookEntry then you can do the same in the loop. and finally, do update Matckes;; – itzmukeshy7 Jul 5 '17 at 10:40
  • i understand how to do the update but im having a little trouble setting up the Map<> so i have access to the standard price book entry values without doing a SOQL inside the loop to retrieve it. So at the moment i have the product2Ids in a List, but i need access to the other values on the standard entry too so i can use them to update the match inside the loop. – AndrewG Jul 5 '17 at 12:46

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.