Refering Product using productId in pricebookentry

Question

I have created a custom object called Top5UpSells on Leads page which basically shows the top 5 potential UpSell opportunities for that lead based on his product interest captured in another custom field called product interest. So basically i am listing down those products under Top5UpSells whose Product2.Family is same as Lead.Product_Interest__c. I have a trigger written on Lead to insert Top5UpSells records by calling the following class' createUpSells method as follows:

//Class called by Trigger on Lead

public class leadUpSellHelper{
  public void createUpSells(List<Lead> newLeads){
  //Create a List of UpSells
  List<UpSells_for_Leads__c> UpSellsSet = new List<UpSells_for_Leads__c>();

  //Iterate over Leads to create upSellsSet for each Lead
  for (Lead ld:newLeads)
  {
    List<PricebookEntry> pbeIds = new List<PricebookEntry>();

    // Select those PriceBookEntries for 'Product Plans' pricebook where 
    //Product Family is same as Lead's Product Interest.
    pbeIds = [Select Id from PricebookEntry where Product2.Family =   :ld.Product_Interest__c 
              and Pricebook2Id in (select Id from Pricebook2 where Name = 'Product Plans')];

    for (PricebookEntry pbeId:pbeIds)
    {
        UpSells_for_Leads__c UpSell = new UpSells_for_Leads__c();
        UpSell.Related_Lead_UpSell__c = ld.Id;
        // Throws error in below line
        String prodName = String.valueof([Select PriceBookEntry.Product2.Name from pricebookentry where Id=:pbeId limit 1]);
        UpSell.Related_Product_UpSell__c = [Select Id from Product2 where Name=:prodName];
        UpSell.UpSell_Name__c = prodName;

        UpSellsSet.add(UpSell);
    }
  }
  //Insert UpSell records for all Leads
   insert UpSellsSet;
 }
}

But this does not seem to work. It throws the following ERROR:

Invalid bind expression type of SOBJECT:PricebookEntry for column of type Id

Please suggest how can i create a lookup field for Product using PricebookEntry and insert record in Top5UpSells refering that Product?

Answer 1

1. You're querying inside a loop. That'll possibly trip you up later.

2. You're trying to bind an entire object to an ID field (you called it pbeId, but it's actually a PriceBookEntry, not an Id).

Here's a bulk-safe version of the code:

public class leadUpSellHelper {
    public void createUpSells(Lead[] newLeads) {
        Map<String, PriceBookEntry[]> entries = new Map<String, PriceBookEntry[]>();
        Upsells_For_Leads__c[] upsells = new Upsells_For_Leads__C[0];

        for(Lead record: newLeads)
            entries.put(record.Product_Interest__c, new PriceBookEntry[0]>();

        for(PriceBookEntry entry: [SELECT Id, Product2Id, Product2.Name, Product2.Family
                                   FROM   PriceBookEntry
                                   WHERE  Product2.Family IN :entries.keySet() AND
                                          PriceBook2.Name = 'Product Plans']) {
            entries.get(entry.Product2.Family).add(entry);
        }

        for(Lead leadRecord: newLeads) {
            for(PriceBookEntry entry: entries.get(leadRecord.Product_Interest__c)) {
                upsells.add(new Upsells_For_Leads__c(
                    Related_Lead_Upsell__c = leadRecord.Id,
                    Related_Product_Upsell__c = entry.Product2Id,
                    Upsell_Name__c = entry.Product2.Name
                ));
            }
        }

        insert upsells;
    }
}

I see comments about other values being incorrect; I suggest you study this code to learn how it should work.

Answer 2

The line:

String prodName = String.valueof([Select PriceBookEntry.Product2.Name from pricebookentry where Id=:pbeId limit 1]);

Is calling the String.valueOf() function upon the PricebookEntry object as a whole, from what it looks like you need to add .PriceBookEntry.Product2.Name after the query. i.e.

String prodName = String.valueof([Select PriceBookEntry.Product2.Name from pricebookentry where Id=:pbeId limit 1].PricebookEntry.Product2.Name);

This will then pull the name of the product out of the object returned from your query so it can then be cast to a string.