Loans disappear from report after fields from second table are added

[Former Member]

We are using Crystal XI. We have two tables from a sequel database we use for the reports, and we always have. Suddenly, we have been experiencing problems with loans being dropped from the reports and we have been trying to isolate the problem. To troubleshoot, I created a very simple report to provide the loan number and the borrower name from Table 1. I did not create any parameters so the report provided the information for all loans in the database. However, when I add a field to the report from Table 2, either all of the loans disappear or the number of loans on the report are drastically reduced.

As another test, I created a report to include just the loan number. The loan number field appears in both tables. I get different results if add the loan number to the report from Table 1 than I get if I add the loan number to the report from Table 2. When I create the report, the tables auto-link with no errors.

I have reviewed all the report options and general options in Crystal and can't find why this is occurring. I have had our IT staff troubleshoot and help me test using older versions of the tables, but we get the same results. Any suggestions will be greatly appreciated.

[Former Member · Accepted solution]

Where do I check for the table joins? I went to the linking and clicked on all the tabs, but do not see this info?

[Former Member · Accepted solution]

In CR XI, when you go to database expert and the click on the Links tab, you should see the tables in your report. Between the tables you should see a black line, click on it (once), it should turn blue, then right click on it and then on 'Link Options'. Check the join types.. Ignore 'Enforce Join' and 'Join Type' for now.

[Former Member · Accepted solution]

Before changing the linking to LEFT OUTER, I had tried to unlink the tables and then re-auto link. The message indicated unable to smart link unlinked tables. Foreign key relationships may not be present for the tables. What is this telling me?

[Former Member]

Thank you so much! I have always auto-linked and never had a problem. We recently added fields to both tables, but my IT department had confirmed the same number of loans existed in both tables, so I'm still not sure why I was experiencing the problem.

The linking was set to INNER join. I changed it to LEFT OUTER and all the loans appear on the report that should.

Thank you so much for your help.

[Former Member]

From what you have described, could it be your table joins? You mention that once you add the second table, records get dropped.

Check your table join, even though it auto links, right click on the link and see if the join is INNER or LEFT OUTER.

If INNER then every loan number in Table 1 should exist in Table 2, LEFT OUTER = every loan number in table 1 will be fetched irrespective of whether it has a corresponding match in table 2 or not.

Check your joins and change them and see if that makes a difference.