Hi John
Methinks this can get mighty complicated. Many years ago (FMP5) I did
something rudimentary along these lines... Much water under the bridge
since then. But here's where I'd start my thinking...
Each product record has a simple number field for current inventory.
When a sales order is shipped out, this inventory level is adjusted
down by the quantity of the order. When a purchase order arrives, it
is similarly incremented. Simple so far.
When sales orders are entered into the system, the quantity can be
thought of as taking a kind of option against the current inventory,
with the option due on the planned ship date. The option remains in
place until the order is shipped (at which point the current inventory
is decremented by this quantity) or cancelled (in which case the
option is removed). Similarly, a purchase order entered into the
system can be treated as a potential addition to current inventory –
and if it actually arrives in the warehouse on the due date it is
added to current inventory.
So to determine if a particular item will be available on some
requested shipping date in the future, you first locate the current
inventory. You then need to somehow locate all expected deliveries in
purchase orders up to the requested shipping date and add that figure
to current inventory. Then you need to find unshipped (unfulfilled)
orders for that product with promised shipping dates up to and
including the requested shipping date and subtract that. If the
remainder is greater than or equal to the quantity of the order in
question, then the order can be fulfilled.
Obviously, unless inventory is low, you do not need to do this
calculation for every sales order. But you probably do need to compare
the quantity of the order against current inventory minus unfulfilled
orders in each case. If the inventory is insufficient, you'd then need
to check all expected deliveries to see sufficient inventory will
arrive in time. (IIRC, in my old system we only subtracted unfulfilled
order quantities from current inventory to determine if an item was
definitely available on a particular date. If this check indicated a
possible problem, a partially manual check of incoming purchase orders
was carried out.)
As I say, this can get pretty complicated. How far you go depends on
how serious your needs are. However, I don't think you are going to
find a simple flag calculation that will do the job for you.
Steve
On Jun 9, 2010, at 10:25 PM, John Wenmeekers wrote:
Hi all,
I need some how to ideas about a stock/inventory follow up.
We have an inventory follow up with all the needed fields and calcs
for
items on hand, available etc.
What we want to see is as follows (we ares till in the 'thinking
status'):
An item is on order at a supplier and will be delivered date X.
A customer orders date Y (one week before date x) for delivering
date Z (3
days after date X)
How to calculate a flag value that the item will be 'in the house' for
delivering the moment (date Y) the customer orders?
Other negative example:
An item is on order at a supplier and will be delivered date X.
A customer orders date Y (one week before date x) for delivering
date C (2
days before date X)
How to calculate a flag value that the item will NOT be 'in the
house' for
delivering the moment (date Y) the customer orders?
I hope this is somewhat clear......
TIA
