Table linking issue
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.
Ryan Fry replied
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.