Concatenate with comma but don't display only commas

[elsenorjose]

Hi folks,

Using CR XI R2 on Windows 7 Professional. Database is on a SQL Server 2008 server.

I'm trying to create a series of formulas to display notes that are stored in multiple rows and concatenate them on one row.

In the Group Header I have @InitialiseNote

WhilePrintingRecords;
StringVar ConcatNote := "";

In the Details Section I have @UpdateNote

WhilePrintingRecords;
StringVar ConcatNote := If IsNull({SO_Note.COMNT_30}) Then ""
Else ConcatNote + {SO_Note.COMNT_30}+",";;

Finally, in the Group Footer I have @ShowNote

WhilePrintingRecords;
If IsNUll({SO_Note.COMNT_30}) Then "" Else
StringVar ConcatNote;

What I'm trying to do is separate the values with a comma but if there are no values, I just get a lot of commas. How can I modify the formula(s) to display comma separated strings if there's more than one note but not show anything if there are no records?

Thank you

 

[Charliy]

You may have "Convert Null Values to Default" turned on in the Report Options.
Try testing for both nulls and spaces.

 

[elsenorjose]

Thanks. Checking for blank strings helped. One last question. I have a trailing comma whenever there is only one comment. How can I remove that?

 

[IdoMillet]

Assuming you end up with a sequence of commas, a simple solution is to use an expression such as:
Replace(your_variable or formula, ",,", "")

hth,
- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[elsenorjose]

Hi Ido,

Would I put that in my display formula?

 

[IdoMillet]

In whatever formula you want to use to show the final result.

hth,
- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[pmax9999]

And if you are left with a single comma at the end and you want to remove it, amend the {@ShowNote} formula to:

WhilePrintingRecords;
Left(ConcatNote, Len(ConcatNote)-1)

Cheers
Pete

 

[pmax9999]

And of course, that should have been:

WhilePrintingRecords;
StringVar ConcatNote;
Left(ConcatNote, Len(ConcatNote)-1)

Pete