Update Custom Object field when Opportunity Status Changes

Question

We have a custom object Inventory that is created by the addition of a product to an Opportunity.
On the Opportunity there can be one or more products that will ship together. When a Product is added to the Opportunity in the Opportunity line item section the Apex Trigger creates an Inventory Item with a Credit of the Quantity for each product on the Opp. Inventory records are added (Debit) when product is shipped to the Distributor and a report reflects the difference between credits (each shipment detail) and debits (additions to inventory) so that we have a running total of Inventory at the Distributor.

Now we have two distributors, and the knowledge of which distributor will ship the product is not known until after the Inventory records are created. As it is today, when a product is modified or deleted the associated inventory record is updated or deleted.

The Distributor can be determined by a custom status field on the Opportunity. So I am added a Distributor field to the Opportunity, and set a workflow rule to populate that value. Then I set a formula field on the Inventory record that is related to that Opportunity.

The Problem is that a Debit Inventory record, The Addition of product at the distributor, is not tied to an Opportunity, and with the Inventory.Distributor__c field being a formula field can't be edited.
The current workaround is to have a second Inventory.SendToDistributor__C field that is a picklist that is poplulated for supply shipments (Debits). But I would rather automate via lookup or Work Flow or Trigger.

Thank You, Kelly

Answer 1

It sounds to me as though you may want invest the effort and time to rethink your database model.

Opportunity represents a potential sale. Until it's "closed-won", the sale isn't final. From your description, it sounds like you want to make certain you have the product in inventory before you close a sale and if you do, you want to reserve it for that pending sale once it's crossed a threshold.

What you're discovering is that using a formula on Opportunity, isn't the way to track your inventory as you may not have pending sales or "sales ready to close" on every product you sell. Instead, it would seem to make more sense to have a separate "Inventory" object that contains all of your products. You can update inventory at any time you desire to as you add stock. This obviously occurs independently of Opportunities anyway.

What you can also do, is use a formula field from Opportunity, to reserve inventory for pending sales, which then tells you how much Inventory is available for you to sell to other Accounts as other Opportunities on the same product are created or mature. It will also allow you to have your other products displayed and the quantities available which don't have any Opportunities pending on them.

The text field you're proposing with your existing database model is simply putting a bandaid on a database object that doesn't fit your circumstances. I highly recommend you rethink your model and restructure it to fit your actual business model.

Edit

After looking at your revised question. My recommendation would be to remove your inventory management almost entirely from Opportunity and use workflow to manage it based on Opportunity without being constrained by Opportunity. In essence, use workflow to create the credit of the item with the quantity when it reaches the stage where the product is shipped to the specified Distributor and remove the trigger that's currently doing the work. This will allow you to add products and inventory without going through Opportunity.

If you need the information from Opportunity in order to create certain products or additional product inventory, then I'd recommend you 1st populate that data to an intermediate object. You could then use workflow to manage your inventory on those items based on inserts and updates to the intermediate object.

The above allows Opportunity to drive your inventory management, but doesn't constrain it as it currently seems to based on our earlier exchanges.