Need formula help!

[ddenise]

I am writing a report that reconciles sales orders to inventory - essentially, group sales orders by the scheduled production date, list out all the parts that are needed and forecast the impact on inventory, highlighting any areas where inventory is foretasted to go negative.

In my company, 'products' are given 311xx numbers and the parts used to make those products are given 811xx numbers. I'm interested in tracking the 811 numbers and the question my report is intended to answer is "Given that we are planning on building products as scheduled, when will we run out of each 811 part?" The inventory quantities you see below are for these 811 numbers.

Right now, the results of my report look like this:

Part In Inventory Needed Inventory Remaining
Order Number 567
Product Number 311187
Part Number 8111234 100 5 95
Part Number 8111235
Product Number 311189
Part Number 8111234 100 6 94
Part Number 8112235



You can see the problem is that once I calculate "Inventory Remaining", the next instance of part 8111234, should have an "In Inventory" value of 95, NOT 100, but 100 is what is stored in the database. The next time that part number is used, the database value is pulled and 100 is displayed. A running total or summary won't work because they cannot be used on 2nd pass formulas/variables; "Inventory Remaining" is a formula evaluated on the 2nd pass.

The result that I NEED to come out would be:

Part In Inventory Needed Inventory Remaining
Order Number 567
Product Number 311187
Part Number 8111234 100 5 95
Part Number 8111235
Product Number 311189
Part Number 8111234 95 6 89
Part Number 8112235



So.... I am thinking that I need to create a formula that does the following while records are printed:

1. Check to see if a variable whose name is equal to the part number that is being printed (in this case "8111234") and if it doesn't exist, then create it, setting it's value to what is in the database (in this case "100")
2. Use the variable's current value, subtract the quantity of those parts that are needed for the current record being printed and updated the variable's value to this difference.
3. If the variable does already exisit, then use it's current value to display in the "In Inventory" column and,
4. Use the variable's current value, subtract the quantity of those parts that are needed for the current record being printed and updated the variable's value to this difference.

What this would do is essentially, dynamically create variables that can be used for display and other evaluation and update the particular variable that is related to the part number that is being printed.


Has anyone done anything like this before? I'm sure I'm not the first to encounter this situation. Also, if there are other ways to solve this problem that Crystal can handle, please advice - I cannot seem to find any other solution.


I have thought that a cross tab might work and I've produced a cross tab that looks like:

811-1 | 811-2 | 811-3 | 811-n...
Prod Date
Order#
311#
Order#
311#
Prod Date
Order#
311#
Order#
311#

But all I can do is get summaries into the cross tab.


Thank you in advance,

 

[lbass]

I don't follow why you would rule out running totals. Insert a running total {#rt82111234} that does a sum of {table.needed}, evaluate using a formula:

{table.Part Number} = '8111234'

Reset never. Then create a formula for {@Inventory Remaining}:

{table.ininventory}- {#rt8111234}

-LB

 

[ddenise]

Because I have LOTS of part numbers, my example shows this working only for one part number for simplicity. I am trying to avoid manually creating a running total and matching inventory formula for each part and looking for a more efficient way of doing this.

I like your approach above and from it, my question could be rephrased to:
"can I dynamically create running totals?"

And, can I dynamically create the inventory formula? Each time I encountered a part number in the result set, I would want to implement the logic you describe above.