Hi All,

I am tasked with building a report which needs to include 2 addresses stored on a customer, but as they are stored in the same table I am struggling a bit.

Say I have Customer A who has a Business address and a Residential address (call them ADDR_TYPE_ID=1 and ADDR_TYPE_ID=2), but Customer B only has Business. I need to export both addresses in full for my report, or just the Business address is there is no Residential.

We have 3 tables - CUSTOMER, ADDR_LINK and ADDR. CUSTOMER links to ADDR_LINK via CUSTOMER_ID, and ADDR_LINK in turn links to ADDR using ADDR_ID. I have a formula which says: IF ADDR.ADDR_TYPE_ID=1 THEN ADDR.ADDR1 and another for ADDR_TYPE_ID=2. As the address pulled through can't equal both address types, this returns false on one of the 2 formulae.
My next attempt I created an alias for the ADDR_LINK and ADDR tables for Residential addresses, and used an outer link. This has got me closer, but on occasion the Residential is pulling blank when there should be an address, or sometimes even the Business address in its place.

Is there a way I can force the alias to only pull ADDR_TYPE_ID 2 without it restricting to accounts with both addresses? I have tried the selection criteria of: (ISNULL({ADDR2.ADDR_TYPE_ID}) OR {ADDR2.ADDR_TYPE_ID}=2) to no avail.

Thanks a bunch :)

P.S. I am using Crystal 10.


Edited by Ibzy - 08 Feb 2012 at 11:56pm