Hello, We have a report that sho 1

[mmemon]

Hello,

We have a report that shows contact information for our sales force. Right now if a sales rep has more than 1 email, 2 records for that person are shown:

Donald Duck address phone
email1 fax

Donald Duck address phone
email2 fax

What I would like to change it to is this:

Donald Duck address phone
email1, email2 fax

So, the record doesn't repeat the address, phone and fax because someone has multiple emails.

However, I am not having much success. Any ideas on how I might go about doing this? Should I modify the query or it there a formula I can have in Crystal to accomplish this. Much appreciated. Thanks

Michele

 

[jdemmi]

If the address, phone, and fax are in different fields you should ba able to use the "suprres if duplicated" option.

 

[jdemmi]

If the address, phone, and fax are in different fields you should be able to use the "suprres if duplicated" option.

 

[mmemon]

HI jdemmi,

I can't use the suppress if duplicated option because there are many sales reps in 1 territory that share the same fax/phone. And the report needs to print out the data for each sales rep.

MIchele

 

[jdemmi]

1st let me apologize for the duplicate posts...IE had a fit when I hit submmit. (hey that rhymes)....anyway...can you provide some insight as to the data structure?

One of the DBs I report against is laid out similar to this.

EMPLOYEE Table
first name, last name, dept, location

EMPLOYEE PROFILE Table
address1, city1, state1, email1, address2, city2, state2, etc.

Because it is setup this way I can use the supress feature.

I think I can help is you can describe the data structure. (Tables, columns)

 

[Naith]

Hey Michele,

You might have some joy with the following:

Group on the sales rep. Suppress the group header and details section.

In the details section, place the following formula:

//@EmailAdder
WhilePrintingRecords;
StringVar email := ", " + email + {YourTable.Email};

In the group footer, place all your regular stuff, but instead of the email field, place this formula instead:

Mid({@EmailAdder},3)

In your suppressed group header, place:

WhilePrintingRecords;
StringVar email := '';

Good luck,

Naith

 

[mmemon]

Hi

The data structure is as follows:

Employee Table
PK,First_name, Last_name etc..

1,Donald,Duck

Employee Communication Table
PK,FK,ATT_TYPE,VALUE

1,1,FAX,212-555-1212
2,1,EMAIL,dduck@aol.com
3,1,EMAIL,dduck@yahoo.com
4,1,PHONE,212-555-9999

**This table can have multiple records for each att_type.

When the orginal query was written, it was okay to display 2 records for a sales rep but now they want it to change so 1 record displays and the email addresses are on 1 line.

Michele

 

[lyanch]

Group by ATT_TYPE, and use specified order (easiest way to get EMAIL first. Then only show the group header.. you will only get one of each type.

Lisa