Apex Code Development You need to sign in to do that
Don't have an account?
kiwitrotter How to query back Product field value in OpportunityLineItem trigger.
Hi guys,
Just wondering if anyone can help me out with this one. I'm trying to query back the family value of an opportunity product line item so that I can populate a custom field with a value based on the product family that it belongs to. I have tried a few different things but nothing has worked. The closest that I can get is as follows:
---
trigger oppLineItemCheck on OpportunityLineItem (after insert) {
for (OpportunityLineItem oli : Trigger.new)
{
String resultName = oli.PricebookEntry.Product2.Name;
String resultFamily = oli.PricebookEntry.Product2.Family;
Double lineItemPrice = oli.ListPrice;
List <Opportunity> oList = [select name, amount from Opportunity where Id = :oli.OpportunityId];
System.debug('Inside first for loop...');
for (Opportunity o:oList){
o.Description = 'Opportunity Product Name is: ' + resultName + ' Opportunity Product family is: ' + resultFamily;
System.debug(o.Description); //prints null values to logs for name and family
System.debug('Inside second for loop...');
System.debug('Pricebook2Idb 1 = ' +oli.PriceBookEntryID); //prints PricebookEntryID value to logs
System.debug('Pricebook2Idb 2 = ' +oli.PriceBookEntry.ID); //prints null
if(resultFamily == 'Products'){
o.Products_Amount__c = lineItemPrice;
} else {
o.Services_Amount__c = lineItemPrice;
}
}
}
}
---
However, in my debug logs the values of the Product name and family are null. I'm probably missing something obvious here but if anyone can shed any light on this I would appreciate it.
Many thanks in advance.
You need to do following changes in your code :-
from
Double lineItemPrice;
lineItemPrice = oliList[i].ListPrice;
To
Double lineItemPrice=0.0;
lineItemPrice = lineItemPrice + oliList[i].ListPrice;
Try this ,I am pretty much sure this is going to work
All Answers
trigger oppLineItemCheck on OpportunityLineItem (after insert) {
for (OpportunityLineItem oli : Trigger.new)
{
String resultName = oli.PricebookEntry.Product2.Name;
String resultFamily = oli.PricebookEntry.Product2.Family;
Here the mistake is the red part.
Pricebookentry is a lookup or some other relationship to the OpportunityLineItem . So by the above syntax it may not possible to get the required value.
Here you should query for it
Double lineItemPrice = oli.ListPrice;
List <Opportunity> oList = [select name, amount from Opportunity where Id = :oli.OpportunityId];
Do not write quries in the loops which will let you to hit the governor limits.
System.debug('Inside first for loop...');
for (Opportunity o:oList){
o.Description = 'Opportunity Product Name is: ' + resultName + ' Opportunity Product family is: ' + resultFamily;
System.debug(o.Description); //prints null values to logs for name and family
System.debug('Inside second for loop...');
System.debug('Pricebook2Idb 1 = ' +oli.PriceBookEntryID); //prints PricebookEntryID value to logs
System.debug('Pricebook2Idb 2 = ' +oli.PriceBookEntry.ID); //prints null
if(resultFamily == 'Products'){
o.Products_Amount__c = lineItemPrice;
} else {
o.Services_Amount__c = lineItemPrice;
}
}
}
}
Provide me the following details so i can help more better.
what are the relationsships b/w opportunity, opportunitylineitem price book etc.
If this answers your question make this as a solution and give kudos
Agreed with Suree,you should not be writing the query inside a for loop that is not recommended at all.Try the below code :-
trigger oppLineItemCheck on OpportunityLineItem (after insert) {
List<Id> oppIds = new List<Id>();
List<Id> oppLineIds = new List<Id>();
for (OpportunityLineItem oli : Trigger.new)
{
oppIds.add(oli.OpportunityId);
oppLineIds.add(oli.id);
}
List<Opportunity> oList = new List<Opportunity>[select Decription,Products_Amount__c,Services_Amount__c from Opportunity where Id in :oppIds];
List<OpportunityLineItem> oliList = [select ListPrice,name,PricebookEntry.Product2.Name,PricebookEntry.Product2.family from OpportunityLineItem where id in:oppLineIds];
String resultName = oliList[0].PricebookEntry.Product2.Name;
String resultFamily = oliList[0].PricebookEntry.Product2.Family;
Double lineItemPrice = oliList[0].ListPrice;
System.debug('Inside first for loop...');
for (Opportunity o:oList){
o.Description = 'Opportunity Product Name is: ' + resultName + ' Opportunity Product family is: ' + resultFamily;
System.debug(o.Description); //prints null values to logs for name and family
System.debug('Inside second for loop...');
System.debug('Pricebook2Idb 1 = ' +oli.PriceBookEntryID); //prints PricebookEntryID value to logs
System.debug('Pricebook2Idb 2 = ' +oli.PriceBookEntry.ID); //prints null
if(resultFamily == 'Products'){
o.Products_Amount__c = lineItemPrice;
} else {
o.Services_Amount__c = lineItemPrice;
}
}
}
Agreed with Suree,you should not be writing the query inside a for loop that is not recommended at all.Try the below code :-
trigger oppLineItemCheck on OpportunityLineItem (after insert) {
List<Id> oppIds = new List<Id>();
List<Id> oppLineIds = new List<Id>();
for (OpportunityLineItem oli : Trigger.new)
{
oppIds.add(oli.OpportunityId);
oppLineIds.add(oli.id);
}
List<Opportunity> oList = new List<Opportunity>[select Decription,Products_Amount__c,Services_Amount__c from Opportunity where Id in :oppIds];
List<OpportunityLineItem> oliList = [select ListPrice,name,PricebookEntry.Product2.Name,PricebookEntry.Product2.family from OpportunityLineItem where id in:oppLineIds];
String resultName = oliList[0].PricebookEntry.Product2.Name;
String resultFamily = oliList[0].PricebookEntry.Product2.Family;
Double lineItemPrice = oliList[0].ListPrice;
for (Opportunity o:oList){
o.Description = 'Opportunity Product Name is: ' + resultName + ' Opportunity Product family is: ' + resultFamily;
System.debug(o.Description); //prints null values to logs for name and family
System.debug('Inside second for loop...');
System.debug('Pricebook2Idb 1 = ' +oli.PriceBookEntryID); //prints PricebookEntryID value to logs
System.debug('Pricebook2Idb 2 = ' +oli.PriceBookEntry.ID); //prints null
if(resultFamily == 'Products'){
o.Products_Amount__c = lineItemPrice;
} else {
o.Services_Amount__c = lineItemPrice;
}
}
}
Hi Vinit_Kumar, thanks for your advise and updated code. This worked for me and I have been able to update my trigger to implement certain actions based on the product family value. However, now I have run into another problem that I'm hoping you might be able to help me with.
My trigger works now for adding product line items to the opportunity one by one. However, when I try to add multiple line items to the opportunity at the same time, only details of the last line item are used, so if I try to add 3 opportunity line items, only the amount value of the 3rd line item gets used to update my custom field in my opportunity.
Here is my code:
---
trigger oppLineItemCheck on OpportunityLineItem (after insert) {
List<Id> oppIds = new List<Id>(); //List to hold opportunity ids
List<Id> oppLineIds = new List<Id>(); //List to hold opportunity line item ids
String resultName, resultFamily;
Double lineItemPrice;
Integer i;
for (OpportunityLineItem oli : Trigger.new)
{
oppIds.add(oli.OpportunityId); //build opportunity ids list
oppLineIds.add(oli.id); //build opportunity line item ids list
}
//Pull back a list of opportunities associated with the line item
List<Opportunity> oList = new List<Opportunity>([select Description,Products_Amount__c,Services_Amount__c from Opportunity where Id in :oppIds]);
//Pull back a list of the opportunity line items
List<OpportunityLineItem> oliList = [select ListPrice,Description,PricebookEntry.Product2.Name,PricebookEntry.Product2.family from
OpportunityLineItem where id in:oppLineIds];
for(i=0; i<oliList.size(); i++){
System.debug('Inside oliList for loop. oliList size: ' + oliList.size());
resultName = oliList[i].PricebookEntry.Product2.Name;
resultFamily = oliList[i].PricebookEntry.Product2.Family;
lineItemPrice = oliList[i].ListPrice;
}
/*** NEED TO HANDLE THE CASE WHERE MULITPLE PRODUCTS ARE BEING ADDED IN ONE TRANSACTION TO THE OPPORTUNITY ***/
/*** NEED TO HANDLE THE CASE WHERE A PRODUCT IS BEING REMOVED FROM THE OPPORTUNITY ***/
for (Opportunity o:oList){
o.Description = 'Opportunity Product Name is: ' + resultName + ' Opportunity Product family is: ' + resultFamily;
System.debug('Inside Opportunity For loop');
System.debug(o.Description); //prints null values to logs for name and family
System.debug('Product family is: ' + resultFamily);
if(resultFamily == 'Products' && o.Products_Amount__c != null){ //account for where amount field is null
o.Products_Amount__c += lineItemPrice;
System.debug('Line Item Price(Products with null check): ' + lineItemPrice);
} else if(resultFamily == 'Products'){
o.Products_Amount__c = lineItemPrice;
System.debug('Line Item Price(Products): ' + lineItemPrice);
} else if(resultFamily == 'Services' && o.Services_Amount__c != null){
o.Services_Amount__c += lineItemPrice;
System.debug('Line Item Price(Services with null check): ' + lineItemPrice);
} else {
o.Services_Amount__c = lineItemPrice;
System.debug('Line Item Price(Services): ' + lineItemPrice);
}
update o;
}
}
---
Any ideas on this one? Your help is very much appreciated. Thank you again!
You need to do following changes in your code :-
from
Double lineItemPrice;
lineItemPrice = oliList[i].ListPrice;
To
Double lineItemPrice=0.0;
lineItemPrice = lineItemPrice + oliList[i].ListPrice;
Try this ,I am pretty much sure this is going to work
Hi Vinit_Kumar,
Thank you very much for your help - this has worked for me.
Thanks again.
Good to know,it worked.Happy to help :)