Skip to Content
author's profile photo Former Member
Former Member

Crystal Reports 11 report build question

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Sep 27, 2013 at 04:59 PM

    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'

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.