Print Page | Close Window

Finding the first applicable field

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19712
Printed Date: 01 May 2024 at 10:44am


Topic: Finding the first applicable field
Posted By: VickiM
Subject: Finding the first applicable field
Date Posted: 26 Jun 2013 at 9:54am
Hi.
 
I am working on a report where I need to pull an emergency contact number for employees.
 
In our database, someone may have listed up to 9 people has their emergency contact and they have a seqence number assoicated with them. 1,2, 3, etc.
 
The problem I'm having is:
Some people have never entered a contact but I still need them to show up on my list.  So I need blank fields.
Some people have entered several contacts, then removed some so they may have a contact number in sequence 1, 2, 5 or they may skip all the way to 9.
 
How do I pull in people who have never entered a contact and, pull in the other people's first contact record regardless of what the sequence number is on that field?
 
In other words, if someone doesn't have anything filled in for a contact period, I want them, if someone has 1 and 5 filled in, I want 1, if someone only has 9 filled in I want 9.
 
Any help will be appreciated!
 
 
 
 


-------------
VickiM


Replies:
Posted By: lockwelle
Date Posted: 27 Jun 2013 at 4:47am
are you joining straight to the database tables or are you using a stored proc?

joining straight, I would use a outer join from the employee table to the contact table (this displays people without contacts).
Then I would order by the sequence number of the contact and display the information in the group header (this will display just one contact, and it will be either the first or the last contact (depending on if the order is ascending or descending)

HTH

Posted By: VickiM
Date Posted: 27 Jun 2013 at 4:52am
I'll try that.  Thank you!

-------------
VickiM


Print Page | Close Window