Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Snita LalSnita Lal 

Problem with Upsert Trigger on OpportunityLineItem

Hi,

We've been working on this trigger for a week now and to say its drive us crazy is an understatement. We've been round the housees a little simply on the basis we're trying to expose the line items to a visualforce force.com profile page - If anyone has done this before then they'll appreciate the headache products creates.

We've had several workable inserts that have either repeatedly inserted new lines or that insert without populating the opportunity lookup. We've got to the point that our trigger hits a dml error using the field references we've provided. Assuming that if we collect the opportunity ID from the line item and that this isn't populated prior to insert is why we hit the error. We've seen this done where the ID is insert after the save and therefore bypassing the error but unfortunately this surpasses our knowledge.

Please can someone help and advise the appropriate change in order to fix this?
  
trigger BidProductsInsert on OpportunityLineItem(after insert, after update) {
   
    Map<Id, OpportunityLineItem> opl = new Map<Id, OpportunityLineItem>();
    Map<Id, Opportunity> opps = new Map<Id, Opportunity>();
    Map<Id, PricebookEntry> prices = new Map<Id, PricebookEntry>();
    Map<opportunitylineitem, bid_products__c> lines = new Map<opportunitylineitem, bid_products__c>();

    for(OpportunityLineItem record: Trigger.new) {
        opps.put(record.OpportunityId, null);
        prices.put(record.PricebookEntryId, null);
    }
    
    opl.putAll([SELECT Id, OpportunityID FROM OpportunityLineItem WHERE Id IN :opl.keySet()]);
    prices.putAll([SELECT Id, Product2.Name, Product2Id FROM PricebookEntry WHERE Id IN :prices.keySet()]);
   
    for(OpportunityLineItem record: Trigger.new) {
        lines.put(record,
            new bid_products__c(
                Name='Opp-Product', 
                Product2__c=prices.get(record.PricebookEntryId).Product2Id, 
                Unit_Price__c=record.UnitPrice, 
                Quantity__c=record.Quantity, 
                Product_Name__c=prices.get(record.PricebookEntryId).Product2.Name,   
                Opportunity__c=opl.get(record.OpportunityID).Id,           
                Id=record.Bid_Products__c));
    }
    
    upsert lines.values();

    for(OpportunityLineItem record: Trigger.new) {
        record.bid_products__c = lines.get(record).Id;
    }
}

Thank you,

Snita
Best Answer chosen by Snita Lal
Srinivas SSrinivas S
Pelase try now and let me know if you have any issues - 
trigger BidProductsInsert on OpportunityLineItem(after insert, after update) {
   
    Map<Id, OpportunityLineItem> opl = new Map<Id, OpportunityLineItem>();
    Map<Id, PricebookEntry> prices = new Map<Id, PricebookEntry>();
    Map<Id, bid_products__c> lines = new Map<Id, bid_products__c>();

    for(OpportunityLineItem record: Trigger.new) {
        if(record.PricebookEntryId != null)
			prices.put(record.PricebookEntryId, null);
    }
    
	if(opl.size() > 0)
		opl.putAll([SELECT Id, OpportunityID FROM OpportunityLineItem WHERE Id IN :opl.keySet()]);
    if(prices.size() > 0)
		prices.putAll([SELECT Id, Product2.Name, Product2Id FROM PricebookEntry WHERE Id IN :prices.keySet()]);
   
    for(OpportunityLineItem record: Trigger.new) {
        lines.put(record.Id,
            new bid_products__c(
                Name='Opp-Product', 
                Product2__c= prices.containsKey(record.PricebookEntryId) ? prices.get(record.PricebookEntryId).Product2Id : null, 
                Unit_Price__c=record.UnitPrice, 
                Quantity__c=record.Quantity, 
                Product_Name__c= prices.containsKey(record.PricebookEntryId) ? prices.get(record.PricebookEntryId).Product2.Name : null,   
                Opportunity__c= opl.containsKey(record.Id) ? opl.get(record.Id).OpportunityID : null,
                Id=record.Bid_Products__c));    
    }
    
    upsert lines.values();

    for(OpportunityLineItem record: Trigger.new) {
        record.bid_products__c = lines.get(record.Id).Id;
    }
}

------------
Thanks,
Srinivas
- Please mark as solution if your problem is resolved.

All Answers

Srinivas SSrinivas S
Hi Snita,

