Warehouse Inventory Structure

May 1, 200817 yr

Hi, I'm starting up with creating inventory solutions for Warehouse using Filemaker. In creating the system, I haven't figured out how you guys deal with the approach on handling report generation using the following scenario:

1. I use a transaction table to record incoming/outgoing items, see sample below:

WHEN PURCHASING OR ADDING NEW ITEMS

Transaction Type: "Add" (this is just a marker so the system knows what type of transaction is being handled)

Item Code:

Qty: 10 (assuming this is the first time this item is encoded, remaining Qty=10 also)

WHEN SELLING ITEMS

Transaction Type: "Minus"

Item:

Qty: 3 (expected remaining for this product when calculated should be 7)

2. Using the above method, to know the exact quantity available in the warehouse, I normally do this:

a. Get total(summary) qty for "Add" transactions on the target item code

b. Get total (summary) qty for "Minus" transactions on the target item code

c. Subtract the totals, or "Add"-"Minus" transactions to arrive on having the actual remaining qty on the warehouse

3. Assuming that on the same item code, I've been doing several transactions a week doing "Add" and "Minus"

My question is this--if the program is already running for, say, two years already, does it mean that I have to run through getting the summary of the total "Add"; and the summary of the total "Minus" transactions from day 1 in order to know the actual remaining Qty?

What could be the best approach to achieve this with less toll on the system performance? (I want to prevent the system from continuously doing summaries for every date/range that the user requests it to report on the remaining qty for each items)

Please help me on this, I really need your expert advise.

Thank you.

May 1, 200817 yr

You're lucky to discover this at the beginning! Yes, relying on calcs for Qty on Hand will get slower and slower. Your best bet is a transactional model. This is an article about how to build one: FM Advisor Article , but it's only available to subscribers. Might be worth it. Basically, update the numbers as you go so that they are stored.