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 Member · Accepted solution]

Brian, with your help as well as this article, I finally got a solution that works. For others out there who may be struggling with similar issues, here's what I learned.

Printing Each Record To A String

Create a formula field called ff_myRecNo:

WhilePrintingRecords;
Global numbervar recNo;
    IF
        [Suppressed line formulas]
    THEN
        //Ignore suppressed records
        -1
    ELSE
        IF recNo > -1
            THEN 
                //Count visible records
                recNo := recNo +1
            ELSE
                1

Put this field somewhere in each detail section, and you'll see it create an index for only the visible records. Suppress this field so it's out of your way, then create another formula field, OrderNumbers:

WhilePrintingRecords; 
StringVar stringOutput; 
stringVar toAddToOutput := Cstr({Order Number},0,"") + ToText(ChrW(13));
//ToText(ChrW(13) inserts a line break after the order number.

IF
    {@ff_MyRecNo} <> -1
THEN
            stringOutput := stringOutput + toAddToOutput; 

Then simply put your OrderNumbers variable somewhere in the report and you're done.

Two Important Side Notes

1. This solutions works to color alternating visible lines as well.

Use the ff_myRecNo formula to create the line index, then go to Section Expert > Details > Color (tab), and enter the formula:

select {@ff_MyRecNo} mod 2 case 0: crWhite case 1: color(222,246,252)

// or use another RGB color

2. For comparing numbers as strings, be sure they're being formatted properly

I had to use

Cstr(OrderNumber, 0, "")
//This outputs a clean number, like '123456'

not

toString()
//This outputs a decimal number, like '123456.00', ruining the string comparison