Calculations in inventory file

[Branwen]

Hello. I need help with an inventory system that shows reduces the number of products on hand as each one is sold. However, I don't want it to show "0" on the list that feeds the order file; I want it to show the actual number in stock.

Here's the situation. There are 4 main files: "order data" where the actual order is filed. This file includes a field called "checked" that indicates whether the order was sent [3], was canceled due to credit card or whatever [5] or hasn't been processed yet [blank]. The second file is "products sold" and is a list of each product sold with order number, stock number, etc. The data in this file comes from the "product list" file, using the stock number as the link. There is also a supplier file, but it isn't relevant to this problem.

In setting up the calculations, I started with one of the FM examples and made changes from there. Here's what I have now.

In Product List -

Quantity Sold = Sum[Products sold ::Quantity Sold ]

Quantity on Hand = Initial Inventory Level - Quantity Sold

In Products sold -

Quantity sold = If[Order data ::checked < 5, Quantity, 0]

Quantity on hand = If[Product list ::Quantity on Hand <= 0 and Order data ::checked = 3, Product list ::Quantity on Hand, Product list ::Quantity on Hand + Quantity]

The problem is that I can't get it to work right as the "checked" box changes. The current calculations work for orders pending, showing "1" on hand in the products sold if only "1" product remains, but showing "0" in the product list, indicating that there are no more available. It also works when the "checked" box shows "3" indicating "0" on hand in both files. The problem is that when the order is cancelled [checked =5], the products sold file shows "2" on hand, though the correct "1" is shown in the product list. I suppose I could just ignore this since it only occurs in cancelled orders, but it bothers. me.

Any ideas?

[Fitch]

In Product List, you get the correct number for Quantity on Hand. But in Products sold, your Quantity on hand = If[Product list ::Quantity on Hand <= 0 and Order data ::checked = 3, Product list ::Quantity on Hand, Product list ::Quantity on Hand + Quantity]

Look at the last expression. You're adding Quantity to Quantity on Hand!

So it needs to be changed to something like:

If[Product list ::Quantity on Hand <= 0 and [Order data ::checked = 3 or Order data ::checked = 5], Product list ::Quantity on Hand, Product list ::Quantity on Hand + Quantity]