Print each record into a string

[Former Member]

Hello world!

I've been fighting with this for hours at this point and am finally going to ask - I haven't been able to find this answer anywhere online:

I need some way to select each visible record in my report in a loop, something like {OrderNumber}[i]. Here's my code:

local NumberVar i := 0;
local NumberVar recordCount := Count({eWRLocations.Location Code});
global StringVar stringOutput := "";
local stringVar toAddToOutput;
local NumberVar currentrecord;

WHILE recordCount >= i DO
    (
    currentrecord := ***{Order Number}[1]***;
    toAddToOutput := Cstr(currentrecord, 0, "") + ToText(" ");
    stringOutput := stringOutput + toAddToOutput;
    i := i + 1
);

IF stringOutput = "" THEN "Nope, you messed up"
ELSE
    stringOutput

Everything I've tried up to this point (like RECORDNUMBER) just repeats the total count of records, as in, 861 repeated 861 times.

Two other things to consider

This report is an aggregate of several reports and has 6 different details sections, so using a counter for the detail lines doesn't work

and

there's a bunch of hidden rows so I'd like to only include visible (non-suppressed) rows in the final string output.

The End Goal is to compile a list of visible order numbers into a single string field in my report. It's odd, I know, but makes an audit process much faster. In the end, I'd like to turn something like this:

10001 A

10002 B

10003 B

10004 C

...

10100 AA

to something like:

10001 10002 10003 ... 10100

Thanks in advance!

[former_member292966]

Hi Matt,

Using a loop won't work here because Crystal executes a formula in the section you place it in. What you need is a running total and not a loop.

Try this, in the Detail section have a formula like:

WhilePrinintRecords; 
StringVar stringOutput;
stringVar toAddToOutput;

toAddToOutput := Cstr({OrderNumber},0,"") + ToText(" ");
stringOutput := stringOutput + toAddToOutput;

In the Report Footer have a formula like:

WhilePrinintRecords; 
StringVar stringOutput;

You don't have to show this formula but it will run for each row in the detail section. This way you don't need the loop at all.

You will have to include an If in the detail formula to exclude the suppressed rows. Other than that, this should work.

Good luck,

Brian

[Former Member]

Thanks! This has gotten me almost all of the way there. One more issue though - The running tally adds an entry for each record, and if there's multiple errors on a given order number (which happens often) it'll add multiple entries of the same order number.

To combat this I've modified your suggestion to:

WhilePrintingRecords; 
StringVar stringOutput;
stringVar toAddToOutput;
Stringvar currentOrderNum := ToText({eOrder Number});
    IF
        INSTR (stringOutput, currentOrderNum) > 0
    THEN
        stringoutput
    ELSE
        toAddToOutput := Cstr({eWRServiceOrders.Order Number},0,"") + ToText(" ");
        stringOutput := stringOutput + toAddToOutput;

The idea being it searches the stringOutput for the current order number and skips adding it again if it's already in the string. Apparently the INSTR() doesn't work with two variables. This stackoverflow question points out that, for VBA at least, this compares the binary, so it's better to do a textual comparison. So I've tried using alternative options like

currentOrderNum IN stringOutput

with no avail.

What operators are out there can look for a string variable value inside another string variable?