Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

CONCATENATE RECORDS

Status
Not open for further replies.
markdt

markdt

Technical User
Feb 15, 2006
63
0
0
GB
Hi All,

I have a problem where i would would like to add together a list of records based on another statement. The table looks similar to this.

Salesnum | SText
________________________________________
1 | Invoice for
1 | £100 for work 1
1 | carried out
1 | blah blah blah
2 | some more text
2 | invoice more
2 | money.

Basically i have a table with salesorder number in the first column and text relating to that salesorder in multiple columns. My problem is that crystal reports displays it as multiple records. I would like to concatenate the text for each salesorder into one string?

How could i do this.

I have tried syntax such as

{tblText.SText} + " " + next{tblText.SText}

the next only gets the next record and not all which relate to salesorder 1. I have tried a series of loop statements of which i didnt get anywhere.

Please help!
 
Sort by date Sort by votes
Insert a group on salesnum and then create three formulas:

//{@reset} for the group header:
whileprintingrecords;
stringvar x;
if not inrepeatedgroupheader then
x := "";

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar x := x + {table.SText}+" ";

//{@display} to be placed in the group footer:
whileprintingrecords;
stringvar x;
trim(x);

Then suppress the group header and detail section.

-LB
 
Upvote 0 Downvote
Thanks for the quick reply LB.

It worked as you suggested thanks alot.

I have one more problem with trimming of a field. I have a name of sales person and i would like to trim their whole name to just display the initials, both the first and last name are in the same field though and i dont know where to use trim or instr to get the right results.

Please help!
 
Upvote 0 Downvote
You should start a new thread on a new question. Try the following:

If there are only the two names (no middle initials), you can use this:

stringvar array x := split({table.name}," ");
x[1][1]+x[2][1]

-LB
 
Upvote 0 Downvote
LB,

Thanks once again, worked perfectly.

Cheers
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

socalvelo
  • Locked
  • Question
Limiting the number of records in groups and sub-reports 1
Replies
2
Views
64
socalvelo
socalvelo
nerdygirlgear
  • Locked
  • Question
Limit sub report records to a time range on main report 1
Replies
1
Views
42
hilfy
hilfy
peaches9531
  • Locked
  • Question
Formula for multiple records selection criteria in Crystal
Replies
3
Views
93
lbass
lbass
codrutza
  • Locked
  • Question
Concatenate the Results
Replies
3
Views
25
lbass
lbass
sfullergs
  • Locked
  • Question
Select records by time frame
Replies
1
Views
48
lbass
lbass

Part and Inventory Search

Sponsor

Back
Top