One line formula

[oshlonger]

I've posted this question earlier but I don't think I was clear and detailed enough, so I am trying again.

I am using the "Appending a List of Details into One Line" formula from this website (

http://www.kenhamady.com/form16.html)

My report is listing companies associated with a Product Id and I want the list to be only one row, with the company names separated by a comma. The report handles this but I am having problems when the @Display is larger than 254 characters. For some (not all) of the records that will go over the 254 character limit, the formula correctly stops at 254, but for the others it will return an error message when run (A string can be at most 254 characters long).

I don't understand why it works for some records that go over the limit, but not all. My list has about 15 records that will go over the 254 character limit. Another thing I have found, if I change the order of my companies (desc or asc) the records that pass change.

I'm stuck and hope someone can shed some light on this issue. Thanks!

 

[Naith]

If you're using the formula from that website, change '> 254' to '> 252', because in the last line, you're adding 2 more characters.

If, however, you want to stack several 254 limits on top of each other, so that you end up with what appears to be a long string of 6 or 700 characters instead of sacrificing everything over the 254 limit, then refer to thread767-343993 and amend the accumulator formulae to your needs.

Naith

 

[synapsevampire]

Post your formula.

The theory is fairly straight forward, break the string into more than 1 formula if it exceeds 254 characters. I haven't read Ken's FAQ, but it should have something like:

if len(string1)+len({table.company})+2 < 254 and secondstring = false then
string1 := string1+&quot;, &quot;+{table.company}
else
secondstring := true; // used
string2 := string2+&quot;, &quot;+{table.company}

Now reference string1 and string2 in different formulas and place them in a text object, they will appear as a single string.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[oshlonger]

Here is the formula:
1) In the Group Header place the @reset formula:

WhilePrintingRecords;
StringVar chain := '';
NumberVar ChCnt := 1


2) On the Details place the @Accum formula, putting your field into the second line:

WhilePrintingRecords;
StringVar Item:= {Your.Field}; // place your field in place of {Your.Field}
StringVar Chain;
NumberVar ChCnt;

if ChCnt = 1
then (ChCnt:= 2; chain := Item)
else
if Length(Chain) + Length(Item) > 252
then Chain := Chain else
chain := chain + ', ' + Item


3) On the Group Footer place the @Display formula:

WhilePrintingRecords;
StringVar Chain

I was able to fix my errors thanks to Naith's post but now I'm trying to print the rest...

 

[Naith]

If you want to show the values after you breakthrough the 254 limit, this is probably the simplest way I can think to do it;

1) In the Group Header place the @reset formula:

WhilePrintingRecords;
StringVar String1 := '';
StringVar String2 := '';
NumberVar Counter := 0;

2) On the Details place the @Accum formula. (I guess you can suppress this formula):

WhilePrintingRecords;
StringVar String1;
StringVar String2;
StringVar String1OK := 'Y';
NumberVar Counter := Counter + 1;

If Counter = 1
Then String1 := {Your.Field}
Else
If String1OK = 'Y'
Then If 
Length(String1) + Length({Your.Field}) +2 > 254
Then String1OK := 'N'
Else String1 := String1 + ', ' + {Your.Field};

If String1OK = 'N'
Then
If Length(String2) + Length({Your.Field}) +2 > 254
Then String2 := String2
Else String2 := String2 + ', ' + {Your.Field};

3) On the Group Footer place the @Display formula:
(This time, we're gonna have 2 display formulas.)

First place an empty text box into the Group Footer.

Drop this formula into the text box:

@Display1

WhilePrintingRecords;
StringVar String1;
StringVar String2;

If String2 = ''
Then String1
Else Mid(String1,1,Length(String1)-2)

and then place this formula to the right of @Display1, whilst still in the text box:

@Display2

WhilePrintingRecords;
StringVar String2;

If String2 <> ''
Then Mid(String2,1,Length(String2)-2)
Else String2;

This will give you a display length maximum of 508 characters. If you need to get more than that, create additional variables to pick up where String2 left off.

Naith

 

[oshlonger]

Thanks a lot for all your help. I tried to alter the oringinal formula myself, before I check back for your suggestions. Your formula is working great!

Are there any sites out there where I can learn more about crystal formulas? I have a good grasp on the basics but run into trouble the more involved the formula gets.

Thanks again!