Maintaining Running Inventory

[DORaymond]

I would like to know what is considered the best way to maintain a running inventory say for products manufactured and then shipped in a factory.

Is it best to use Post operations to increment/decrement quantity-on-hand, or is it better to use some type of running total either in a calculated field, or one that can only be had through a report? If it is a Post operation, how do we know if Post failed? If it is calculated type of result, what happens to the performance when there are many records?

Thanks,
Don

[MoGrace]

Hi Don,
I have tried using posts and calc fields and had the same questions as you. So instead I use a transaction table in which matching records are created that works similar to a check register with fields for IN & OUT quantities based on the TYPE of transaction and links to the ITEM_ID. Then I can create sets and reports based on those sets to see whatever amounts I need.

In my case I receive LBS of yarn which is knit into rolls of fabric grouped into lots for shipping. And since each roll of fabric may use more than one yarn item (50/50 Poly Cotton), I need to break out the roll lbs by percentage for each yarn used. When a shipment of yarn is received I have one transaction record for the IN quantity. In my work order, I set the percentages for each of the yarn items that will be used. Then after each roll is produced, weighed and selected for shipping (creating the LOT NO), a transaction record is created for the OUT quantity that calcs the total roll weight by the percentage for each yarn item referencing the lot number (instead of each individual roll no. which would be too many transaction records).

[DORaymond]

Robin, thanks for the reply.

I would want to structure the transaction table so that I can see the quantity on hand in real-time on a form, rather than having to run a report to calculate what is on hand. I intend to have a transaction record created each time a barcode is scanned such as when a unit is produced (IN) and another scan (record) when it is shipped (OUT). Once you have IN & OUT records, how and when are you calculating QOH? What happens when the transaction table becomes large?

Regards,
Don