Ask Search:
Jody PupeckiJody Pupecki 

How do I hide duplicate records in a report?

We are using the non-profit CRM and created a custom field in the household view for newsletter since we only want one newsletter going to the house even though there might be multiple contacts listed in the household.

When I create a custom report to pull the right information from the household for mailing labels we found that when running the report, if there were 3 contacts listed in the household then the report listed the household 3 times.  How do I hide the duplicates so when I run the report I am only seeing the household listed once?
Best Answer chosen by Moderator (salesforce.com) 
Judi SohnJudi Sohn
 Hi Jody, I believe you're using the wrong report type for your report.

You are starting with a report that begins with Contacts and including Households, right? That's your problem. The report is looking at each individual and giving you that data. So if you have 4 people in a Household you're getting each person in the Household on each row of the report. 

Instead, create a new custom report and select "Other Reports" from the box on the far left, then "Households" in the middle box.

Now you'll get a report which each Household in its own line. Mind you, this report won't have any contact-specific data unless you've added fields that pull data from household contacts. But for a postal mailing list, this is what you want. 

All Answers

Steve MolisSteve Molis
Let me qualify this by saying up front that I'm an admin on an EE Org, and you might want to wait until an NP Admin has a chance to chime in, but I just don;t want to see you twisting in the wind on this.  

If the field that you're slecting on is on the Household(account) object you might be able to just run an Account report rather than a Contacts and Accounts report.  
Meryl FlynnMeryl Flynn
You will need to work on cleaning up your dupes inside of Salesforce. You can use the data loader to do a mass cleanse of the data in your environment: http://wiki.developerforce.com/index.php/Apex_Data_Loader

I can also suggest using a de-duping tool moving forward suc as Dupeblocker: http://appexchange.salesforce.com/listingDetail?listingId=a0N300000016cMzEAI

To prevent those nasty dupes from getting in there in the first place.
Jody PupeckiJody Pupecki
Miss Meryl...they are not duplicates, it's 3 different contacts listed in one household so I don't need to cleanse any data unless I am misunderstanding you...

For example for the Smith Household we'd have Sally Smith, Mark Smith and Joan Smith all listed as household members, that is why when I run the report the household is showing up 3 times, one for each different household member.
Staci RiceStaci Rice
If you're exporting this list to an excel file for mail merge or something, you could always just "remove duplicates" which will leave you with only 1 record for each Household/Org.

It also sounds like it might make sense to have the custom fields you're referring to exist on the "Account" level so that you don't have to run a "Contact" report.  Is there a specific reason these fields were added on the contact level?
Jody PupeckiJody Pupecki
Staci, I added it to the household level b/c we might have multiple contacts residing in the same house (mother, father, sibling) but only want one newsletter to go to the entire household.  Adding at the household level allowed me to specify only once whether the household got a newsletter, rather than having to specify it for each contact.

As for removing duplicates in excel, I am trying to make this as easy as possible for our mailing coordinator who is not so computer saavy, so the more I can do on my end to get the report right before she runs the report and exports it the better :)
Judi SohnJudi Sohn
 Hi Jody, I believe you're using the wrong report type for your report.

You are starting with a report that begins with Contacts and including Households, right? That's your problem. The report is looking at each individual and giving you that data. So if you have 4 people in a Household you're getting each person in the Household on each row of the report. 

Instead, create a new custom report and select "Other Reports" from the box on the far left, then "Households" in the middle box.

Now you'll get a report which each Household in its own line. Mind you, this report won't have any contact-specific data unless you've added fields that pull data from household contacts. But for a postal mailing list, this is what you want. 
This was selected as the best answer
Jody PupeckiJody Pupecki
Judi, that was it!  Thank you so much!
Heather ParkerHeather Parker
This fixed my delimma too glad this was posted here Judi!!
Usha CharlesUsha Charles
Hi Judi & all,

I have a similar issue, I want a report showing leads & the campaigns to which it is attached/registered.
I believe, leads with campaigns report would suit my requirement because I dont want a lead appearing more than once in my report. I tried creating a custom report type - 'Leads with Campaigns' & I didnt find  "Campains" in the list but just "Campaign History". Is there any workaround ? Could you please help ?
Rachel Calvarese-DonovanRachel Calvarese-Donovan
With the Nonprofit Starter Pack updates, you can no longer pull a mailing list by Household. When I run a report through Contacts, I end up with duplicate contacts from each Household (as Jody experienced). When I run the report through Organization, all miss all my individual conatcts. How do I elimate the duplicates in a report but still capture all the data I need (including Informal and Formal Greetings, which are only linked to Contacts and (former) Households. 
Sue LipseySue Lipsey
I have been working on this one a while and it seems it should be so easy..
For a postal mailing list you create an account report using billing address. This will pull the address from the contacts if you haven't entered an address for the account. Formal and Informal greetings are related to accounts, so you can add those. The problem I run into is you cannot get an email for the account. Emails are only linked to contacts. There is a field in accounts called "Primary Contact" that seems to work, but no field for Primary Contact Email. This would be helpful to see which accounts have related addresses and/or emails.
Also, there is email opt out and phone opt out, but I cannot find a postal mail opt-out field..