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.