Table linking issue

[Former Member]

Working with Crystal Reports 11. Fairly new to this, but I am little confused.

Working with 3 tables.

Table 1 is a list of all the details of customers with a CUSTOMER_CODE field

Table 2 is all the invoices of customers also with a CUSTOMER_CODE and an INVOICE_NO field

Table 3 is all the details for the invoice table also linked with a CUSTOMER CODE and a INVOICENO field

Have links via CUSTOMER_CODE to all three tables and have the INVOICE_NO field linked between Table 2 and Table 3.

The problem that is occuring is there is a few CUSTOMER_CODES in TABLE 2 and TABLE 2 that are not available in TABLE 3. So when I try and run a report containing any additional information from TABLE 1 it will exclude the CUSTOMER_CODES that are not available to be linked.

Was hoping via an IF statement I could resolve this but no luck. CUSTOMER_CODE is a mix of alphanumeric so it is a string.

if ="99999" then "Cash Sale" else

Same problem all the 99999 accounts get eliminated from the report. CUSTOMER_CODE 99999 does not exist in Table 1, but can be found in Table 2 and Table 3.

[Former Member · Accepted solution]

Hi Andy,

Don't go too link crazy, or you'll get unexpected results.

Based on what you're describing, it sounds like Customers are your parent records, invoices child to customers, and invoice details child to invoices.

You should not have a link from customers to invoice details.

Link: Customers to Invoices and invoices to details. There should be no link between customers and details.

Then, what you need to do is click on the table links, right click, and select change join type. Depending on how you've linked the tables (directionally) you may need either a left outer, or a right outer join. You could potentially also use a full outer join, but that will give you all customers without invoices, as well as all invoices without matching customers. Try one, and if that doesn't work, try the other.

A left outer join takes the two tables, and says 'give me all of the records on the table on the left, regardless if there is a corresponding record in the table on the right, and all right table records that 'belong' to records on the left table'

Crystal determines which table is 'left' and which is 'right' based on which table you linked FROM. It takes the FROM table, and calls it the left.

Hope that helps.

-R

[Former Member]

Glad I could help.

The wole left/right thing and outer joins make a whole lot more sense after you play with them. There are some great articles on the web about it too... but lemme see if I can clarify.

let's assume I have two tables, Company and Employee.

If I am writing a report and I want all companies, and thier related employees but I also want to get details about companies who have no related employee records:

I would connect FROM company (left) TO Employee (right), I would then select the link, right click, and choose 'Left Outer Join'. then I'll get ALL records in the company table, AND any records in the employee table that are related to one of the companies in the first table.

A right outer join would do the same, but for the right hand table.

A full outer join gives me all records on both tables.

[Former Member]

Thanks Ryan,

Doing a sample table now. Simplifying the links, but still curious to understand the LEFT and RIGHT table relationship. I will play a bit and see what happens. I might have more questions soon.

Is there a way to change the LINK INDEX or how is that determined. BTW lots of playing got it working.

Edited by: Andy Duffy on Sep 5, 2008 7:07 PM