Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: selecting bottom record Post Reply Post New Topic
Author Message
Tiff71
Newbie
Newbie


Joined: 10 Jun 2008
Location: Australia
Online Status: Offline
Posts: 4
Quote Tiff71 Replybullet Topic: selecting bottom record
    Posted: 10 Jun 2008 at 9:35pm
Hi,
 
I am designing a report in Crystal XI that should pull all contacts that have a nominated field populated.
 
I need to have each of these contacts included in the report but also need to pull out the last call or appointment from the history table. So in other words, I need one record line only per contact that will show either call details, appointment details or no details for the last call or appointment logged.
 
How can I include details of the last call or appointment (where there is one) and still include all contacts where there is no call or appointment.
 
If I base a formula for the report to pull only calls or appointments, multiples come into the report and I lose contacts that have had no calls or reports which is not what I want.
 
Any help would be greatly appreciated.
 
Thanks
IP IP Logged
xbatty17
Newbie
Newbie


Joined: 26 May 2008
Online Status: Offline
Posts: 2
Quote xbatty17 Replybullet Posted: 11 Jun 2008 at 8:34am
Hey tiff, you need to include an right or left outer join in you're linking of the tables.  this will include all contacts with an appointment, call or nothing.  As for the multiple records of the same contacts, you need to have enough conditions to make sure you only get one record per contact
IP IP Logged
Tiff71
Newbie
Newbie


Joined: 10 Jun 2008
Location: Australia
Online Status: Offline
Posts: 4
Quote Tiff71 Replybullet Posted: 11 Jun 2008 at 3:26pm
Hi,
 
I'm having trouble with the actual formula to pull the last call or appointment history item from a list of many types of history items. For example, the last call/appt received by a contact may have been as far back as 2004.
 
Where exactly do I apply the left or right outer join? To the entire report?
 
..and do I add a rule into the 'select' options to bring only calls or appts? this is where the contacts without any are being cut out.
 
Sorry, I'm still confused.... thank you :)
IP IP Logged
Tiff71
Newbie
Newbie


Joined: 10 Jun 2008
Location: Australia
Online Status: Offline
Posts: 4
Quote Tiff71 Replybullet Posted: 11 Jun 2008 at 3:49pm

Hi again,

OK. I've added several additional conditions to cut down the unwanted logs. I've also changed the table links to left outer joins. I now have the people I want and only calls and appointments.

Only problem now  is that I have ALL calls and appointments for each person. I've sorted by date within each group so how do I now just keep the last line of each group?

Cheers.

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.032 seconds.