1

I created Order and Order Line Item custom objects they are similar and related to Opportunity and Opportunity Line Item. When I create an Opportunity, a related Order automatically created, when I add a product to Opportunity Line Item, same product be added automatically to Order Line Item of related Order. I don't have a problem with inserting product but I can't make it update. How to achieve it? This is my code for insert but I couldnt make that product on Order Line Item update, please help me. Thanks.

trigger CreateOrderLineItem on OpportunityLineItem (after insert, before delete) {
if(trigger.isInsert){
     Map<Id,Product2> proMap = new Map <Id,Product2>([Select Order_line_Items__c From Product2]);
    List<Order_Line_Item__c> oliList = new List<Order_Line_Item__c>();

        for(OpportunityLineItem opli : [Select opportunityid, Id, pricebookentryid, unitprice, quantity From OpportunityLineItem Where id IN:Trigger.newMap.keyset()])
        {
                for(Opportunity oppList : [Select id, Orders__c From Opportunity Where id=:opli.OpportunityId])
                {                    
                    for(PricebookEntry pbe : [Select pricebook2id, product2id, unitprice, IsActive From PricebookEntry Where Id=:opli.PricebookEntryId])
                    {
                        for(Pricebook2 pb2 : [Select Id  From Pricebook2 Where Id=:pbe.Pricebook2Id])
                        {
                        for(Product2 pro : [Select ProductCode, IsActive, Order_Line_Items__c From Product2 Where Id =: pbe.product2id])
                        {
                            for(Order__c orrList : [Select Id, Opportunities__c From Order__c Where Opportunities__c =:oppList.id])
                            {
                                Order_Line_Item__c oli = new Order_Line_Item__c(product2__c=pro.id, Order__c=orrlist.id, UnitPrice__c=opLi.UnitPrice, Quantity__c = opli.Quantity, ProductCode__c=pro.ProductCode, id=pro.Order_Line_Items__c);
                                oliList.add(oli);
                            }
                        }
                        }
                    }
                }
        }
        if(oliList.size()>0)
        {
        insert oliList;
        }
}

1 Answer 1

4

I think easiest would be to do a bit more work in after insert, set the Id of the Order Line Item on the Opportunity Line Item. You probably have there something similar to this:

OpportunityLineItem oli = new OpportunityLineItem(OpportunityId = ...,
    PricebookEntryId = ...,
    Quantity = orderLineItem.Quantity__c,
    UnitPrice = orderLineItem.Price__c
);
items.add(oli);
// (...)
insert items;

Simply add 1 more field there:

OpportunityLineItem oli = new OpportunityLineItem(OpportunityId = ...,
    PricebookEntryId = ...,
    Quantity = orderLineItem.Quantity__c,
    UnitPrice = orderLineItem.Price__c,
    Source_order_line_item__c = orderLineItem.Id,
);
items.add(oli);

Then in after update you can fetch all matching Opp Line Items:

List<OpportunityLineItem> olis = [SELECT Id, Source_order_line_item__c
    FROM OpportunityLineItem
    WHERE Source_order_line_item__c  IN :trigger.newMap().keyset()];

And go through them

for(OpportunityLineItem oli : olis){
    Order_Line_Item__c matchingOrderLine = trigger.newMap().get(oli.Source_order_line_item__c);
    oli.Quantity = matchingOrderLine.Quantity__c;
    oli.UnitPrice = matchingOrderLine.Price__c;
    // etc
}
update olis;

You probably could even improve the last part by querying with "dot" directly (if it will be after update trigger so the new values are saved to database):

SELECT Id, Source_order_line_item__r.Quantity__c, Source_order_line_item__r.Price__c
FROM OpportunityLineItem
WHERE Source_order_line_item__c  IN :trigger.newMap().keyset()

No need to use trigger.newMap() anymore, directly overwrite the values

for(OpportunityLineItem oli : olis){
    oli.Quantity = oli.Source_order_line_item__r.Quantity__c;
    oli.UnitPrice = oliSource_order_line_item__r.Price__c;
}
update olis;

If you don't want to do this extra step during insert (or are worried about existing data) it gets much trickier because how do you know that this line on Opportunity was based on that line on Order? You'd have to somehow consider them matching if for example Product (or PricebookEntry) + Quantity + Price match (you could have multiple lines on the order about the same product). But then the whole point of update is that values can change ;) So you would have to be matching OppLineItems in DB with trigger.old but write values from trigger.new. Hardcore...

It's doable but a lot can go wrong in code like this. Just identifying the source reference can save you a lot of grief later on. And since it'll be a lookup you could easily build reports that list values side by side to look for problems.


Last edit I'm not touching it anymore, you're doing it wrong and it'll haunt you.

