Crystal Reports Keeping track of the count of parts...

Message

     Afternoon all.. Hopefully I can thoroughly confuse you as much as I've confused myself..     

The company I work for builds machines. We have each machine broken down into logical assembly steps. Each of those assembly steps has a number of parts that are needed to build it. We keep track of these parts in our ERP system with inventory counts. We keep parts in inventory until all the parts for a assembly step are here (mainly so they don't get lost.. it happens a lot). I'm trying to make a report that shows all of the assembly steps for all of the machines that are being built, and which ones have all the parts here so we can pull them. That report in itself is easy enough for me to do, but to add another layer of insanity to it, I wanted to only pull the parts I had ENOUGH inventory for. So if I have 3 assembly steps that use quantity 1 of a part, but I only have 2 available in inventory, I only want to pull the first two assembly steps.

So far what I have done is make a report that is grouped by part. In the details section, I have all of the open assembly steps listed and the quantity of the part needed for each. It is sorted by the due date of the final machine so I know which parts are needed for which assembly steps first. Then I took how many we have available in inventory and subtracted out how many are needed for each line. Kind of looks something below (only condensed):

Part 12345 (has quantity 3 available in inventory)

             Qty Need    Inventory Qty Left

  Step AAAAA      1               2

  Step BBBBB      2               0

  Step CCCCC      1              -1

Part 23456 (has quantity 7 available in inventory)

             Qty Need    Inventory Qty Left

  Step AAAAA      2               5

  Step CCCCC      3               2

Part 34567 (has quantity 4 available in inventory)

             Qty Need    Inventory Qty Left

  Step AAAAA      2               2

  Step BBBBB      2               0

Where in the first line 'Inventory Qty Left' = available in inventory - Qty Need, then each line below is the previous 'Inventory Qty Left' - Qty Need. So according to this, I should be able to pull parts for Step AAAAA and Step BBBBB but not Step CCCCC because I don't have enough of Part 12345 leftover in inventory.

So then I made another report where it's grouped by the assembly step and the parts and quantities needed are in the details. Looks something like this:

Step AAAAA

              Qty Need

  Part 12345     1

  Part 23456     2

  Part 34567     2

Step BBBBB

              Qty Need

  Part 12345     2

  Part 34567     2

Step CCCCC

              Qty Need

  Part 12345     1

  Part 23456     3

What I would like to be able to do is add a column to the right of 'Qty Need' and insert the value calculated for the leftover inventory in the previous report so it would look like this:

Step AAAAA

              Qty Need    Inventory Qty Left

  Part 12345     1               2

  Part 23456     2               5

  Part 34567     2               2

Step BBBBB

              Qty Need    Inventory Qty Left

  Part 12345     2               0

  Part 34567     2               0

Step CCCCC

              Qty Need    Inventory Qty Left

  Part 12345     1              -1

  Part 23456     3               2

I've inserted the first report as a subreport into the second and linked it by the part number, but that's as far as I've been able to get. If I link the sub report to the specific step, it gets rid of the detail lines of the other steps in the first report and doesn't make the 'Inventory Qty Left' column accurate. I don't know how (of if it's even possible) to take the value from the first report and put it into the second. Or if I'm even thinking about how to do this correctly. Any support would be greatly appreciated!   