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