View Examples: Same Email No Relationship

The following View shows Contact that have the same email but does not have a Contact Relationship with one another.

There is no lifeguard on duty. Swim at your own risk :-)
This View Example is offered as is and may need to be altered to fit your specific church culture. For assistance, contact your SPoC or, if you are a SPoC, contact Support for a Professional Services estimate (billed at an hourly rate).
Difficulty: Advanced
Purpose: For use by the Data Quality Team to assist in identifying possible duplicate records not found by the Duplicate Finder Routine.
Page: Contacts
 

Field List

Leave this blank to use the default fields. Alternatively, you can add any of the following fields to show helpful details for identifying duplicates:

Contacts.Last_Name 
,Contacts.Nickname 
,Contacts.First_Name 
,Contacts.Display_Name 
,Contacts.Email_Address 
,Contact_Status_ID_Table.Contact_Status 
,Household_ID_Table.Home_Phone 
,Contacts.Mobile_Phone 
,Household_ID_Table_Address_ID_Table.Address_Line_1 
,Household_ID_Table_Address_ID_Table.City 
,Household_ID_Table_Address_ID_Table.[State/Region] AS State 
,Household_ID_Table_Address_ID_Table.Postal_Code 
,Convert(Varchar(12),Contacts.Date_of_Birth,101) AS Date_of_Birth 
,Gender_ID_Table.Gender ,Marital_Status_ID_Table.Marital_Status 
,Household_ID_Table_Congregation_ID_Table.Congregation_Name 
,Household_ID_Table.Household_Name 
,Household_Position_ID_Table.Household_Position 
,[dp_Created].[User_Name] AS [Created By] 
,[dp_Updated].[User_Name] AS [Updated By]

View Clause

The following clause limits the list of Contacts to those with the same email address and no Contact Relationship. Contacts with the same email, but with a Contact Relationship (e.g. Married To: or Child Of):

Contacts.Email_Address IS NOT NULL AND EXISTS 
(SELECT 1 FROM Contacts C WHERE C.Email_Address IS 
NOT NULL AND C.Company = Contacts.Company AND 
C.Email_Address = Contacts.Email_Address AND 
C.Contact_ID <> Contacts.Contact_ID AND 
ISNULL(C.Household_ID,0) <> 
ISNULL(Contacts.Household_ID,0) AND NOT EXISTS 
(SELECT 1 FROM Contact_Relationships CR WHERE 
CR.Contact_ID = Contacts.Contact_ID AND 
CR.Related_Contact_ID = C.Contact_ID))

Techniques

 

Last Modified: 4/3/2018

Did this article help?
× Thank You for the Feedback