For Loop - breaking down summary to single records 1

[Vera1]

Hi,

I have a table containing orderdata, the table looks like this:

ORDER PART QUANTITY
5455168 FK1211 10
5455168 FK451 4
5455168 AGSNT01 1
5455168 AS771 2
5455168 AS551 3

I want to break down the lines to individual lines instead of being summarised. So in total 20 lines should be displayed.

I have tried with the following code, but it won't work:

for i := 1 to {ORDERLINES.QUANTITY}
{ORDERDETAIL.PART}
i + 1
next i

Any help would be appreciated.

 

[Madawc]

You have one record for FK1211, but you want ten detail lines for it. Correct?

You could get this result using multiple detail lines. Right-click on the detail section and choose 'insert section below'. Create ten or more, your maximum. Then selectively suppress. Right-click again on extra sections and choose Format Section. Then choose the formula icon (x+2 and a pencil) for suppression.

QUANTITY >= 2

The same for each of them.

Alternatively, if you also have a table for orders, you can do it more simply by linking your table to the orders, you then get one detail line per order. Note also that 'orderdata' records with zero orders would be suppressed unless you use a 'left outer' link.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[Vera1]

Madawc,

Thanks for your reply. Indeed I want 10 lines of FK1211, your solution will work for a orderline I quess up to 100 pieces, but when a order hold 1500 times, well that makes it impossible to handle.

Your alternative by using the ORDER parent table won't work. ORDERDATA is the table with the orderlines and the most detailed table of the content of the orderlines.

 

[Madawc]

I don't see a solution. Can anyone else help?

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[lbass]

Please see: faq767-3227.

-LB

 

[Vera1]

Thanks lbass, but it won't work. I've tried but I find it hard to believe that this could work. The relation between ORDERS and REPEATER will only show records equal or greater than the value in ORDERS. So when a order has a qty of 20 than it should display the number of records that the table REPEATER holds until the maximum value is reached. However it does'nt show more records at all.

 

[lbass]

I have used this method and it should work for you, so I wonder whether you implemented it correctly. Please try it again.

You should first create an Excel spreadsheet with numbers in one column up to the maximum number of repetitions, let's say 20. Then link FROM your table TO the spreadsheet using an inner join, enforce both, >= join from the quantity field to the spreadsheet number. The value in your table will repeat once for each time it is greater than or equal to the value in the second table.

-LB

 

[Vera1]

Thanks lbass, it works!

On friday I could'nt change the link options, prabably some bug in version 8.4. Today I added a table to the same Oracle database that is the source for my report. Now I can change the link options and it works. I quess the SQL statement just won't work in 8.4

Thanks-a-lot!

Vera