Skip to Content
avatar image
Former Member

Print each record into a string

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!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    avatar image
    Former Member
    Dec 09, 2016 at 07:21 PM

    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
    Add comment
    10|10000 characters needed characters exceeded

  • Dec 06, 2016 at 10:17 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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?

  • Dec 07, 2016 at 09:48 PM

    Hi Matt,

    Rather than do this in the Detail section, can you create a group based on {eorder Number}? Then move this formula in the Group Footer? This will make sure only the unique orders are processed and not duplicate them.

    You can remove the InStr part of the formula as well.

    BTW, I used the InStr function with two variables and it did work.

    Thanks,

    Brian

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Grouping by order number would make this much easier, and I would love to do just that. However there are loads of order numbers that are suppressed that create 'blank' groups. For context, this report looks at all orders from the previous day and flags abnormal values. What is/isn't abnormal is determined by 5 very long and complex formula fields, so there's way too many criteria to simply exclude orders with normal values via the original query.

      If I suppress the group headers I still have the blank group detail lines, which just gums up the appearance of the report. Like this:

      If you know how to completely suppress blank group headers and their blank detail lines I would be extremely grateful. I've scoured the web and haven't found a way to do this. It would be an excellent solution for this and another report we're working on.

      Edit: Fixed some bad grammar

      groups.png (17.3 kB)
  • Dec 08, 2016 at 10:55 PM

    Hi Matt,

    When you right-click the section in the left margin and go to Section Export, you should have the option to Suppress Blank Section. Put a check in there and see if that works. If not, then you can click the formula button beside Suppress Blank Section and create a formula that also checks if a field or formula is equal to an empty string or space like:

    {table.FIELD} = "" or {table.FIELD} = " "; 

    Good luck,

    Brian

    Add comment
    10|10000 characters needed characters exceeded