How to suppress duplicate records

[n2nuk]

I am producing a Contact activity report that lists all clients and the types of contacts they have had with our advisors. Each client can have more than one contact.

I need to produce a listing that highlights a particular type of contact but at the same time displays all clients.

What I am getting is duplicated rows because a client has had more than one contact, what I would like to do is display all the clients but only only once even if they have had more than one contact or no contacts.

 

[cjlarue]

try creating a client group and then moving the client information into the group. You could supress the detail section or use it to list information from each specific contact.

 

[lbass]

Use a left outer join FROM the client table to the contact table.

It is unclear what you want to do with the specific contact you mention. You could write a formula like:

if {table.contacttype} = "X" then {table.contact}

You would then insert a group on the client and insert a maximum on the above formula and drag the result into the client group header.

-LB

 

[lbass]

PS. And then suppress the detail section.

-LB

 

[n2nuk]

Hi there,

Many thanks for all the replies, I have followed your advice and know have a listing of client contacts and only the max contact type is displayed which is great, however I also need to return an additional column where the contact type = 'O140'.

The max formula I have used reads:
Maximum ({@condate},{CLIENT.Client No}) I have changed the formula slightly to include the O140 contact type:

Maximum ({@condate},{CLIENT.Client No}) or {CLCONT.Type} = "0140" but I get an error highlighting the first part of the formula with 'A Boolean is required here'

The '@condate' is a concatenation of contact date and time.

 

[lbass]

It is still unclear what you want displayed. I thought what you wanted was to show all clients, and if they had a specific contact type, then show the contact, otherwise show nothing. So what I meant for you to do was to group on client no, and then create a formula:

if {CLCONT.Type} = "0140" then {CLCONT.Contact}

Then insert a maximum on this at the group level. If this isn't what you meant, you should show a sample of the results you would like to see.

-LB

 

[n2nuk]

Hi there,

Thanks for getting back to me ans apologies for not being clear as to what it is I am trying to achieve.

My intended report should look something like this:

CLId CLName Contact Type ContDate @S140
40021 Joe A one 2 one contact 03/07/06 No
40054 Joe B telephone contact 23/05/06 No
49032 Joe C interview 18/04/06 Yes
49032 Joe C S140 Assessment 25/02/06 Yes

All of the 3 contacts listed above are likely to have more than one contact, in the case of client 49032, this clients latest contact is displayed along with the S140 assessment contact. To make it easier to identify clients that have had the S140 I have created a formula called 'S140' this reads:
if {CLCONT.Type} <> "O140" then
"NO" else "Yes"

Although it would be handy to display the S140 contact details, it is not important, As long as the @S140 formula can correctly say Yes or No if the client has had a S140 completed.

I have used your maximum formula idea to return only the latest contact based on contact date. I have then added my columns to the group details (incl the @S140 formula) and suppressed the details section. This is where I am currently at.

I hope this gives you a clearer idea of what I am after.

Thanks in anticipation.

Regards

Naz

 

[lbass]

Please understand that I never suggested you use a formula to return the most recent record. All you really need to do is sort your records by date ascending and drag your detail fields into the group footer. This will return only one record (the most recent) per person. Then take your formula {@S140), place it in the detail section, and insert a maximum on it so that the maximum (which will be "Yes", since "Y" is > "N") will appear in the group footer. The reason you need to insert the maximum on your formula is because you are testing for the "Yes" record anywhere in the client group. If you just place your formula in the group footer, it might or might not pick up the "Yes", depending upon the last detail in the group.

-LB

 

[n2nuk]

Thanks foe prompt reply,

Sorry, I got the wrong end of the stick! I have removed the max formula I had set on the contact date and applied the settings you have described. I have amended the S140 formula to read:
if maximum({CLCONT.Type}) <> "O140" then
"No" else "Yes" however this is not returning any Yes's. Any ideas where I am going wrong?

Many thanks

Naz

 

[rmttbone]

Hello,

I also deal with a similar situation as you describe, and I like to use a suppress formula in the section expert.

I am assuming the you are using Crystal 10?


>Open the Field Explorer.
>Right-click Running Total Fields, select new
>Highlight your Client ID# or Primary Key field
>Click the > this is left of "Field to Summarize"
>Select "Count" from the "Type of Summary" drop down
>Under the Reset section, select "On change of field" and click the > that is left of it.
>Name the Running Total and close
>Open the Section Expert
>Highlight "Detail" and click the X+2 buttong that is on the right of "Surpress"
>Double-click the Running total you created from the tree view that has the fields.
>{#Running Total} appears in the text field, type >1 after the field and close.
>You will have to use the Sort expert to sort the Client ID#

 

[lbass]

No, you are not following my instructions. Place the following formula (slightly revised to account for nulls) in your detail section and then insert a maximum on it:

if isnull({CLCONT.Type}) or
{CLCONT.Type} <> "O140" then
"No" else "Yes"

-LB