records are repeating for one field that returns multiple rows

[JSeidel]

I have one field that is creating multiple rows in the database for one account because it varies by time. I would like all values for this field to be placed in one field on the report (like a cancatenation of this field) so that I don't have multiple sections for one account because this field changes...

does anyone know how to do this?

 

[synapsevampire]

Sure, but which row do you want?

You can group by whichever fields are appropriate and the date and use a maximum or minimum (report->Edit Selection formula->group) to get just one row.

If you need further assistance, please post specifics about your environment:

Crystal Version
Database type and version
table layout(s)
example data
expected output

An alternative is to add in a (perhaps another) group to the Crystal Report and place the fields in the group header (first date) or group footer (last date).

However you will stilol get all rows in the report, so any summary functions would have to be tweaked.

-k

 

[lbass]

If you don't need the time field, just remove it and your duplicates should be eliminated. If you want to show all time values in one line, then use the three-formula method for collecting data in one line:

First group on account number. Then create {@resettime} and place it in the group (acctno) header:

whileprintingrecords;
stringvar times := "";

Then create {@timedetail} and place in the details section:

whileprintingrecords;
stringvar times := times + totext({table.time}) +", ";

(If the time field is already a string, remove the "totext.&quot

Then create {@displaytime} and place in the group footer:

whileprintingrecords;
stringvar times;
left(times,len(times)-2);

Then place the other fields in the group footer along with {@displaytime} and suppress the group header and details sections.

-LB

 

[JSeidel]

I am using crystal 8.5 and this is what I have as output. Sql 2000 db.

ID#: Allergy:

Order No.
Likes/Dislikes:
Comments:
Modifiers:

I have it grouped by ID# because you can have many Order No. within an ID (this is OK), the problem I have is if Allergy is 1...n then it creates a new record. I need Allergy to be "Allergy 1, allergy 2, allergy 3, allergy 4" with the orders underneath....

 

[lbass]

If the allergies have to be in the group header, then create a subreport which group on ID and then use my formula above, but substitute "Allergies" for "times" and your field {table.allergy} for {table.time}. Place each formula in the recommended section in the subreport, and then suppress the subreport report header, group header, details and report footer, leaving the group footer with the display formula unsuppressed. Link the subreport to the main report on the ID field, and place the subreport in the group header.

-LB

 

[JSeidel]

will this still work without subreports? the framework I push this report up to will not accept subreports and thus I need to keep this to a onesie....

 

[lbass]

To get the allergies in the group header, I think you'd have to use a subreport, because you can't collect info from details before the details have been read by the report. There is a way to make the group footer to appear to be a group header, but it would only work if you had one row of data for your other fields, like OrderID, but you have indicated you could have multiple Order IDs. Could you do something like in the group header add a text box in red font: "Important: see allergies listed below"
and then show the allergies, again in red, in the group footer? Although if it needs to be on top to ensure it's noticed then it should stay there...

-LB