[SalesForce] Deduct Qty On Hand from Product Object when an Opportunity is Closed Won

Need help, I'm trying to figure out how can I make a trigger to update Quantity On Hand in the Product Object when an Opportunity is Closed Won.

  • OpportunityLineItems -> Quantity (Standard Field) (Number)
  • Product2 -> QtyOnHand (Custom Field) (Number)
  • ( Product2.QtyOnHand__c – OpportunityLineItems.Quantity ) = Product2.QtyOnHand__c

Opportunity Stage Updated to Closed Won, then run trigger.

Best Answer

I don't think it's wise to rely on the state of a field prior to the start of a transaction as an accurate count. Instead, you should just use a trigger on Opportunity and an aggregate query on the OpportunityLineItem object, which looks up to both Product2 and Opp.

First collect all of your Product2 Ids from the trigger set:

Set<id> productIdsToQuery = new Set<id>();
List<OpportunityLineItem> lineItems = [SELECT Product2Id FROM OpportunityLineItem WHERE OpportunityId IN : Trigger.New];
for (OpportunityLineItem oli : lineItems) {
    productIdsToQuery.add(oli.Product2Id);
}

Next, do an aggregate query on the Closed Won opps related to that Product:

List<Account>AggregateResult[] groupedResults = [SELECT SUM(Quantity) totalQuantity, Product2Id FROM OpportunityLineItem WHERE Product2Id IN :productIdsToQuery AND StageName = 'Closed Won' GROUP BY Product2Id];

List<Product2> productsToUpdate = new List<Product2>();    

for (AggregateResult ar : groupedResults)  {
    Product2 updatedProduct = new Product2(
        id = ar.get('Product2Id');
        QuantityConsumed__c = ar.get('totalQuantity');
    );
    productsToUpdate.add(updatedProduct);       
}

update productsToUpdate;

EDIT: You edited your post in the middle of my answer to change what Quantity On Hand means. You need a field for how many you have and then how many have been used (the calculation I listed) and a formula to do the subtraction for 'On Hand'

Related Topic