Selected Running Totals - Use Formula?

[TheLibrary]

Crystal 9
I need to monitor parts to stock and status of completion:

Part No. OnHandQty ShipQty
1000-22 20 10
1000-22 20
1000-22 15
1022-05 30 10
1022-05 10

Total to Ship 65
OnHand Ready to Ship 40

Calculating the "OnHand Ready to Ship" is the difficulty. The multiple entries of part no.s is for each of the separate sales orders. You can't just subtract the total "ShipQty" from the total "OnHandQty", each part no. needs to be evaluated then placed into the "Running Total".

Request specific help or reference to a posted FAQ.
Thanks,
Gerry

 

[synapsevampire]

Assuming that On Hand QTY is a known and not calculated, I think that a few formulas could satisfy this:

Current part number on hand qty:
// @OHCurrQty
whileprintingrecords;
numbervar CurrOH;
if on firstrecord or {table.partno} <> previous({table.partno}) then
CurrOH := {table.ohqty}

RunningTotal of OHReadytoShip
// @OHReadytoShip
numbervar OHReadytoShip;
numbervar CurrOH;
If {table.shipqty} <= CurrOH then
CurrOH := CurrOH-{table.shipqty};
OHReadytoShip := OHReadytoShip+{table.shipqty};

Now the OHReadytoShip variable should contain the qty of parts available to ship.

Lots of assumptions made here, not knowing if you ship partials, etc. BTW, this method for monitoring inventory is suspect.

Even basic MRP (material requirements planning) systems will maintain a minimum order point on the database side (not a report), and a minimum order qty (which you would add the qty of backordered parts (those you do not have enough stock to fill) to create the purchase order, if not adjust the minimum order qty).

So when an order is placed, trigger based transactions take place to generate the ordering of new parts, statistically *rethink* the minimum order qty to assist in maintaining a minimum inventory, yet allow you to conduct business.

The big kids offload this data to their vendors with blanket PO's to cover the parts, and then the vendor is tasked with filling (and perhaps adjusting manufacturing schedules) based on your orders, so check with your vendors, you may be able to offload the task on them.

Anyway, this should get you close, or hopefully it helps in some way if you're doing manual MRP.

-k