Summary:

This is a general description as to how to identify the source of duplicate companies returned when doing a Company search.

NOTE - Although this will help identify the possible causes of duplication, this will not cover all the reasons for it and you may still have duplicate entries even if these queries return empty.

A common cause of duplicates is when contact details may have been submitted twice, causing the program to think there is two different companies.


Symptoms:

Search for company by name and one or more results for the same company is shown other than the specific company the user searched for.

The way to solve these duplicate issues is simply by figuring out what entry is causing them to appear more than once. There is no fool proof way of finding every possible problem but a few common ones are with the contact details (epd_emailid, epd_phoneid, Plink_Type and ELink_Type)

Solution:


You can take each of these SQL queries below and search them on the database that is affected by the duplicate entries.

But if you are unsure about any of the querys below please contact you local support team for more help on this issue.

This checks for duplicate email addresses

select epd_entityid,
 (case when (epd_EntityID = 5) then 'Company' when (epd_EntityID = 13) then 'Person' end) as 'Entity',
 epd_recordid, epd_emailid, count(epd_emailid) as 'Duplicate email addresses'
from CRMEmailPhoneData
group by epd_entityid, epd_recordid, epd_emailid
having count(epd_emailid) > 1

To find out what the company names are use set the epd_recordid as the comp_CompanyId and use the following query:

select * from Company where Comp_CompanyId = epd_recordid

This checks for duplicate phone numbers

select epd_entityid,
 (case when (epd_EntityID = 5) then 'Company' when (epd_EntityID = 13) then 'Person' end) as 'Entity',
 epd_recordid, epd_phoneid, count(epd_phoneid) as 'Duplicate phone numbers'
from CRMEmailPhoneData
group by epd_entityid, epd_recordid, epd_phoneid
having count(epd_phoneid) > 1

To find out what the company names are, use the epd_recordid as the comp_CompanyId when running the following:

select * from Company where Comp_CompanyId = epd_recordid

This checks for duplicate phone link types

select PLink_EntityID,
 (case when (PLink_EntityID = 5) then 'Company' when (PLink_EntityID = 13) then 'Person' end) as 'Entity',
 PLink_RecordID, PLink_Type, count(PLink_Type) as 'Duplicate phone links'
from phonelink
group by PLink_EntityID, PLink_RecordID, PLink_Type
having count(PLink_Type) > 1

Once you have these results run the following:

select * from phoneLink where PLink_RecordID = recordID

THEN run this
select * from Phone where Phon_PhoneId = PLink_PhoneID

THEN run this
select * from Company where comp_phone_number = 'put phone number here'

This checks for duplicate email link types

select ELink_EntityID,
 (case when (ELink_EntityID = 5) then 'Company' when (ELink_EntityID = 13) then 'Person' end) as 'Entity',
 ELink_RecordID, ELink_Type, count(ELink_Type) as 'Duplicate email links'
from EmailLink
group by ELink_EntityID, ELink_RecordID, ELink_Type
having count(ELink_Type) > 1

To find out what the company names are use the epd_recordid as the comp_CompanyId and use this:
select * from Company where Comp_CompanyId = Elink_RecordID

Once the name of the company is found it is only a matter of searching for that name to find out how many duplicates there (select * from Company where Comp_Name = 'put full company name here') are and decide which entries to erase.