On Jun 10, 2010, at 2:56 AM, John Wenmeekers wrote:

> What I have so far to control the inventory is:
> + Initial on hand qty
> - inventory transfer
> - scrapped qty
> - lost/damaged
> + total received
> + total produced
> - returned to Vendor
> - total consumed
> This should give the total inventory on item level.
> + total pending PO
> - total pending SO
> + inventory transit
> + total backorder PO
> - total backorder SO
> This should give the total available on item level.
>
> I think I cover pretty much every possibility (hope)
> I think I have to play with dates and some operators within or  
> between the
> above fields to end up with a 'yes' or 'no'.

John

The above may or may not be sufficient. That depends on your  
situation, I guess.

The only comment I would make would be this: don't attempt to  
calculate all of this on the fly (from initial inventory) every time  
you need an inventory figure (such as when entering a sales order line  
item). You will wait while it calculates, and the wait will increase  
as time goes by and more related records have to be summed.

You need to consolidate transactions into a simple number field at  
some point so as to limit the number of records of each type of  
transaction that have to be summed. My preference, for simplicity, was  
to do the consolidation at the actual moment of the transaction. So  
for example, as I explained, entry into the database of a sales order  
would place an option on a certain amount of inventory, and that would  
be subtracted on the fly (by calculation) from current inventory. At  
the moment of dispatch, that option on the inventory is exercised and  
the current inventory figure is adjusted downward.

I think others might suggest a scheduled consolidation process. You'd  
find, on some regular timetable, all completed transactions and  
consolidate them all at once into the current inventory field. In FMP5  
days, this seemed like overkill for my client's needs. In particular,  
it would have been difficult to enforce a schedule for consolidation  
without a plugin or some custom software or suchlike.

HTH

Steve