-2

I have a custom object custom__c with couple of custom fields that needs to determine the price of the product.

  • Custom_Child__c is a lookup field that gets the product.
  • Price_Book__c is a lookup field that gets the pricebook
  • Custom_Child_PB_ID__c is a text field that should the id of the pricebookentry depending which pricebook and product is selected.
  • Custom_Child_PB_UnitPRice__c gets the unitprice depending of which pricebook and product is selected depending on the pricebookentryid.

Let me explain more about the custom object:

This object has couple of lookup fields to products and pricebook i want when i select a product in the lookup field custom__c to automatically update the pricebookentryid field Custom_Child_PB_ID__c and also update that pricebookentryid price that comes from a pricebook that i already inserted it.

Below is my trigger it works fine except when I want to update the UnitPrice in that custom field Custom_Child_PB_UnitPRice__c and the Custom_Child_PB_ID__c depending on the Price_Book__c and Custom_Child__c selected.

What I am doing wrong?

trigger setPricebookEntryID on custum__c(before update) { 
    Set<Id> setPriceBookId = new Set<Id>();
    Map<Id,PricebookEntry> mapPriceBookIdToPBE = new Map<Id,PricebookEntry>();
    for (custum__c p : Trigger.new) { 
        if (p.Price_Book__c != null) {
            setPriceBookId.add(p.Price_Book__c);    
        }
    }
    if (!setPriceBookId.isEmpty()) {
        for (PricebookEntry pbe : [SELECT Id, UnitPrice, Pricebook2Id,Product2Id 
                    FROM PricebookEntry
                    WHERE Pricebook2Id IN: setPriceBookId]) {
             mapPriceBookIdToPBE.put(pbe.Pricebook2Id, pbe);
        } 
        for (Custom_c p : Trigger.new) {
            if (mapPriceBookIdToPBE.containskey(p.Price_Book__c) && mapPriceBookIdToPBE.get(p.Price_Book__c).Product2Id == p.Custom_Child__c) {
                p.Custom_Child_PB_ID__c= mapPriceBookIdToPBE.get(p.Price_Book__c).Id;
                p.Custom_Child_PB_UnitPRice__c = mapPriceBookIdToPBE.get(p.Price_Book__c).UnitPrice;
            }
        }             
    }  
}

If the field is empty and i am trying to save the record I am getting this. enter image description here

Can I make that optional?

2
  • First of all, never perform a query inside for loop. When you will process records in bulk it will surely hit the governor limit. Commented Oct 8, 2018 at 4:15
  • Ok thanks Abhishek i will take the query outside of the look. Commented Oct 8, 2018 at 7:34

2 Answers 2

3

It looks like you are trying to update the Custom_Child_PB_UnitPRice__c with the same object record that fired the trigger. Did you mean to update it with the unit price from the PricebookEntry? If so, then you will need to do the following:

trigger setPricebookEntryID on custom__c (before insert) { 
   for (custom__c p : Trigger.new) { 
     PricebookEntry pbe = [SELECT Id, UnitPrice FROM 
     PricebookEntry WHERE Pricebook2Id = :p.Price_Book__c 
     AND Product2Id = :p.custom_Child__c limit 1];
     p.Custom_Child_PB_ID__c= pbe.Id; 
     p.Custom_Child_PB_UnitPRice__c = pbe.UnitPrice;
   } 
}
9
  • Thanks Brian for the help. This works only when I insert a pricebook and not when i don't. I am getting the following error. See update above. Commented Oct 8, 2018 at 7:31
  • Have you tried changing your trigger from before insert to before update? Commented Oct 9, 2018 at 4:00
  • I did that and i am getting this error: There were custom validation error(s) encountered while saving the affected record(s). The first validation error encountered was "Apex trigger setPricebookEntryID caused an unexpected exception, contact your administrator: setPricebookEntryID: execution of BeforeUpdate caused by: System.SObjectException: SObject row was retrieved via SOQL without querying the requested field: PricebookEntry.Product2Id: Trigger.setPricebookEntryID: line 29, column 1". Commented Oct 9, 2018 at 9:51
  • 2
    Ok. in your SOQL query add the Product2Id. it is referenced later on but not retrieved in the query. So it should be [SELECT Id, UnitPrice, Pricebook2Id, Product2Id from PricebookEntry .......ect Commented Oct 9, 2018 at 14:35
  • Hi Brian, I do what you said I am using the code made by Abhishek as followed and I added the Product2Id and it doesn't get the error, however it doesn't show the price when I populate both the Price_Book__c and custom_Child__c fields :( Commented Oct 9, 2018 at 16:14
1

You are getting this issue because the field is empty and it's not returning any number of records

While a SELECT normally returns an array/list, these statements are using the shorthand syntax that assumes only one row is returned. What’s not obvious is that it also assumes that exactly one row is returned! Refer this for detail explanation around this error

Again, never ever have a SOQL query inside a loop. Loops, especially those iterating over Trigger.new, will easily run 200+ queries and push you across the limit.

You can modify your trigger something like below which will follow all best practices

trigger setPricebookEntryID  on custom__c (before insert) {
Set<Id> setPriceBookId = new Set<Id>();
Map<Id,PricebookEntry> mapPriceBookIdToPBE = new Map<Id,PricebookEntry>();
for (custom__c p : Trigger.new) {
    if (p.Price_Book__c != null) {
        setPriceBookId.add(p.Price_Book__c);    
    }   
}

if (!setPriceBookId.isEmpty()) {
    for (PricebookEntry pbe : [SELECT Id, UnitPrice, Pricebook2Id 
                    FROM PricebookEntry
                   WHERE Pricebook2Id IN: setPriceBookId]) {
        mapPriceBookIdToPBE.put(pbe.Pricebook2Id, pbe);
    }   
}

for (custom__c p : Trigger.new) {
    if (mapPriceBookIdToPBE.containskey(p.Price_Book__c) && mapPriceBookIdToPBE.get(p.Price_Book__c).Product2Id == p.custom_Child__c) {
        p.Custom_Child_PB_ID__c= mapPriceBookIdToPBE.get(p.Price_Book__c).Id;
        p.Custom_Child_PB_UnitPRice__c = mapPriceBookIdToPBE.get(p.Price_Book__c).UnitPrice;
    }
}                                                                           }
3
  • Thanks for your time Abhishek I really appreciate it but this doesn't return the price somehow to my field Custom_Child_PB_UnitPRice__c. Something is missing I can't figure it out. Commented Oct 8, 2018 at 13:41
  • Are you facing error while an assignment? Also, debug the value of mapPriceBookIdToPBE.get(p.Price_Book__c).UnitPrice Commented Oct 8, 2018 at 13:56
  • i am kinda getting the exact same error. Maybe I am not doing something right. Commented Oct 8, 2018 at 14:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .