Hi,
Can any one know about Adding a duplicate search rule for contact mini page here we are adding a new filed in contact mini page Title is ZIP/postal code and name in DB is Zip.
we tried it in code level after saving the code. it was Showing this rule in duplicate rules section but this rule is not applying while saving the contact records.
Here The Script :
IF NOT OBJECT_ID('[dbo].[tsp_FindContactDuplicateByZip]') IS NULL
BEGIN
DROP PROCEDURE [dbo].[tsp_FindContactDuplicateByZip];
END;
GO
CREATE PROCEDURE [dbo].[tsp_FindContactDuplicateByZip] (
@parsedConfig CreatingObjectInfo READONLY,
@sysAdminUnit UNIQUEIDENTIFIER,
@ruleId UNIQUEIDENTIFIER
)
AS
BEGIN
DECLARE @parsedConfigRowsCount INT = (SELECT COUNT(*) FROM @parsedConfig);
DECLARE @zipType NVARCHAR(50) = N'Zip';
IF OBJECT_ID('tempdb..#searchContact') IS NOT NULL
BEGIN
DROP TABLE #searchContact
END
CREATE TABLE #searchContact (
[Number] NVARCHAR(250) COLLATE database_default,
[SortDate] DATETIME
);
IF @parsedConfigRowsCount = 0
BEGIN
INSERT INTO #searchContact ([Number], [SortDate])
SELECT
[dedup].[Number],
MAX([dedup].[SortDate]) SortDate
FROM (
SELECT
[ContactId],
[Number],
MAX([ContactModifiedOn]) AS SortDate
FROM [VwContactDuplicateSearch] WITH (NOEXPAND)
WHERE [CommunicationTypeId] IN (
SELECT
[Id]
FROM [VwCommunicationTypeWithCode]
WHERE [Code] IN (@zipType)
AND [UseforContacts] = 1
)
GROUP BY [ContactId], [Number]
) AS [dedup]
GROUP BY [dedup].Number
HAVING COUNT(*) > 1;
END;
ELSE
BEGIN
INSERT INTO #searchContact ([Number], [SortDate])
SELECT
[Number],
GETDATE() AS [SortDate]
FROM @parsedConfig
WHERE [CommunicationTypeId] IN (
SELECT
[Id]
FROM [VwCommunicationTypeWithCode]
WHERE [Code] IN (@zipType)
AND [UseforContacts] = 1
);
END;
INSERT INTO [ContactDuplicateSearchResult] ([ContactId], [GroupId], [RuleId], [SysAdminUnitId])
SELECT
[vr].[ContactId],
DENSE_RANK() OVER (ORDER BY [vr].[SortDate] DESC, [vr].[Number] ASC),
@ruleId RuleId,
@sysAdminUnit
FROM (
SELECT
[v].[ContactId],
[v].[Number],
[r].[SortDate]
FROM [VwContactDuplicateSearch] [v] WITH (NOEXPAND), #searchContact [r]
WHERE [v].[Number] = [r].[Number]
GROUP BY [v].[ContactId], [v].[Number], [r].[SortDate]
) [vr];
END;
GO
(Thanks in Advance)
Like
Hello.
One you have added the rule it will work as a duplicate search rule for the manual or scheduled duplicate record search. You need to enable 'Use this rule on save checkbox' if you want it tot be triggered once the record is saved. The example is below:
Best regards,
Matt
Hello.
After writing code for duplicate search rule . It was showing in the duplicate rules setting page. I am trying added this rule. But duplicate search rule was not working for zip/postal code if the data is same while saving the record.Can you please suggest is there any changes to do in code level.
Thanks in Advance.
praveen n,
Firstly, the duplicate search on the mini page works the same as it does for regular page. In order to check to duplicates on save you need to put a tick on the rule itself (the step you have indicated in the screenshot).
I have copied your stored procedure and executed on the out of the box system. After adding the rule itself the manual duplicate search returned no results, even when duplicates existed. It was neither working for on save.
Please double check the logic for the stored procedure. There is not special pattern or rules for creating the procedure within the system. It should be written under common SQL language rules.
Regards,
Anastasia