Line 13: opl.putAll([SELECT Id, OpportunityID FROM OpportunityLineItem WHERE Id IN :opl.keySet()]);
Above line holds OpportunityLineItem id as key and OpportunityLineItem record as value.

Line 24: Opportunity__c=opl.get(record.OpportunityID).Id,
you are supplying opportunity id for opl map as key which you never find.

replace Line 24 with below line -
Opportunity__c=opl.get(record.Id).OpportunityID

Supply OpportunityLineItem Id and fetch the related opportunity.

Line 12: record.bid_products__c = lines.get(record).Id; --> add to a list and update the opportunity line item records.

------------
Thanks,
Srinivas
- Please mark as solution if your problem is resolved.

 
Snita LalSnita Lal
Hi Srinivas,

Firstly thank you for coming back so promptly.

We have changed line 24 as above and are thinking we should changing something else on line 12 but unfortunately this wasn't so clear, apologies our knowledge in this area is a little weak.

Based on the line 24 change we are still getting the error 'attempt to de-reference a null object' but guessing your other change will probably fix this. Please can you clarify? amended code below.

Thanks,

Snita 
trigger BidProductsInsert on OpportunityLineItem(after insert, after update) {
   
    Map<Id, OpportunityLineItem> opl = new Map<Id, OpportunityLineItem>();
    Map<Id, Opportunity> opps = new Map<Id, Opportunity>();
    Map<Id, PricebookEntry> prices = new Map<Id, PricebookEntry>();
    Map<opportunitylineitem, bid_products__c> lines = new Map<opportunitylineitem, bid_products__c>();

    for(OpportunityLineItem record: Trigger.new) {
        opps.put(record.OpportunityId, null);
        prices.put(record.PricebookEntryId, null);
    }
    
    opl.putAll([SELECT Id, OpportunityID FROM OpportunityLineItem WHERE Id IN :opl.keySet()]);
    prices.putAll([SELECT Id, Product2.Name, Product2Id FROM PricebookEntry WHERE Id IN :prices.keySet()]);
   
    for(OpportunityLineItem record: Trigger.new) {
        lines.put(record,
            new bid_products__c(
                Name='Opp-Product', 
                Product2__c=prices.get(record.PricebookEntryId).Product2Id, 
                Unit_Price__c=record.UnitPrice, 
                Quantity__c=record.Quantity, 
                Product_Name__c=prices.get(record.PricebookEntryId).Product2.Name,   
                Opportunity__c=opl.get(record.Id).OpportunityID,
                Id=record.Bid_Products__c));    
    }
    
    upsert lines.values();

    for(OpportunityLineItem record: Trigger.new) {
        record.bid_products__c = lines.get(record).Id;
    }
}

 
Srinivas SSrinivas S
Pelase try now and let me know if you have any issues - 
trigger BidProductsInsert on OpportunityLineItem(after insert, after update) {
   
    Map<Id, OpportunityLineItem> opl = new Map<Id, OpportunityLineItem>();
    Map<Id, PricebookEntry> prices = new Map<Id, PricebookEntry>();
    Map<Id, bid_products__c> lines = new Map<Id, bid_products__c>();

    for(OpportunityLineItem record: Trigger.new) {
        if(record.PricebookEntryId != null)
			prices.put(record.PricebookEntryId, null);
    }
    
	if(opl.size() > 0)
		opl.putAll([SELECT Id, OpportunityID FROM OpportunityLineItem WHERE Id IN :opl.keySet()]);
    if(prices.size() > 0)
		prices.putAll([SELECT Id, Product2.Name, Product2Id FROM PricebookEntry WHERE Id IN :prices.keySet()]);
   
    for(OpportunityLineItem record: Trigger.new) {
        lines.put(record.Id,
            new bid_products__c(
                Name='Opp-Product', 
                Product2__c= prices.containsKey(record.PricebookEntryId) ? prices.get(record.PricebookEntryId).Product2Id : null, 
                Unit_Price__c=record.UnitPrice, 
                Quantity__c=record.Quantity, 
                Product_Name__c= prices.containsKey(record.PricebookEntryId) ? prices.get(record.PricebookEntryId).Product2.Name : null,   
                Opportunity__c= opl.containsKey(record.Id) ? opl.get(record.Id).OpportunityID : null,
                Id=record.Bid_Products__c));    
    }
    
    upsert lines.values();

    for(OpportunityLineItem record: Trigger.new) {
        record.bid_products__c = lines.get(record.Id).Id;
    }
}

