Report Design
 Crystal Reports Forum : Crystal Reports .NET 2003 : Report Design
Message Icon Topic: table joins Post Reply Post New Topic
Author Message
Yarra
Newbie
Newbie
Avatar

Joined: 13 Dec 2011
Location: United States
Online Status: Offline
Posts: 10
Quote Yarra Replybullet Topic: table joins
    Posted: 13 Apr 2012 at 1:52pm
How can I achieve the desired output in crystal.

Table 1
ID      Name
1     John
2     Mary

Table 2
ID      Phone
1     44444
1     55555
2     88888
2       99999


Current Output (when joined table 1 with table 2 on ID)
ID   Name    phonenumber
1    John       44444
1    John       55555
2    Mary       88888
2    Mary       99999


Desired Output
ID   Name Phonenumber
1    John    44444, 55555
2    Mary    88888, 99999
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 15 Apr 2012 at 2:44pm

Try this:

1.  Join from table 1 to table 2 on ID.
2.  Group on table1.ID
3.  Create a formula to concatenate the phone numbers.  It will look something like this:
StringVar Phones;
if OnFirstRecord or {table1.ID} <> previous({table1.ID}) then Phones := '';
If Phones = '' then
  Phones := {table2.phonenumber}
else
  Phones := ', ' + {table2.phonenumber};
Phones
4.  Place your fields in the Group Footer for the ID group, using this formula for the phone number column.
 
-Dell
 
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.