How to concatinate different records

[PITOInfo]

In Access I can use a produce a recordset in a function using either a preexisting query or by wrinting the SQL and then go through this recordset and concatinate one or more fields from each record into one field. This might be used, for example, to go through all the products you have filtered by a particular colour and put this into a form letter or report.

IE: if you want a red car, then we have cars by Ford, MG and BMW which might fit the Bill for you. The "Ford, MG and BMW" would be concatinated from a query that pulls out all your red cars, and then the function will move through them one record at a time adding each make to a string variable along with the necessary commas and "ands".

My query is, Can I do anything like this in Crystal. I know you can pull out a field from the next record to the one you are working on with Next(fld), but can you go further and do this with several records.

James

 

[pgtek]

hi

change your group to color and pass a parameter to ask user for what color he wants
and then the report will list all cars the have this color

hope this helps

pgtek

 

[PITOInfo]

Thanks

But the problem isn't really pulling out all the cars of a particular colour, it is poulling them out as a continuous string. ie: "MBW, MG and Ford" (exactly like that.)

Believe me I have a good reason for doing this which is nothing to do with cars or colours, but which would bore the pants off you if I tried to explain.

James

 

[IdoMillet]

Create 3 formulas:

In Group Header (suppressed):
------------------------------
whileprintingrecords;
Global Stringvar ls_Cars :="";
------------------------------

In Detail section (suppressed)
------------------------------------
Global Stringvar ls_cars;
ls_cars := ls_cars + {car} +", "
------------------------------------

In the group footer (show):
------------------------------------
whileprintingrecords;
Global Stringvar ls_cars;
left(ls_cars,length(ls_cars-1))
------------------------------------

Note: in versions below CR 9.0
the string variable is limited to a length
of 254 characters.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[IdoMillet]

Correction for last formula:

In the group footer (show):
------------------------------------
whileprintingrecords;
Global Stringvar ls_cars;
left(ls_cars,length(ls_cars)-2)
------------------------------------

hth,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[PITOInfo]

That looks perfect. Now, to be cheeky, is there a way of doing this with memo fields way over 254 characters. We're version 8.5?

James