CODEWITHSUNDEEP
crystal-reports
Nuru Salihu
Nuru Salihu

Reputation: 4948

Join rows in to a string in crystal report formulae

I have a rows of phone numbers like below

Number

11111111111

22222222222

3333333333

4444444444

I want to concatenate these in to a single string like blwo

11111111111,22222222222,

3333333333,4444444444

When i use the formulae in Crystal Report 

stringvar text :='';

if(InStr(text,{CONTACTNUMBERVIEW.CONTACTNO}) = 0 )
then 
  text := text + {CONTACTNUMBERVIEW.CONTACTNO} + ",";

if OnLastRecord
then
   text := Left(text, len(text) - 1)
else
   '';



text;

It did worked, however, its printing a repative values like below

11111111111,

11111111111,22222222222,

11111111111,22222222222,3333333333 ,

11111111111,22222222222,3333333333,4444444444

I thought of another way by adding this line 

if OnLastRecord
then
   text := Left(text, len(text) - 1)
else
   '';

Then it returns three empty rows with the fourth correct. Please how do i return just

11111111111,22222222222,3333333333,4444444444

i though on adding

WhilePrintingRecords; on top of my formular i.e

WhilePrintingRecords;
stringvar text :='';

if(InStr(text,{CONTACTNUMBERVIEW.CONTACTNO}) = 0 )
then 
  text := text + {CONTACTNUMBERVIEW.CONTACTNO} + ",";

if OnLastRecord
then
   text := Left(text, len(text) - 1)
else
   '';



text;

Yet nothing change, Any help is appreciated.

Upvotes: 0

Views: 2893

Answers (1)

Ryan
Ryan

Reputation: 7287

Your formula is on the right track; it's how you're placing it into your report that is the biggest problem. The Details section of the report will appear once for each record returned from the database which is why it's showing you the string as it is built incrementally with each new record.

To fix that, you need to suppress the Details section of the report which will prevent it from displaying but not from actually evaluating the formula. Then, create a new formula that you can place in the Report Footer section that will simply display the string you've built. See the two formulas below:

//Incremental formula to build the 'text' variable in Details Section
//Note that this formula or the section it's placed into should be suppressed
whileprintingrecords;
stringvar text;

if(InStr(text,{CONTACTNUMBERVIEW.CONTACTNO}) = 0 )
 then text := text + {CONTACTNUMBERVIEW.CONTACTNO} + ",";


//Formula to display the 'text' variable in Report Footer
whileprintingrecords;
stringvar text;
left(text, len(text) - 1) //remove trailing comma

Upvotes: 1

Related Questions