Duplicate lines Report lines 2

[GEKLAW]

I am trying to write a client data report from three tables. The tables are client table address table and phone table. The three tables are linked by client ID, and I am grouping by client id. Each client can have multiple address and multiple phone numbers.

The address teble has a field called addresstype that will indicate if it is a home address or an office address by way of a string field. The phone table has a simillar field.

My current result looks like the following:

Client Home address Home phone
Home address Office phone
Office address Home phone
Office address Office phone

What do i need to do to only show one of each address and one of each phone number?

I am using crystal reports 9 and reporting off of a sql database.

 

[kskid]

Try this.

Create an alias of the address and phone table

All address and phone tables are to be left outer joined.

Modify your record selection

(isnull({address.clientid}) or {address.type) = "H") and (isnull({address_1.clientid}) or {address_1.type} = "O") and
(isnull({phone.clientid}) or {phone.type}= "H") and
(isnull({phone_1.clientid}) or {phone_1.type}="O")

Cheers,

-LW

 

[kskid]

Forgot one thing.

You will have to add another detail section to pick up the Office Address and phone (i.e. {Address_1.address} and {phone_1.phone})

-LW

 

[synapsevampire]

You'll likely get some row inflation/duplication dependent upon your joins and the number of addresses/phone numbers.

I think that you'll want to create some elaborate formulas to build each formula in advance and then display the in the client group footer if you intend to display in that fashion.

Keep in mind that many people have addresses longer than 2 lines, so you may want to rethink this...

-k

 

[GEKLAW]

Synapsevampire - The number of phone #s are usually 1-4 numbers per client. And the number of addresses are usually 1-3 per client. I would like to see any formulas that could help resolve the duplication issue.

wichitakid- thanks for your post but i dont' understand how adding a record selection to the report will display each field only once. I have tried creating table aliases and a second details section but everytime the address and phone number is in the details section i get duplicates.
Thanks for all your posts!

 

[synapsevampire]

I still don't understand how your display will work when you have 3 address lines per type of address, but I'll touch on the basics.

Group by client

Place this formula in the group header:

whileprintingrecords;
stringvar haddr1:="";
stringvar haddr2:="";
stringvar haddr3:="";
stringvar waddr1:="";
stringvar waddr2:="";
stringvar waddr3:="";
stringvar hphone1:="";
stringvar hphone2:="";
stringvar wphone1:="";
stringvar wphone2:="";

Now create formulas for the details section, such as haddr1:

if {table.addtype} = "h" then
(
if haddr1 = "" then
haddr1 = {table.address}
else
if haddr2 = "" and {table.address} <> haddr1 then
haddr2 = {table.address}
else
if haddr3 = "" and
{table.address} <> haddr1
and
{table.address} <> haddr2
then
haddr3 = {table.address}
)

This will handle the haddr1, or 1st home address field.

Now you can display these fields accordingly in the group footer.

You also might use arrays to do this, but this should handle it, and you'll need to build out the other formulas as well.

-k

 

[kskid]

I have a similar situation where I may have several phone numbers on an individual and I have to use the latest phone record based on type (R=Residence, B=Business) in my group selection

(
{phone.createdate} = maximum({phone.create_date},{phone.personid}) and
{phone.type} = "R"
) and
(
{phone_1.createdate} = maximum{phone_1.create_date},phone_1.personid}) and
{phone_1.type}="B"
)

 

[lbass]

Here's a situation where a subreport might make the most sense, since fewer records would be returned. Insert a group on {table.client}, if you haven't already, and place the client name and address field in the details section. Insert a subreport that uses the phone table, which has the phone number in the detail section. Suppress all subreport sections but the details section.

Link the subreport to the main report on {table.client} and place it in the group header. Then in the section expert of the main report, format the group header to "underlay following sections". This way you can align the subreport results with the details section of the main report.

-LB

 

[synapsevampire]

How could a subreport end up with the type of display intended? Even with the underlay, you won't be able to align bus phone with bus address and home phone with home address.

I guess the output requirements weren't...

-k

 

[GEKLAW]

Yes the output requirements weren't so strict as to require the home phone and home address to be right next to each other. If they were i would have had to do a lot of formulas. Thanks for all of the input!

 

[synapsevampire]

Stating that you want a specific output will generally make people try to do so which takes much more time, if you don't care about the output, then so state.

Glad that it's working

-k