joining tables in cr10

[neilmcdonald]

Hi,

I'm having some trouble joining tables in crystal reports 10. It's a SQL database using an ODBC connection.

There are 3 tables in the report:

Contract
Purchase Order
Purchase Invoice.

The report needs to be grouped by contract, then by purchase order, and then show the purchase invoices related to the purchase order.

The problem is this - some purchase orders have no invoices, but I still need them to appear. Secondly, some of the invoices are not linked to a purchase order, but again, these need to appear.

At present, I'm linking the purchase order table to the purchase invoice table, and the purchase invoice table to the contract. This gives me all of the purchase invoices I need, but misses out the purchase orders without invoices.

I've tried various join types, and I'm sure I'm missing something obvious.

I'd be grateful for any help you can give me.

Thanks,

Neil

 

[kskid]

Go to Database -> visual Linking and change your link to left outer join

Since you did not give enough specifics on link fields, I can only guess but can you do the following

Contract -> equal -> Purchase Order
-> Left Outer Join -> Purchase Invoice

This will give you a list of all the contract with a purchase order regardless of whether there is an invoice or not

Cheers,

-LW

 

[lbass]

I would try a left outer join FROM contract to purchase order, and a left outer join FROM purchase order to purchase invoice.

You say there can be invoices without purchase orders as well. These must have some kind of contract number on them, so I would suggest adding the purchase invoice table a second time (PurchaseInvoice_1) and do a left join FROM the contract table to this table. Place fields from PurchaseInvoice in a detail_a section, and the same fields from PurchaseInvoice_1 in a detail_b section. Then go to the section expert->detail_a->suppress->x+2 and enter:

isnull({PurchaseOrder.OrderNo})

Then go to detail_b->suppress->x+2 and enter:

not isnull({PurchaseOrder.OrderNo})

Not quite sure this will work, but it might be worth a try.

-LB