Record Selection

[angel00079]

I want to create a sales report to show how much a customer would pay. There is the sales file and the price file. Each item will have several records in the price file depending on the qty.

If there is a sales order for 16 screws I want the report to pick up the price for 15. If I only want to pick up the price record where the qty is 15 is the best option a while loop or would this still give me more than one record?

Example of Price file

Item Qty Price
Screw 1 10
Screw 5 9
Screw 10 8
Screw 15 7
Screw 20 6
Screw 25 5
Nut 1 10
Nut 5 9
Nut 10 8
Nut 15 7
Nut 20 6
Nut 25 5

 

[synapsevampire]

Consider placing the Price File in an array.

It's size might prohibit this, in which case a subreport which gets the qty pssed and then returns the price might make sense.

Questions first:

Crystal version?
(should have known that you need to supply the above with any softeware post about any product, 1st question tech support asks, well, after your credit card number...)
Database used?
How many rows in the price file?

-k

 

[angel00079]

Sorry, the version would help wouldn't it. It is X1. Reporting over JD Edwards. There is about 10000 records in the price file. I thought about using a subreport but would that give me three records (qty 15, 20 and 25) if the data selection criteria was the sales qty is greater then price qty?

 

[synapsevampire]

You can pass valuesback and forth from main reports and subreports using shared variables.

So you can pass through your value and then do an iterative lookup where it's not greater than, but it's the max.

If it's always increments of 5 it'd be easier (sample data shows this), but I doubt that's the case...

So the main report passes a parameter of the item tolimit the rows ot a reasonable number.

Then we use a formula to iterate through the rows for the greatest qty <= to the value passed in the shared variable and take that price into another shared variables and toss it back to the main report.

Gonna be nice and sloooooooooooooooooooooooooooooooooow!

Best would be to have yur dba work out some codefor this in advance in an SP or a View.

But it can be done in CR...

-k