Here is the scenario that I need some input on:

A user enters the quantity of widgets that they produced.  They hit the save button.  The production data is stored in table separate from the widget table.  Once the user clicks save, I want the BO to update the On Hand total of the widget in the widget table.  I am thinking that I would place some code in the AfterSave() method to make this happen.  Here's the rub...what if the user entered the wrong quantity (not that this ever happens--I am just doing some just-in-case type of analysis here  ?  The user finds their production data entry and clicks edit, then proceeds to change the quantity and clicks save.  Now, what is the best way of handling this in order to update the widget table with the difference in quantity?

I have some ideas from previous non-SF days, but is there a good SF way to do this?

Thanks,
Bill 

Hi Bill,

In our case all individually produced widgets are allocated to what we call a PUN (a Packing Unit - same as an SKU - stock keeping unit). Every time a widget is created a trigger on that table recounts the number of widgets on the PUN and recreates the stock count (units/weight) and assigns the values to a PUN record.

While recalculating in-stock values of the fly is ok we steered away from that towards keeping values in the PUN for performance reasons, e.g. a full inventory report could contain 1000's of PUN's each containing about 50 widgets. Because we maintain these values via triggers they are never wrong and, except for the triggers themselves (which are very simple), there is no code involved. For instance if the user produces a widget then deletes it the counts on the PUN are incremented then decremented 'automatically' via the trigger that does a recount every time there is a insert/delete/update on the widget table and this includes things like changing the widgets weight and surface area.

When a PUN is shipped or regraded it is flagged as such in the PUN with a Shipped Date or a Regraded Date and our inventory is simply a total of all PUNs with NULL Shipped Date and Regrade Date. So, as you can see, we don't have a Produced and Shipped table just a Produced table in which the PUN's changes state.

While I understand what you are saying re, in your case, triggers being difficult you may be better of trying to rationalise your current and new application. It's hard to imagine that both systems could have a different understanding of what your inventory position is.

Cheers, Peter

All of the widget on hand quantity updates are done in code in the old application.  For example, the PartMaster table has the OnHandQty field.  It gets adjusted whenever a new record is added/edited in the Production table or the Shipments table.  Also, each month a physical inventory is taken (full plant) and these numbers (stored in an InventoryTransaction table) are synchronized with the OnHandQty in the PartsMaster table.

One of our goals is to eliminate the monthly inventories and rely on the system to present good running totals.  So, I am taking a look at this from a practical perspective with my StrataFrame helmet on.

At the moment, I am considering an InventoryAdjustments table (to handle all of those interim inventory adjustments that always seem to occur because of scrap, miscounts, etc.).  The SQL view would combine all four tables (Parts, Production, Shipments, Adjustments) and present something like this:

PartIndex, ProdTotal, ShipTotal, AdjTotal, OnHandQty

With this layout, I could total each table within the view by transaction entry date which would allow for a point-in-time inventory total.  This has great promise.  I would no longer have to code for changes in the totals...the normal BO functionality handles all of that in each of the tables.  This may be the better way to handle the inventory for us.

By the way, one flaw that I am seeing is time-sensitivity.  The longer we go with this, the more data involved in the queries.  Is there a way to data overhead?  Not seeing an easy way to do that...we would have to maintain all production, shipping and adjustment records indefinitely.  I suppose I could write something to move stuff to a history table of sorts and make a cumulative entry into the adjustment table....kind of like a beginning balance.

Well, I am rambling, now.  If anyone can see major gotchas in my approach, just holler.  I think this will help us best transition into the new application without upsetting the data structure too much.

Bill