Documentation

Creatio development guide
PDF
This documentation is valid for Creatio version 7.11.0. We recommend using the newest version of Creatio documentation.

Adding a duplicate search rule

Glossary Item Box

Introduction

The deduplication uses the rules that are created in the form of stored procedures. When executed, each rule fills the ContactDuplicateSearchResult table with the list of duplicates. The search results are grouped by rules and displayed on the duplicate search page. More information about this functionality can be found in the “Finding and merging duplicates” article in the user guide.

The algorithm for adding a custom rule of the duplicates search

1. If necessary, add a column to the object schema, which will be used to search for duplicates.

2. Add the stored search procedure in the application database.

3. Register the stored procedure as a new rule.

The example of adding a custom rule of the duplicates search

Case description

During the deduplication process, contacts that have the same values in the [Taxpayer ID] column should be counted as duplicates and displayed in the search results.

Case implementation algorithm

1. Adding a field, value of which will be searched for duplicates

The [Taxpayer ID] field does not exist on the contact edit page by default and you need to create it using the section wizard.  You need to add a field with “Taxpayer ID” value in the [Title] property and “UsrInn” value in the [Name in DB] property to the page of the [Contacts] section. The wizard will add the required column to the object automatically. More information about how to add a field to the page can be found in the "How to set up the page fields” article.

2. Adding the stored search procedure in the application database.

To search for duplicates in a specific section, add the corresponding stored procedure to the database. To add a stored procedure for searching duplicates by the [Taxpayer ID] field, you need to execute the following SQL query:

-- Check for the existence of a stored procedure named tsp_FindContactDuplicateByInn.
IF NOT OBJECT_ID('[dbo].[tsp_FindContactDuplicateByInn]') IS NULL
BEGIN
    -- Deleting a stored procedure.
    DROP PROCEDURE [dbo].[tsp_FindContactDuplicateByInn];
END;
GO
-- Creating stored procedure.
CREATE PROCEDURE [dbo].[tsp_FindContactDuplicateByInn] (
    -- This table parameter is transmitted only if a new contact is saved.
    -- Contains data of a new contact.
    --In the case of starting a global search for duplicates, the passed parameter does not contain data.
        @parsedConfig CreatingObjectInfo READONLY,
    -- The unique id of the user who launched the duplicate search.  
    @sysAdminUnit UNIQUEIDENTIFIER,
    -- The ID of the current rule from the table [ContactDuplicateSearchResult].
    -- This ID is created after the registration of the rule in the system.
    @ruleId UNIQUEIDENTIFIER
)
AS
BEGIN
    -- Getting the number of records from the received table to define if a global duplicates search is launched
        DECLARE @parsedConfigRowsCount INT = (SELECT COUNT(*) FROM @parsedConfig);
    --  Create a temporary table with contacts data for searching.
    CREATE TABLE #searchContact (
        [UsrInn] INT,
        [SortDate] DATETIME
    );
    -- In case of the global search, the temporary table is populated with data.
    IF @parsedConfigRowsCount = 0
    BEGIN
        -- Adding data to the temporary table to find duplicates.
        INSERT INTO #searchContact ([UsrInn], [SortDate])
        -- Request a selection of contacts data.
        SELECT
            -- Select of the Taxpayer ID dates of the contact modification.
            [UsrInn],
            MAX([ModifiedOn])
        FROM [Contact]
        -- The grouping by fields is added for the possibility of using the check for quantity.
        GROUP BY [UsrInn]
        -- The table is filled only if there is more than one contact. 
        HAVING COUNT(*) > 1;
    END;
    
    -- Filling out the results table.
    INSERT INTO [ContactDuplicateSearchResult] ([ContactId], [GroupId], [RuleId], [SysAdminUnitId])
    SELECT
        -- Contact duplicate ID.
        [vr].[Id],
        -- Forming a group number.
        DENSE_RANK() OVER (ORDER BY [vr].[SortDate] DESC, [vr].[UsrInn]),
        -- Rule ID.
        @ruleId RuleId,
        -- The ID of the user who started the duplicate search process.
        @sysAdminUnit
    FROM (
        -- A subquery that fills out the duplicates table.
        SELECT
            -- Contact ID.
            [v].[Id],
            --Contact Taxpayer ID.
            [v].[UsrInn],
            -- Sorting date.
            [r].[SortDate]
        -- The data source tables.
        FROM [Contact] [v], #searchContact r
        -- The rule that determines that the contacts are duplicates.
        WHERE [v].[UsrInn] = [r].[UsrInn]
        -- Grouping the search result.
        GROUP BY [v].[UsrInn], [r].[SortDate], [v].[Id]
    ) [vr];
END;
GO

3.

Registration of the stored procedure as a new rule

To register the stored procedure as a new rule of the duplicates search, add a corresponding record to the DuplicatesRule table. To do this, execute the following SQL query:

-- A variable that stores the value of the UId column of the Contact schema.
DECLARE @ContactUId UNIQUEIDENTIFIER;

-- Gets the value of the column UId of the Contact schema .
Set @ContactUId = (SELECT TOP 1 SysSchema.UId FROM SysSchema
WHERE SysSchema.Name = 'Contact' AND SysSchema.ExtendParent = 0);

-- Adds a new rule to the system.
INSERT INTO DuplicatesRule ([IsActive], [ObjectId], [ProcedureName], [Name]) VALUES
  (1, @ContactUId, 'tsp_FindContactDuplicateByInn', 'Contacts duplicates.Taxpayer ID');

To apply the changes you need to refresh the section page and empty the browser cache. After that, a new rule will appear in the rules search list (Fig.1).

Fig. 1. Duplicates search rule by [Taxpayer ID] field

 

 

© bpm'online 2002-2018.

Did you find this information useful?

How can we improve it?