I have a report that I am building to show all projects we have worked over the past few years.  My company wants to show the Superintendent's name for the project (if it's available).  I had to retrieve the values for this from a field that also contains the names of other people who worked on the project, so it's not like it is just a field called Superintendent. 

 

FYI - I created a Formula Field that combines the first and last name into a field I've labeled as Superintendent. 

 

There is another field called Role Code that works in tandem with the Contact field above.  This field is equal to SUPT when it's a Superintendent. 

 

My Problem:  

When i run the report, it repeats the job name the same number of times as there are people who worked on the project. 

 

Job #         Name                     Superintendent           Role Code

1                Jones Building        Sam Adams                 SUPT

1                Jones Building        Henry Franktown        PM

1                Jones Building        Betsy Covington         OWNER

2                Invesco Stadium    Pat Bowlen                 OWNER

2                Invesco Stadium    Mike Shannahan         SUPT

3                Coors Brewery       Nathan Smith              OWNER

4                Coors Brewery       Adolph Coors              PM

Here is what I want to do:

 

I was successful in suppressing the records unless this field was equal to SUPT, but that did not deliver the results I needed.   I ended up with 43 records that met this criteria versus the 900+ records that should appear. 

 

All I want is to show the Superintendent's name if it's available while only listing the project one time.  If the Superintendent's name was not available, I would like it to say Not Available as shown above. 

 

Please help as I am stumped :)  Please be gentle as I am pretty new to Crystal Reports 11

 

Thanks,

Jerad

 

If it's the latter case, you can do the following:

 

1.  Make the link between the project table and the people table be a left outer join FROM project TO people.

 

2.  In you Select Expert, you'll need to enter a formula that looks something like this:

(IsNull({people.Role_Code}) or ({people.Role_Code} = "SUPT"))

 

This should get you the projects that don't have superintendents along with the ones that do.

 

For your report, create a formula for the superintendent's name that looks something like this:

 

if IsNull({people.Role_Code}) then

  "Not Available"

else

  <however you're putting together the name.>

 

-Dell