on 09-27-2013 4:38 PM
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
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'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.