Showing upto so many records

[SueSp]

Hi folks.

I have a requirement to show on a report only enough records to satisfy a quantity.

For example:
I have to show that a Quantity 10 of Part A are required.
I have 20 of Part A on hand.

I want my Report to only show the first 10.

Any ideas how to do this? I'm stuck.

Thanks!

 

[synapsevampire]

You're assuming that others understand your database structure, not sure why...

Post technical information:

Crystal version
Database/connectivity used
Example data from your database (show the tables and fields)
Expected output

-k

 

[SueSp]

You're right. I'm sorry.
CR10 Advanced / CE10
MS SQL

Tables:
Inv.Qty
Inv.Part
Inv.SerialNumber
Ord.Qty
Ord.Part

Required
Ord.Qty = 100
Ord.Part = A

Records in Inv table=
Inv.Part Inv.Qty Inv.SerialNumber
A 10 1234
A 10 1235
A 10 1236
A 10 1237
A 10 1238
A 10 1239
A 10 1240
A 10 1241
A 10 1242
A 10 1243
A 10 1244
A 10 1245
A 10 1246

On the report I need to show only those records that match Ord.Part of "A" and show enough records until the sum of Inv.Qty is <= to Ord.Qty. Of the records shown above in the Inv table, the report would only show the first 10 records. I don't want to show all Inv records. I only want to show enough records to satisfy Ord.Qty which equals 100.

I sure hope this makes sense...
Sue

 

[synapsevampire]

Makes good sense, thanks.

The quick and dirty method within Crystal would be to use the Suppress formula of the details to suppress anything beyond the amount.

Group by the part number (inv. part) and then the following formulas should work:

Group header formula:
whileprintingrecords;
numbervar Partsneeded:= {ord.qty}
numbervar currpartsqty:=0

Details section formula (you can suppress it):
whileprintingrecords;
numbervar Partsneeded;
numbervar currpartsqty:=currpartsqty+{inv.qty}

In the X2 of the details (right click the details and select Section Expert and click the X2 next to suppress(no drill down) place something like:

whileprintingrecords;
numbervar Partsneeded;
numbervar currpartsqty;
currpartsqty > Partsneeded

-k

 

[SueSp]

Hmm. Ok. Thanks a ton synapsevampire. I'll need to digest this a little and give it a shot.

I'll let you know how it works.

Thanks again.

 

[SueSp]

Wow. It works! synapsevampire can you explain to me why it works. I'd like to understand better what is going on with the formulas you suggested.

I think I understand what WhilePrintingRecords; is doing, but not sure how the numbervars are being set and/or reset.

Thanks so much!