0

How can I track product stock levels across locations?

Our business process is basically this:

When an oppy is closed won a trigger will automatically create a contract (not used by us - consider it a dummy record) and an order (which needs the contract). The order will have the price book and products automatically populated from the oppy alongside some other pieces of information like account/contact etc. Anyway when the Order is Activated I want to track our product stock levels decreasing and need to account for the stock being removed from diferent locations (so various locations have various stock levels).

I have created a schema like the following, The Standard Objects Order, Order Line Item, Price Book, Price Book Entry and Product are used. I have created custom objects as lookup relationships from Orders:

  • Payment Terms (to relate payment terms to the order (which will have a PDF invoice associated with it (visualforce)).
  • Payments (to track payments made to complete the order)
  • Deliveries (to store tracking numbers from shipments to fulfill the order)

And I have the following lookup relationship against Products:

  • Stock Location (to store locations that can be used to store stock)

    enter image description here

I really only had two ideas to achieve this, the model above forces me to have to have duplicate products, one for each stock. However this can't be the best method...

The only other idea I had was to store the location on the order so we can see what is shipped from each location. However this doesn't tell us what quantity we have in stock, only what has been shipped out.

I apologies if this is the wrong place to ask, I just need a little advice in database design/schema design. I think I have over complicated it :(

1 Answer 1

1

This is a pretty high-level question, so I'll certainly caveat this with "haven't done full discovery/there may be more than one right answer/your mileage may vary".

I think your Product Stock/Stock Location model is not ideal for your use case and is going to cause you some issues, as your question notes.

I would call Product Stock "Stock Movement" and create it as a detail object under Stock Location as the master. Give Stock Movement a number field Quantity and treat negative values as expenditure of stock and positive numbers as an addition of stock.

Add a lookup field (not master-detail) from the Stock Movement to the Order Line, not the Order.

When your order commits, create a Stock Movement for each Order Line, with a negative Quantity equal to the amount order times -1, parented under the Stock Location from which you plan to fulfill the order. When you restock, create a Stock Movement for the Stock Location with a positive Quantity, and don't link it to an Order Line.

Add a Roll-Up Summary Field to Stock Location to roll up the SUM of Quantity on Product Stock Movements, and call it "Inventory". Then you've always got access to the total inventory available in each location. Optionally, you can also roll up the total Inventory across Stock Locations to the Product level.

I think this data model will make your automation and your reporting a good deal easier. It's loosely similar to how the ERP managed package Accounting Seed handles inventory.

5
  • Thanks, I actually have a lot of that in place for the calculations but knew my schema was off. I have added a new image above. Is that better (Stock Movement amended/created), my final question would be - how do I represent what stock is being removed from the location? Presumably I need a drop down on Order with all our locations, then I can use that + the order line item to create the negative stock movement record with the right location value....then the roll up summary on the Location Object will show all of the associated stock for each location? Jan 15, 2019 at 10:42
  • Yes, I think that that summary is accurate. There will be some custom development involved beyond your data model, I think.
    – David Reed
    Jan 15, 2019 at 15:12
  • Thanks, I have done quite a bit of the triggers and managed to get the order/stock part working today. In reality I, for some reason, really got stuck with the location aspect and needed a second pair of eyes. I'll be working on it this week so will soon find out if there are problems. I appreciate the help - thanks. Jan 15, 2019 at 16:54
  • Actually I added the stock location to the order line item instead. It means the same order can add the same product on but from two different locations if need be... Jan 16, 2019 at 17:20
  • Yes, that seems best. Good call.
    – David Reed
    Jan 16, 2019 at 17:21

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .