Skip to Content
author's profile photo Former Member
Former Member

Combine Multiple Records in Crystal Reports

I have a table with our clients buying preferences, The first three columns is the client ID, Name and Last name and the fourth and fifth columns is the clients buying preference, I have multiple records per client depending on the preference the client has chosen.

ID First Name Surname Type Suburb

5 John Smith House Melrose

6 John Smith House Rosebank

7 John Smith House Kilarney

8 John Smith Cluster Melrose

9 John Smith Cluster Rosebank

10 John Smith Cluster Kilarney

I need to combine the fourth column into one string and the same for the fifth column.

ID First Name Surname Property Type Suburb

5 John Smith House, Cluster Melrose, Rosebank, Kilarney

I have been getting closer by using the formula in a similar post,

but still struggling a bit

Any help with this would be greatly appreciated.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Sep 04, 2009 at 12:18 PM

    Can you group by client ID?

    If so, you could then reset your variables at each group header, and then display the formula on each group footer.

    you can reset the variable by creating a new formula which says

     WhilePrintingrecords;
    global stringvar sub;
    sub:=""

    And then place this formula in the Client ID group Header. Then put your other formula field in the group footer. You can then suppress the group headers and details to show just those summary lines.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 03, 2009 at 12:45 AM

    Try this..use same formula for Type just change the field ..place it in Details section

    WhilePrintingrecords;
    global stringvar sub;
    IF ONFIRSTRECORD THEN
    sub:={Suburd_field_here}
    ELSE(
    IF NOT ({Suburd_field_here}  IN SPLIT(sub,',')) THEN
    sub:=sub&','&{Suburd_field_here}
    );
    sub

    And add a suppress condition for Details section

    NOT ONLASTRECORD

    HTH,

    Helps

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Thank you so much, I was a bit blind there. That works brilliant for one client,

      As I was saying yesterday, that was just one client of many in my report, What do I do in the case of multiple clients...

      ID First Name Surname Property Type Suburb

      5 John Smith House Melrose

      5 John Smith House Rosebank

      5 John Smith House Kilarney

      5 John Smith Townhouse Melrose

      5 John Smith Townhouse Rosebank

      5 John Smith Townhouse Kilarney

      6 Jack Black Cluster Sandton

      6 Jack Black Cluster Houghton

      6 Jack Black Cluster Fourways

      6 Jack Black Appartment Sandton

      6 Jack Black Appartment Houghton

      6 Jack Black Appartment Fourways

      obviously if I use that formula, with many records the following happens, before I suppress...

      ID First Name Surname Property Type Suburb

      5 John Smith House Melrose

      5 John Smith House Melrose, Rosebank

      5 John Smith House Melrose, Rosebank, Kilarney

      5 John Smith House, Townhouse Melrose, Rosebank, Kilarney

      5 John Smith House, Townhouse Melrose, Rosebank, Kilarney

      5 John Smith House, Townhouse Melrose, Rosebank, Kilarney

      6 Jack Black House, Townhouse, Cluster Melrose, Rosebank, Kilarney, Sandton

      6 Jack Black House, Townhouse, Cluster Melrose, Rosebank, Kilarney, Sandton, Houghton

      6 Jack Black House, Townhouse, Cluster Melrose, Rosebank, Kilarney, Sandton, Houghton, Fourways

      6 Jack Black House, Townhouse, Cluster, Appartment Melrose, Rosebank, Kilarney, Sandton

      6 Jack Black House, Townhouse, Cluster, Appartment Melrose, Rosebank, Kilarney, Sandton, Houghton

      6 Jack Black House, Townhouse, Cluster, Appartment Melrose, Rosebank, Kilarney, Sandton, Houghton, Fourways

      obviously if I have to suppress, NOT ONLASTRECORD

      I have the following result...

      ID First Name Surname Property Type Suburb

      6 Jack Black House, Townhouse, Cluster, Appartment Melrose, Rosebank, Kilarney, Sandton, Houghton, Fourways

      Where as the result I desire is

      ID First Name Surname Property Type Suburb

      5 John Smith House, Townhouse Melrose, Rosebank, Kilarney

      6 Jack Black Cluster, Appartment Sandton, Houghton, Fourways

      Is there anything I can do to get this result?

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.