cancel
Showing results for 
Search instead for 
Did you mean: 

Crystal Reports 11 report build question

Former Member
0 Kudos

Hello Folks,

I am looking for a little help on the ability of Crystal Reports 11 to produce a report that I have a requirement for.

Try my best to explain.

The report will have to draw data from two tables within the database.

Table 1 contains the main applicant data, like name, applicant number, etc

Table 2 contains records related to each applicant that represents their uploaded documents.  (There will be a record in table 2 corresponding to each document that the applicant uploads).  So, there could be multiple records in table 2 that correspond to a single record and applicant number in table 1.

Basically,  I want to display the list of applicants using the data in table 1 and, as well, have a formula field that will should a "yes" or "no" if the applicant in table 1 has documents listed in table 2.

If have tried to do it just by simply references a single field in table 2 as a formula (If table2.desc <>"" the "yes" else "no").  No the correct syntax here I know.  Just for reference.

When I include the formula field in the report to display the table 1 data, I end up display repeated records for each applicant in table 1.  I only want to display 1 reference of the table 1 applicant.  I understand why it is doing this, because if an applicant has more than one record that exists in table 2, it produces another line in my report.  This is not the desired result.  I effectively want to indicate Yes or no whether an applicant ID that exists in table 1, has any records in table 2, then go on to the next applicant id in table 1.

BTW, there is a direct field join between the two tables "App_id"

Long winded explaination, but I hope that is clear.

Any help would be greatly appreciated.

Regards,

Terry Bennett

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member205840
Active Contributor
0 Kudos

Hi

Join two table using left outer join.  Table1 will be the first table and table 2 will be your second table.

Now pull the records into your report and insert a group on App_id and suppress detail line and place your fields in group footer.

Now create a formula at group level

If isnull(table2.des) then 'No' Else 'Yes'

Former Member
0 Kudos

Sastry,

I appreciate the quick response.  I attempted to do what you suggested and I am not getting the results I expected.  Firstly, it is only showing the "YES", and not the "no" records.  I will keep trying to see what I have done wrong.  Any other insight would be appreciated.

Regards,

Terry

former_member205840
Active Contributor
0 Kudos

Terry,

After making left outer join, please place fields from both tables into your crystal reports detail section and see whether you are getting any records where you have null values for table2.

I.e. Values from table one and no values from table2.  If you get this kind of results then your join is correct else there is some issue with the join.

-Sastry

Former Member
0 Kudos

Thank you Sastry for your help thus far.  I think I am getting closer, but still having a couple issues.  Would you be willing to get on a skype call and remote desktop session?

trbconsulting is my skype id.