CiviCRM Community Forums (archive)

Hi All,

I'm having a heck of a time creating a view that doesn't give me unexplainable duplicates. The situation is as follows:

I have contacts of type individual spread across different groups.
Some, but not all individuals have active "employee-of" relationships with organization contacts.
Some, but not all individuals have inactive "employee-of" relationships with organization contacts.
Some, but not all individuals have both active and inactive "employee-of" relationships with organization contacts.
Some, but not all individuals have an "employee-of' at all.

The view I've created uses views relationships (CiviCRM Relationships: Contact ID A) to grab the employer website and display this along with individual contact information.
It filters on group id= 7.

Here is a specific case for a duplicate:

John Doe - Current Employee of Coca Cola. Has 2 past employee of relationships, inactive.

In the view, John Doe appears twice.  Once with the correct, current title and employer and correct website, and again with the correct, current title and employer but no website.

Now, if this guy appeared three times, I would say: "ok, there's something going on with the employee-of relationships", but he has 3 employee-of relationships, and shows "only" twice, plus the correct company and title are showing for both.

Here's the clincher:

When I add the filter "Is relationship active=true" John Doe appears correctly, only once; but I lose all the individuals that have inactive employee-of relationships.

I've also tried getting fancy with AND/OR groups in filters, but I end up where I started.

I have selected the "distinct" option in "query settings", to no avail.

This is the query being run by views (without the "relationship active=true" filter):

SELECT DISTINCT civicrm_contact.id AS id, civicrm_contact.first_name AS civicrm_contact_first_name, civicrm_contact.middle_name AS civicrm_contact_middle_name, civicrm_contact.last_name AS civicrm_contact_last_name, civicrm_contact.job_title AS civicrm_contact_job_title, civicrm_contact.organization_name AS civicrm_contact_organization_name, civicrm_contact.image_URL AS civicrm_contact_image_url, civicrm_contact_civicrm_relationship__civicrm_website.url AS civicrm_contact_civicrm_relationship__civicrm_website_url
FROM
{civicrm_contact} civicrm_contact
LEFT JOIN {civicrm_relationship} civicrm_relationship ON civicrm_contact.id = civicrm_relationship.contact_id_a
LEFT JOIN {civicrm_contact} civicrm_contact_civicrm_relationship ON civicrm_relationship.contact_id_b = civicrm_contact_civicrm_relationship.id AND civicrm_relationship.relationship_type_id = '4'
LEFT JOIN {civicrm_group_contact} civicrm_group_contact ON civicrm_contact.id = civicrm_group_contact.contact_id
LEFT JOIN {civicrm_group} civicrm_group ON civicrm_group_contact.group_id = civicrm_group.id
LEFT JOIN {civicrm_website} civicrm_contact_civicrm_relationship__civicrm_website ON civicrm_contact_civicrm_relationship.id = civicrm_contact_civicrm_relationship__civicrm_website.contact_id
WHERE (( (civicrm_group.id = '7') ))
ORDER BY civicrm_contact_last_name ASC

Any ideas?

Hi Donald,

No dice. Same results on aggregation. 


I found some posts relating duplicate contact results to an issue related to differences in phone/web/email fields set as main/home/work/etc.

I checked both contacts (individual and org) for multiple contact fields tagged as primary, but could not find any.



Ack.