Linking differnet Datatables

[1994mkiv]

I recently start using crystal report 8.5 at work. Today I was working on a report and it requires data out of 2 different tables. Here is how the tables look like

Table 1:
Customer# Order Packed
001 True
002 True
003 False
004 False


Table 2:
Customer# Order Shipped
001 True
003 false
004 false

If I print each report individually they are fine. If I combine the tables and print the whole report it gives me an error. I understand that customer 2 is missing in table 2 but I want the report to say "N/A" if order shipped is not found. I think I can solve the problem by modifying the database but I do not have the authority. Here is the exact error I am getting.

"ODBC Error [Data Direct-Technologies] [ODBC Progress drive] The keyword outer join were not found in an outer join escape sequence.

 

[Madawc]

Do a left-outer link from table 1 to table 2. This should allow details where table 2 has no entry. Note that this will only work when there are no selection conditions for table 2.

Do a formula field, something like

if isnull({table2.value}) then "N/A"
else {table2.value}

Note that tests for nulls must always come first, otherwise the test stops when a null is found.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[1994mkiv]

Thanks for the help, now the error message is gone but the report is not accurate. The report is skipping some values of the order shipped even though they are presented in the table 2 (Not talking about customer 2, I know there is no info available). So is report is looking like this


Customer# Order Packed Order Shipped
001 True
002 True
003 False false
004 False

 

[Madawc]

To clarify, you want an entry when there is an entry in table 1, or table 2, or in both?

If so, that's tricky in Crystal, unless you can left-outer link both tables to something else. Otherwise I don't think it can be done without using an SQL stored procedure.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[1994mkiv]

Thanks for the Help, the problem was solved. Initially crystal report linked both tables with every common field in both tables. I deleted all the extra links and kept the customer # link (left outer join) and now it's working.