co_individual_dup_check

This stored procedure is used in the Duplicate Check Child Form on the Individual Object.

It attempts to find possible duplicate records when a new Individual is added.

Where Used

All Individual Forms.

Logic

Any individuals who meet the following conditions will be flagged as a suspected duplicate:

  • The SOUNDEX values of first name and last name are equal
  • Any of the following conditions is true:
    1. the existing individual has an empty city and state
    2. the existing individual has a primary address with the exact same city and state of the individual being added
    3. the existing individual has a business address with the exact samecity and state of the individual being added

SQL Text

Here is the text of the SP as of 2006.02. The text may change from build to build. Check your database for the exact text. 

CREATE PROCEDURE [dbo].[co_individual_dup_check] 
 
	@ind_first_name nvarchar(255),
	@ind_last_name nvarchar(255),
	@adr_state nvarchar(255),
	@adr_city nvarchar(255),
	@ad2__adr_state nvarchar(255),
	@ad2__adr_city nvarchar(255),
	@ind_cst_key av_key
 
AS
 
SET nocount ON
 
SELECT ind_cst_key,
 [Individual] = isnull(cxa.cxa_mailing_label_html, ind_full_name_cp)
FROM co_individual (nolock) 
 JOIN co_customer (nolock) ON cst_key = ind_cst_key 
 LEFT JOIN co_customer_x_address cxa (nolock) ON cxa.cxa_key = cst_cxa_key 
 LEFT JOIN co_address adr (nolock) ON adr.adr_key = cxa.cxa_adr_key 
WHERE soundex(ind_first_name)=soundex(@ind_first_name) 
 AND soundex(ind_last_name)=soundex( @ind_last_name) 
 AND ( (adr.adr_state IS NULL AND adr.adr_city IS NULL ) OR (adr.adr_state = @adr_state AND adr.adr_city = @adr_city ) 
OR (adr.adr_state = @ad2__adr_state AND adr.adr_city = @ad2__adr_city) )
 AND (ind_cst_key IS NOT NULL OR ind_cst_key <> @ind_cst_key)
 AND @ind_cst_key IS NULL
 AND cst_delete_flag=0

Altering Logic

If you want to alter this logic, do not change the SP. Make a new version of the SP and name it client_xyz_co_individual_dup_check. Then edit the duplicate check child form and change the select sql to run your new SP instead. Be sure to lock the child form.