About the Customers Entity Duplicate Check Object

The Customers entity in the Motor Loaner application includes a plug-in that checks for duplicate customer records. The plug-in uses the spCheckForCustomerMatch Database Object to provide the following matching logic:

Existing records that match a new record's FirstName, LastName, PhoneAreaCode, and Phone are flagged as absolute matches.
Existing records that match a new record's FirstName and LastName (but not the phone number) are flagged as potential duplicates.

This stored procedure receives the RecordID, FirstName, LastName, PhoneAreaCode, and Phone to check from the CheckForDuplicate method (using the values from the current GE object), and it returns a Match Type and if a match is found, a list of matching IDs. This stored procedure returns one of the following Match Values:

0: No duplicates found
1: Absolute Match Found, the record ID(s) of the absolute match will be placed in the arDuplicates array
2: Possible Match(s) found, the record ID(s) of the potential match(s) will be placed in the arDuplicates array

The text of the spCheckForCustomerMatch stored procedure appears below but you can also find it in the Database Objects service if you installed the sample application.

CREATE PROC spCheckForCustomerMatch
(@SourceID INT,
@FirstName NVARCHAR(255),
@LastName NVARCHAR(255),
@Phone NVARCHAR(20),
@PhoneAreaCode NVARCHAR(20))
AS
SELECT MatchType, ID FROM
(SELECT MatchType
= CASE
WHEN
(FirstName = @FirstName AND
LastName = @LastName AND
Phone = @Phone AND
PhoneAreaCode = @PhoneAreaCode AND
ID <> @SourceID)
THEN
1
WHEN
(FirstName = @FirstName AND
LastName = @LastName AND
ID <> @SourceID)
THEN
2
END,

ID FROM APTIFY..vwCustomers) CustomerMatch
WHERE MatchType IS NOT NULL

OLD – CREATE PROC spCheckForCustomerMatch
(@SourceID INT,
@FirstName NVARCHAR(255),
@LastName NVARCHAR(255))
AS
SELECT MatchType, ID FROM
(SELECT MatchType
= CASE
WHEN
(FirstName = @FirstName AND
LastName = @LastName AND
ID <> @SourceID)
THEN
2
END,

ID FROM APTIFY..vwCustomers) CustomerMatch
WHERE MatchType IS NOT NULL

If you have a comment on this topic, contact Aptify Documentation. If you want to return to the Aptify Community Site, please click here.

About the Customers Entity Duplicate Check Object