[SalesForce] How to update Opportunity Line Item product on a related custom object

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;
        }
}

Best Answer

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;
}

Related Solutions

[SalesForce] Populate a Lookup Field on OpportunityLineItem with Custom Object Record Just Created from Opportunity Line Item Trigger

So you just need to link (via lookup field) to the newly created Commission__c from the LineItem record? I think you can just do it in a before trigger for OpportunityLineItem:

Create Commission__c (insert) - you automatically get the Id back for any insert
Take the Id from Commission__c's Id and stuff it in the lookup field of OLI
Since it's a before trigger, you're done - no separate update needed!

[SalesForce] How to provide relation between a custom object and Opportunity Line Item

You cant' make a lookup to OpportunityLineItem. Best you could do is some Text(18) field filled in with triggers. You could make the lookup from Opp Line Item to Order Line Item but it'd look bit stupid (Order -> Opportunity but Opp LI -> Ord LI).

Nonetheless - a rough idea how to fetch Opps because that's a start anyway:

Fetch Orders related to line items from current trigger's scope.

Set<Id> orderIds = new Set<Id>();
for(Order_Line_Item__c oli : trigger.new){
    orderIds.add(oli.Order__c);
}

Fetch Opportunities (and Pricebook selections on them) that these Orders point to. Consult http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_erd_products.htm to understand the relations
```
Map<Id, Order__c> ordersMap = new Map<Id, Order__c>([SELECT Id, Name,
    Opportunity__c, Opportunity__r.Pricebook2Id
    FROM Order__c
    WHERE Id IN :orderIds]);
```

To create an Opp Line Item you need Opportunity Id:

for(Order_Line_Item__c oli : trigger.new){
   Id oppId = ordersMap.get(oli.Order__c).Opportunity__c;
   ...
}

and PricebookEntryId (I'm skipping the price, quantity etc). That one will be tricky because we don't know what do you have on the line items? Lookup to Product2? What do people actually fill in? You said you've made a lookup to pricebook, but it might be bit of duplicate info (the pricebook entry you'll end up picking has to match the opportunity's pricebook or the line won't save). You will need a query similar to this:
```
SELECT Id, Pricebook2Id,Product2Id
FROM PricebookEntry
WHERE Pricebook2Id = :ordersMap.get(oli.Order__c).Opportunity__r.Pricebook2Id
AND Product2Id = :oli.Product__c
```

Of course if you'll store say product names on Order line items, you could go ... AND Product2.Name = 'Generator' in that last line. You might also get multiple entries for that product in the pricebook if you have multiple currencies for example...

Up to you how to go from here...

Best Answer

Related Solutions

[SalesForce] Populate a Lookup Field on OpportunityLineItem with Custom Object Record Just Created from Opportunity Line Item Trigger

[SalesForce] How to provide relation between a custom object and Opportunity Line Item

Related Topic