Data Connectivity: Linking Tables

Print Page | Close Window

Linking Tables

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=13426
Printed Date: 04 May 2024 at 7:19pm

Topic: Linking Tables

Posted By: stevetothink
Subject: Linking Tables
Date Posted: 07 Jun 2011 at 6:09am

I'm a newbie at crystal but I understand that tables need to be linked using a common field.

I started with a standard report that had 2 tables. I added a 3rd table that has an additional field I need.

I linked the 2nd table to the 3rd table using a field called "num."

When I run the new report, it comes up blank.

Any ideas what I'm doing wrong?

Thanks in advance.
Steve

Replies:

Posted By: Dewald
Date Posted: 07 Jun 2011 at 6:44am

There needs to be a logical relationship between fields that you link the tables on. So if you have a customer table with a customer number you can link it to a sales table on the customer number. Both of these tables, customer and sales, might have an internal ID field, but linking on the ID fields is meaningless.

Hope this helps.

-------------
Dewald Botha
http://www.ITClarity.co.za

Posted By: CircleD
Date Posted: 07 Jun 2011 at 12:22pm

There are so many ways to link tables that sometimes you have to play around with the links.Sometimes just changing the link to a left or right outer link will fix things and sometimes you have to add another table that you don't need any information from in order to add a table you do.I'd try a left outer link and see if that doesn't cure it.You may also be running into a null value and that will kill a report especially if you're using a formula.

Posted By: DBlank
Date Posted: 08 Jun 2011 at 4:20am

also keep in mind that crystal joins are not "automatically enforced" when they are added to a report as they are in something like SQL. Just making the join in SQL will alter your returned data set based on the join.

In Crystal you either enforce the join in the Link Options in the DB Expert links tab or the join becomes enforced when when you use any 1 field from both tables that are joined. They can be used in any fashion in the report (referenced in formula or the select expert or just a field in the report canvas).

This explains really odd behavior when you have a report that is showing all your records, you add one field to the canvas or a formula (the first use of any field in a table) and all of the sudden your data rows disappear or go to a huge number (enforcing a cartesion product of a join).

Posted By: stevetothink
Date Posted: 08 Jun 2011 at 9:51am

Thanks for the tips. I played with the links and got it to return what I think is the correct date field I'm looking for.

The problem now is that the quantity fields associated with each record are being doubled.

Any idea why adding a date field would double the quantity fields the report was already displaying?

Thanks again!

Posted By: CircleD
Date Posted: 08 Jun 2011 at 11:27am

If they are holding true to being double each time a quick easy fix would be to create a formula for that field and divide by 2.Something like {Table.Field}={Table.Field}/2 should do it.

Posted By: DBlank
Date Posted: 08 Jun 2011 at 11:44am

also ...

it is likley a a cartesion product from the joined tables.

You can try FILE >REPORT OPTIONS make Select Distinct Records as TRUE (not available for all data source types)

You can conditionally suppress rows using a NEXT(field_A)=field_A

You can use Running Totals to evaluate on change of a PrimaryKey field

Posted By: Dewald
Date Posted: 08 Jun 2011 at 8:37pm

Just be aware that if you do set the Select Distinct Records = True, any Valid Duplicates will allso be elimenated.

I would have a look at the join and see if there is another link between the tables that will make the results valid.

For example sometimes you would need to join on both Document Number and Document Type to make the join valid.

-------------
Dewald Botha
http://www.ITClarity.co.za

Posted By: stevetothink
Date Posted: 09 Jun 2011 at 9:22am

Thanks again for the help. I really appreciate it.

I changed the report so it only selects distinct records. That fixed the 2X problem I had on the quantities. However, the report now only includes terminated employees. It used to include both terminated and active employees.

Any ideas?

Posted By: DBlank
Date Posted: 09 Jun 2011 at 9:25am

what is your select statement?

Posted By: stevetothink
Date Posted: 09 Jun 2011 at 9:39am

Ha - I don't even know what that means.

Allow me to correct my prior post.

Changing the setting to include unique records didn't cause this problem. The problem is caused when I linked a new table and inserted a field that only applies to terminated employees.

The result is that the report now only runs for terminated employees. I want the report to run for all employees but only show the inserted field when displaying a terminated employee.

Posted By: DBlank
Date Posted: 09 Jun 2011 at 9:42am

assuming you have no select statement then it is likely to do with a join. sounds oike you store termination data in a separate table from the employee table

is that correct?

Posted By: stevetothink
Date Posted: 09 Jun 2011 at 9:47am

The original report automatically picked up active & terminated employees and included the following 2 tables:
1. ROTPersonnelStatusTableB
2. ROTPersonnelStatusTable

I added a link to a table called "Closing_Statement"

Then I wrote a custom formula as follows:

If isnull ({Closing_Statement.Vest Cancel Date}) then {ROTPersonnelStatusTableB.Expiration Date}
else {Closing_Statement.Vest Cancel Date}

If instead of using this formula I simply insert {ROTPersonnelStatusTableB.Expiration Date}, the report includes both active and terminated people. However, when I insert the custom formula above, it only pulls terminated people.

I'd like to use my custom formula above and have it show both active & terminated people.

Thanks again for the help.

Posted By: DBlank
Date Posted: 09 Jun 2011 at 9:51am

it has to do with the join to the closing statement table

I assume only staff that are termed have a matching row in that table

if you made your table join an inner join it only returns rows where there is a match in BOTH tables

you need to alter it to an outer join.

Posted By: stevetothink
Date Posted: 09 Jun 2011 at 10:03am

thanks - how do I change it to an outer join?

Posted By: DBlank
Date Posted: 09 Jun 2011 at 10:10am

Database Menu

Database Expert

Links Tab

Double click on the join (line between the 2 tables)

Link options window opens

Change it to LEft outer Join in the Join TYpe

Posted By: stevetothink
Date Posted: 09 Jun 2011 at 10:12am

Thanks - left outer join didn't fix the problem.

Full outer join seems to have worked.

What do these settings mean?

Posted By: DBlank
Date Posted: 09 Jun 2011 at 10:14am

try right outer join

Posted By: stevetothink
Date Posted: 09 Jun 2011 at 10:26am

Right outer join and full outer join work for some people but not others.

For example, all terminated people have a vest cancel date. The report now displays both active & terminated people but it does not show the vest cancel date for all terminated people.

Steve

Posted By: DBlank
Date Posted: 09 Jun 2011 at 10:30am

It is difficult to assist as I am completely guessing at your table structure and joins...

an outer join (left or right) includes all row from one table (left or right) and matching rows from the other.

a FULL OUTER JOIN includes all rows from both tables.

since you only altered one join the other joined table still is impacting your returned rows.

What are the 3 tables

How are they join and what data (purpose) is each table

Posted By: stevetothink
Date Posted: 14 Jun 2011 at 10:11am

Thanks for the help on this. I got it to work by linking to a different table and creating more links (left outer).

I appreciate the help. This was big for me.

Steve

Print Page | Close Window