------------
Thanks,
Srinivas
- Please mark as solution if your problem is resolved.
This was selected as the best answer
Snita LalSnita Lal
Hi Srinivas,

Thanks for your response. 

Copied the code exactly and hit an error saying 'read only line 32' on insert. Changed the trigger to before insert and before update, then the product was able to be saved but didn't populate the lookup back to the opportunity on the bid product record and therefore no link was created. However the bid product record does update once the line item has been changed which works perfectly. So ultimately still in a position that we need the opportunity lookup on the bid products object to be populated.

Thanks,

Snita
Srinivas SSrinivas S
trigger.new is Read Only if after triggers, made a change please try with the following code and let me know if something is wrong - 
trigger BidProductsInsert on OpportunityLineItem(after insert, after update) {
   
    Map<Id, OpportunityLineItem> opl = new Map<Id, OpportunityLineItem>();
    Map<Id, PricebookEntry> prices = new Map<Id, PricebookEntry>();
    Map<Id, bid_products__c> lines = new Map<Id, bid_products__c>();

    for(OpportunityLineItem record: Trigger.new) {
        if(record.PricebookEntryId != null)
			prices.put(record.PricebookEntryId, null);
    }
    
	if(opl.size() > 0)
		opl.putAll([SELECT Id, OpportunityID FROM OpportunityLineItem WHERE Id IN :opl.keySet()]);
    if(prices.size() > 0)
		prices.putAll([SELECT Id, Product2.Name, Product2Id FROM PricebookEntry WHERE Id IN :prices.keySet()]);
   
    for(OpportunityLineItem record: Trigger.new) {
        lines.put(record.Id,
            new bid_products__c(
                Name='Opp-Product', 
                Product2__c= prices.containsKey(record.PricebookEntryId) ? prices.get(record.PricebookEntryId).Product2Id : null, 
                Unit_Price__c=record.UnitPrice, 
                Quantity__c=record.Quantity, 
                Product_Name__c= prices.containsKey(record.PricebookEntryId) ? prices.get(record.PricebookEntryId).Product2.Name : null,   
                Opportunity__c= opl.containsKey(record.Id) ? opl.get(record.Id).OpportunityID : null,
                Id=record.Bid_Products__c));    
    }
    
    upsert lines.values();
	
	List<OpportunityLineItem> oppLineItems4Upd = new List<OpportunityLineItem>();
	//Since it is an after trigger 'trigger.new' is read only to void this query from the database
    for(OpportunityLineItem record: [select id, bid_products__c from OpportunityLineItem where id in: trigger.new]) {
        record.bid_products__c = lines.get(record.Id).Id;
		oppLineItems4Upd.add(record);
    }
	if(oppLineItems4Upd.size() > 0)
		update oppLineItems4Upd;
}

------------
Thanks,
Srinivas
- Please mark as solution if your problem is resolved.
Snita LalSnita Lal
Thanks Srinivas, I tried the updated code and unfortunately this came back with the following error when trying to add a product to the opportunity:

Apex trigger BidProductsInsert caused an unexpected exception, contact your administrator: BidProductsInsert: execution of AfterInsert caused by: System.DmlException: Update failed. First exception on row 0 with id 00kN0000005B9AuIAK; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, BidProductsInsert: maximum trigger depth exceeded OpportunityLineItem trigger event AfterInsert for [00kN0000005B9Au] OpportunityLineItem trigger event AfterUpdate for [00kN0000005B9Au] OpportunityLineItem trigger event AfterUpdate for [00kN0000005B9Au] OpportunityLineItem trigger event AfterUpdate for [00kN0000005B9Au] OpportunityLineItem trigger event AfterUpdate for [00kN0000005B9Au] OpportunityLineItem trigger event AfterUpdate for [00kN0000005B9Au]: []: Trigger.BidProductsInsert: line 38, column 1 

Thanks,

Snita
Snita LalSnita Lal
Hi Srinivas,

Thanks for your help yesterday we've managed to get the requirement working using your suggestion above for the insert/update piece, then created a separate mapping trigger to populate the lookup and a flow to fire the trigger. We've marked you as best answer as we've used this element in the solution.

Now we've just got to work out how to delete the related record when the original record is deleted from the parent...I'm sure this is fairly easy but unfortunately as mentioned previously we are pretty weak in this area.

Thanks for your help.

Snita