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,

Personally I would do all that kind of work using database triggers.

Cheers, Peter

Thanks, Peter!  Triggers do seem the best way.  Just wondering if there were any business layer methods to doing this.

The problem is that I currently do not have triggers on the DB objects.  If I add them, then the current application (which uses the sames tables) will have some difficulties.  Trying to mesh a couple of systems together with least amount of fallout is quite a touchy procedure.

Bill,

I would say you could use the fieldchangedevent to accomplish this. In the bomapper under properties you can turn the generation of these events on. Once you turn on the event generation and rebuild your business object you can handle the event so whenever the field is changed in the main bo child business object is also updated. I use this in several places and it works great, also bound controls automatically update so it makes it very nice.

Paul

Interesting.  Should I set the Property Changed Event to "Unique Event for Each Property"?  Not sure how I could use this to my benefit...I would have to think about this one a bit.  Thanks for the tip.

Bill

All of these are great ideas...here is another idea that we use.  In fact, I am in the middle of creating some quanity on hand calculations for inventory items within our medical software.  We do not store the quantity on hand in the table at all.  This is better as a calculated field.  You can create a view, user-defined function, or stored procedure to get this amount for you dynamically.  This way it is ALWAYS right and you never have to worry about updating a field (which is the approach that we used in the past).

We have a transaction posting section for our inventory items that allows the end-user to post the purchase of new items, which in turn updates the tran table showing a quantity of X amount that has been added to inventory.  Then there is a transaction posting when the item is actually sold, which then would be a decrement of the QOH.  The view takes all of this into account and returns the value for me....this was a bit of an adjustment to our thinking at first, but has turned out to be MUCH faster, reduce errors, and be a better road to travel altogether.

Bill,

I usually set it to be a single event for each property as Strataframe passes the field name in the event args. However I also have created my own event and event args and then raise the  custom event by adding the logic in the custom code section of the BO mapper.

For instance I handle events on reg and o.t. hours worked and pay rate fields with one event handler. in handling these events I can then recalculate gross pay whenver any of these fields change which in turn raises another event that calculates taxes and updates the check business object's value for withholding taxes etc. So in the Ui when a user changes something the event is raised and handled which then updates other values in other bound business objects causing all the bound controls to automatically refresh which makes things real real nice.

As Trent said views are great!! I write payroll software and in foxpro I keep running totals of week to date hours taxes garnishments etc and year to date hours etc in seperate tables, well these tables always seemed to get screwed up and I would have to run routines to recalc them. I now have all this type of logic in views so no more totals tables and the data is always accurate, so depending on what you need to do a view may be a better choice, hopefully this helps a little.

Guys...great discussion...thank you!!

Let me see, then...to gather the widget on hand quantity I would run a query (in a view) that would do something like this:

Production table (get total of the widgets made) - Shipping table (get total of the widgets shipped) = Total OnHand Quantity

What about inventory counts when actual and on hand do not match?  How are those handled?  Inventory adjustment table?

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