Apending Strings in v8.5 into one long string over 256 Chars

Tuesday 19 February 2008 @ 12:31 pm

I have a formula on my site that appends a series of string values from consecutive records and turns them into one long string. But if you are using version 8.5 there is a limit of 256 characters of output for any formula which limits the usefulness of this formula. But Shelly Petersen of Computer Arts, Inc. recently gave me a draft of a variation of my formula that allows v8.5 to get around this limit. It splits the string into several variables that are each under the 256 limit, and then she uses separate formulas to display each variable. She puts all of the formulas together in one large text object to show the long combined string (since text objects don’t have length limits). Here is the main formula:

WhilePrintingRecords;
StringVar Item:= {your.stringfield};
StringVar Chain;
NumberVar ChCnt;
NumberVar SetCnt;
StringVar Set1;
StringVar Set2;
StringVar Set3;
StringVar Set4;
StringVar Set5;

if ChCnt = 1
then (ChCnt:= 2; chain := Item)
else
if Length(Chain) + Length(Item) > 200
then (SetCnt := SetCnt + 1;
if SetCnt =1 then Set1 := Chain;
if SetCnt =2 then Set2 := Chain;
if SetCnt =3 then Set3 := Chain;
if SetCnt =4 then Set4 := Chain;
if SetCnt =5 then Set5 := Chain;
NumberVar ChCnt := 2;
StringVar chain := ', ' & Item)
else chain := chain + ', ' + Item;
Setcnt & ' ' & Item

You can add more ‘SetX’ variables and their corresponding IF lines depending on how long your maximum string will be. You can also change the 200 to a smaller value if your individual values might go beyond 56 characters, or a larger value if you are sure your individual values will be shorter than 56 characters. In addition to this formula you would need a separate formula to display each ‘SetX’ value, like this:

WhilePrintingRecords;
StringVar Set1;

And you will need one formula to display the leftover strings in the chain variable:

WhilePrintingRecords;
StringVar chain;

Drag all of these formulas and drop them into an empty text object. Make sure it is formatted to “Can Grow” and it should show you all of your values. If you are using a recent version of Crystal the original formula should display 100K characters before it complains.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)






3 Responses to 'Apending Strings in v8.5 into one long string over 256 Chars'

  1. Dan_Burleson - March 4th, 2008 at 1:15 am

    Ken,

    Thanks very much for all that you do in putting this Crystal information out there.

    In this blog entry the code from Shelly generally works, but includes dead code and dead variables. Note that the variable ChCnt is only set to the value of 2 (other than 0 initially), but is tested for a value of 1.

    Below I have eliminated the dead code and variable plus I added a simple test that eliminates the comma from the beginning of the resulting composite string.

    WhilePrintingRecords;
    StringVar Item:= {your.stringfield};
    StringVar Chain;
    NumberVar ChCnt;
    NumberVar SetCnt;
    StringVar Set1;
    StringVar Set2;
    StringVar Set3;
    StringVar Set4;
    StringVar Set5;

    if Length(Chain) + Length(Item) > 200
    then (SetCnt := SetCnt + 1;
    if SetCnt =1 then Set1 := Chain;
    if SetCnt =2 then Set2 := Chain;
    if SetCnt =3 then Set3 := Chain;
    if SetCnt =4 then Set4 := Chain;
    if SetCnt =5 then Set5 := Chain;
    StringVar chain := ', ' & Item)
    else if chain = ""
    then chain := Item
    else chain := chain + ', ' + Item;
    Setcnt & ' ' & Chain

    Regards,
    Dan

  2. Ken Hamady - March 4th, 2008 at 7:25 am

    Dan,

    Thanks for the contribution. If you follow the link to my original formula you will see that the main formula is designed to work with a ‘reset’ formula, which is why there appears to be a dead variable. The ‘reset’ formula prevents the initial comma and also allows a separate accumulation for each group. This is described in the original formula page but probably not very clear here.

    So I tested my version of this formula with a grouped report and found that the ‘reset’ formula needs to be changed for this expanded formula. It should also reset the variable ‘SetCnt’ back to the number 1 at the beginning of each group.

    Your version does work better if there is no need to have a group reset.

  3. OWright - August 14th, 2008 at 2:20 pm

    Hello,

    I liked the idea but I think what I had come up with as far as dealing with the 8.5 limitation was better suited for my needs. Being that you folk had given me the idea I will post my version of the code. Please let me know what you think.

    Here is my script that I place at the report header:
    ————————————————–
    Shared StringVar Array strings;
    Shared NumberVar arrayMax := 1;
    Shared NumberVar maxLength := 250;
    Shared BooleanVar firstTime := true;

    Redim strings[arrayMax];

    strings[1] := “”;

    ‘done!’
    ————————————————–

    Here is my main loop that I used in the group header:
    ————————————————–
    WhilePrintingRecords;
    Shared StringVar Array strings;
    Shared NumberVar arrayMax;
    Shared NumberVar maxLength;
    Shared BooleanVar firstTime;

    Local StringVar enteredString;
    Local NumberVar lenStr;
    Local NumberVar lenArr;
    Local NumberVar lenLeft;

    enteredString := [Field Name];

    enteredString := IIF(firstTime,enteredString, “, ” & enteredString);

    firstTime := false;
    lenStr := Length(enteredString);
    lenArr := Length(strings[arrayMax]);
    lenLeft := maxLength – lenArr;

    if (lenLeft >= lenStr) then
    (
    strings[arrayMax] := strings[arrayMax] & enteredString;

    )
    else
    (
    strings[arrayMax] := strings[arrayMax] & left(enteredString, lenLeft);
    arrayMax := arrayMax + 1;
    redim preserve strings[arrayMax];
    strings[arrayMax] := “”;
    strings[arrayMax] := strings[arrayMax] & right(enteredString, lenStr – lenLeft);

    )
    ————————————————–

    and then to display the different sets in the text area. I would have one of these for every element I need displayed and change the display number variable.
    ————————————————–
    Shared StringVar Array strings;
    Shared NumberVar arrayMax;

    Local NumberVar displayNumber := [element in array];

    if(arrayMax >= displayNumber) then
    (
    strings[displayNumber];
    )
    else
    (
    ”;
    )
    ————————————————–
    I like this version better because the array is completely dynamic. It can be as big or as small as it needs to be. Please let me know what you think.


Leave a Reply

Jeff-Net
Recrystallize Pro

Crystal Reports Server