Custom Report Builder - Duplicate Results Due to Associations

[TWallen]

I'm using the Custom Report Builder to create Contact-based report with Companies and Deals as my secondary data sources. I need to reference the associated Companies and Deals for my filter criteria. 

The report itself is set up as table only exporting the contact's name and email address. There are no deal or company properties in the table.

I export the result and the contacts are duplicated each time they have more than one type of association. For example, the contact will show up 5 times if it has 5 deals associations. 

I thought unchecking "Show Record IDs" under Chart Settings would stop this from happening but they are still being duplicated without the Record IDs in the export. 

I figured I could try Lists but that does not include the Custom Filter Rules for the filter criteria. 

Is there something I am missing?

[Jnix284]

@TWallen I think this comes down to visualization and how you're filtering it - if you wanted the same data represented as a number, you'd likely want to know the total number of deals (5) even if they were all associated to the same contact.

Because you're trying to generate a table of all of the contacts with deals that meet the criteria, it's first looking at the deal and determining which deals qualify to be on the list and then surfacing all of the contacts that meet that criteria. There's nothing in how the report is built to tell it to look at other deals for the same contact to exclude duplicates, only "if deal = x, then display contact" - unfortunately, there isn't a way to do this in a report.

One option would be to create a custom coded action (requires ops hub pro) that creates a list of the contacts based on the same criteria via a workflow, but it would also be possible to check if the contact is already on the list so you wouldn't have duplicates. You could then use this list in a report, but since you're using it to create an export you could also just export that list directly.

[TWallen]

Thanks for your response @Jnix284. To clarify, my table does not include contacts with the deals. I'm only exporting Contact properties to my table (First Name, Last Name, Email). 

I added Deals as a data source because I want to include contact records that have an associated deal created in the last 2 years in my filter. 

I see "First deal created date" under Contact properties but nothing for latest deal created date. I assume I could use a workflow and custom property to track that information. That way I don't need to bring in Deals as a data source and have my contacts duplicated for each deal association.

That just seems like extra steps for what should be a simple solution. I shouldn't have duplicate rows in my report just because I'm using a Deal property in my filter criteria and the contact has multiple deal associations. 

[Jnix284 · Accepted solution]

Hi @TWallen, yes, thank you for clarifying, I understand and can confirm that based on the filters you have, the duplicate contacts showing are "correct" despite that not being what you want to see.