/*  On update of opportunity line item(s)
    try to find matching Order_Line_Item__c and update it.

    there's a lookup(Opportunity) on Order__c and Order_Line_Item__c has lookup(Product2)

    I really struggle to understand why you DON'T want to set "source line item Id" on order line item,
    similarly how you DO set "source Opp Id" on Order... True, we can't make lookups to Opp Line Items but you could have Text(18) field marked as external Id and life would be simpler.

    It is a really tricky piece of logic and I don't guarantee it'll work. Having a helper field with source would make stuff much easier.
    Or you could just say "screw it" and on update of line items delete & recreate the order lines completely (you already have code to create them in the on insert trigger).

    But ok. It's your maintenance nightmare.
    Assumption - on given order there combination "opportunity id - product id - quantity" must be unique. If on 1 order you have 2 lines about same product and same quantity - I don't know which one to update.
*/

trigger syncOppLineItemToOrderLineItem (after update){

    // 1. On Opp Line Item we don't have product links but the pricebook links. Need to get the product ids so let's start with getting a "dictionary".
    Set<Id> pricebookEntries = new Set<Id>();
    Set<Id> opportunityIds = new Set<Id>(); // we'll need these later too
    for(OpportunityLineItem oli : trigger.old){
        pricebookEntryToProduct.add(oli.PricebookEntryId);
        opportunityIds.add(oli.OpportunityId);
    }
    for(OpportunityLineItem oli : trigger.new){
        pricebookEntryToProduct.add(oli.PricebookEntryId);
    }

    Map<Id, Id> pricebookEntryToProduct = new Map<Id, Id>();
    for(PricebookEntry pe : [SELECT Id, Product2Id FROM PricebookEntry WHERE Id IN :pricebookEntries]){
        pricebookEntryToProduct.put(pe.Id, pe.Product2Id);
    }

    // 2. Build the keys consisting of "opportunity id - product id - quantity" (old values)

    Map<String, Id> keysFromTriggerOld = new Map<String, Id>();

    for(OpportunityLineItem oli : trigger.old){
        keysFromTriggerOld.put(oli.OpportunityId + ' ' + pricebookEntryToProduct.get(oli.PricebookEntryId).Product2Id + ' ' + oli.Quantity, oli.Id);
    }
    System.debug(keysFromTriggerOld);

    // 3. Fetch all Order Line Items that are based on Opportunities which are being modified here (that's why we needed the set)
    // and that are related to products we know we can match.
    List<Order_Line_Item__c> orderLines = [SELECT Order__r.Opportunity__c, Product__c, Quantity__c
        FROM Order_Line_Item__c
        WHERE Order__r.Opportunity__c IN :opportunityIds AND Product__c IN :pricebookEntryToProduct.values()];

    // 4. So based on values from order lines we'll be able to match it to old OppLineItem. And the Id will be same as new OppLineItem (obviously).
    // So we can go order line -> "key" -> old opp line -> id -> new opp line's values -> copy them to the order line.

    for(Order_Line_Item__c oli : orderLines){
        String key = oli.Order__r.Opportunity__c + ' ' + oli.Product__c + ' ' + oli.Quantity__c;
        System.debug('Looking for ' + key);
        if(keysFromTriggerOld.contains(key)){
            Id oppLineItemId = keysFromTriggerOld.get(key);
            System.debug('Found! ' + oppLineItemId);

            OpportunityLineItem newValues = trigger.newMap(oppLineItemId);

            oli.Product__c = pricebookEntryToProduct.get(newValues.PricebookEntryId); // serious black magic here
            oli.Quantity__c = newValues.Quantity;
            oli.Unit_Price__c = newValues.ListPrice;
            // copy over more fields from "newValues" as needed
        } else {
            System.debug('Could not find the match: ' + oli);
        }
    }

    // 5. Save. Uff.
    update orderLines;
}
4
  • Thank you for your answer but, in OpportunityLineItem oli = new OpportunityLineItem(...) query it's Order_Line_Item__c, not opportunityLineItem, I used OrderLineItem Id to connect Order Line Item with Product2 already, cannot use it for Opportunity Line Item connection, it doesn't work if I change it. I was thinking using old.map, new.map in (trigger.isupdate) part, I think this is only way. Can you please me about this part? Thanks.
    – user5204
    Commented Jan 21, 2014 at 10:09
  • Your trigger will consume lots of queries so I think some time in future you'll face need to change it anyway. And I'm afraid I didn't get your comment about "I used OrderLineItem Id to connect Order Line Item with Product2 already, cannot use it for Opportunity Line Item connection" - I'm talking about new field on Opp Line Item that would be lookup to Order Line Item... I'm at work now so I can't write code I'm not being paid for, maybe I'll revisit it in evening... How would you match the line items? Is there an unique combination? Can 1 product appear only once on order or multiple times?
    – eyescream
    Commented Jan 21, 2014 at 10:28
  • Yes. I have lookup between Opp Line and Order Line already but I used Order Line Id in "id=pro.Order_Line_Items__c" (pro means Product2) trigger already.
    – user5204
    Commented Jan 21, 2014 at 10:36
  • When you have time can you please help me about my trigger, eyescream? I still couldn't solve it. Thank you.
    – user5204
    Commented Jan 23, 2014 at 0:30

You must log in to answer this question.