Mattersphere Contact MergeDelete

Original post by Robert Brown: robert.brown@morton-fraser.com

Has anyone else attempted to use the Mattersphere Contact MergeDelete Routine to merge contact records?

We have been looking at improving data quality by using the routine to reduce the number of contact duplicates we have on our system.

In testing the stock Contact MergeDelete routine in our Development environment we have found that there were issues with the Contact Links step of the procedure that required amendments to the stored procedure and that when performing a "Merge" the routine does not act as we might expect (deleting data we want to retain instead of merging it to one record).

We are also facing issues ensuring 3E is also updated correctly by the export service.

I wondered if anyone else had any experience of using this routine, if they had faced similar challenges and if it required some adaptation of the stock routine to achieve the desired result?

Thanks

Robert

Original post by Steven Rodney: srodney@kingsleynapley.co.uk



Hi Robert I did some work on this. Please see attached a text file with step by step instructions on process and possible problems.

Code:

1) Hi Laura I have reviewed the contact merge process within mattersphere. The process uses the same screen, stored procedures and SQL jobs for both DELETE and MERGE. I will use DELETE as example.

2) From admin kit select Admin Tools -> Contact Duplication Maintenance.

3) Search for the contact you want to delete.

4) Select and press [Delete].

5) If the contact is linked to a client, you will not be allowed to delete, else you will be asked are you sure

6) Click OK. A record will be created and inserted into table [dbContactMergeDelete]. This acts as a maintenance queue for deletions/merges.

7) The actual delete is carried out by a SQL Job called [Script: MCKN MatterCentre Contact MergeDelete Routine]. On LIVE its schedule is [Occurs every day every 5 minute(s) between 19:00:00 and 21:59:59]. It is recommended to execute after work as the process can be slow arduous.

 The job consists of 18 steps that executes different stored procedures
    The steps are...
    I) Flag Record to Process
    II) dbAssociates
    III) dbAssociatesMulti
    IV) dbContact_Solicitors
    V) dbContactCompany
    VI) dbContactIndividual
    VII) dbContactLinks
    VIII) dbContactReferral
    IX) dbContactSecurity
    X) dbContactEmails
    XI) dbContactNumbers
    XII) dbContactAddresses
    XIII) dbPerformance
    XIV) dbClientContacts
    XV) dbClient
    XVI) dbFile
    XVII) dbContact
    XVIII) Flag Record as Processed

9) It processes one record at a time.

10) Each time a job runs, it will check the corresponding queue for the first record whose ExecutionStatus value is 0. If and when one is found, the steps in the job are processed and any and all of the data relating to the queued object is dealt with accordingly. In the case of Contacts, data removal and merging takes place. While processing is being undertaken, the ExecutionStatus of the record is set to 1.

11) If a job fails, the ExecutionStatus of the record being processed is set to 2. The administrator can check the job history for the relevant queue to see those records which have failed and reset them to 0 if desired. They would then be processed as normal when the job schedule resumed.

12) Once a record has been successfully processed by the relevant job, the ExecutionStatus value is set to 5.

13) Currently, it will not work until we have added stored procedures and job references for each extended datatable for contact that we have. These jobs would be placed between steps I and II.

14) We can use the stored procedure [dbo.ContactMergeDelete_dbContact] as a model.

15) For [MERGE], you first search for contact you DO NOT WANT TO KEEP.

16) Click [Move]

17) Select the contact to [MERGE INTO]

18) Once merged a message displays to inform you. If the contact that was originally selected has no other links to data it will also be deleted.

20) Scripts to List Possible Tables...

    SELECT
        a1.extCall AS [sqlExtendedData]
    FROM
        dbExtendedData a1
    WHERE
        1 = 1
        AND a1.extWhere LIKE '%contid%'

    SELECT
        '[' + SCHEMA_NAME(schema_id) + '].[' + t.name + ']'AS [TableName]
    FROM
        sys.columns c
            INNER JOIN
        sys.tables t
            ON c.object_id = t.object_id
    WHERE
        1 = 1
        AND c.name LIKE '%contid%'
    GROUP BY
        SCHEMA_NAME(schema_id)
        , t.name
    ORDER BY
        TableName ASC

    SELECT
        '[' + SCHEMA_NAME(schema_id) + '].[' + t.name + ']' AS [TableName]
    FROM
        sys.columns c
            INNER JOIN
        sys.tables t
            ON c.object_id = t.object_id
    WHERE
        1 = 1
        AND c.name LIKE '%contactid%'
    GROUP BY
        SCHEMA_NAME(schema_id)
        , t.name
    ORDER BY
        TableName ASC

**THERE IS CROSS-OVER AND NOT ALL TABLES NEED TO BE ADDED**

21) e.g. Create a script for user table called [udContactAddInfo]

/*
-- Author:                      SPR
-- Date Created:                15/09/2017
-- Purpose:                     Stored Procedure For merging/deleting
--                              duplicate contacts in table
--                              [udContactAddInfo]
--
--                              See Case SD34504
-- Modified By:                 Steven Rodney - 15 September 2017
-- *******************************************************************
-- Create Stored Procedure within the Mattercentre Database
*/
IF EXISTS (
 SELECT a.name
 , a.type
 FROM sysobjects a
 WHERE a.name = 'ContactMergeDelete_udContactAddInfo'
 AND a.type = 'P'
 )
BEGIN
 DROP PROCEDURE [dbo].[ContactMergeDelete_udContactAddInfo]
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[ContactMergeDelete_udContactAddInfo]
AS
SET NOCOUNT ON

DECLARE @contID BIGINT
DECLARE @newContID BIGINT
DECLARE @logID INT
DECLARE @logXML XML
DECLARE @logString NVARCHAR(MAX)
DECLARE @logTable TABLE (ID BIGINT)

SELECT @contID = OldContID
 , @newContID = NewContID
 , @logID = LogID
 , @logString = CONVERT(NVARCHAR(MAX), ExecutionXML)
FROM dbo.GetContactMergeDeleteRecord()

BEGIN TRY
 BEGIN TRANSACTION

 DELETE dbo.udContactAddInfo
 OUTPUT deleted.contID
 INTO @logTable
 WHERE contID = @contID

 IF @@ROWCOUNT > 0
 BEGIN
 SET @logXML = (
 SELECT (
 SELECT ID AS contID
 FROM @logTable
 FOR XML PATH('')
 , TYPE
 )
 FOR XML PATH('udContactAddInfo')
 )

 IF (@logString = '<log/>')
 BEGIN
 UPDATE dbo.dbContactMergeDelete
 SET ExecutionXML = '<log>' + CONVERT(NVARCHAR(MAX), @logXML) +
                '</log>'
 WHERE logID = @logID
 END
 ELSE
 BEGIN
 UPDATE dbo.dbContactMergeDelete
 SET ExecutionXML = REPLACE(Convert(NVARCHAR(MAX), ExecutionXML),
                '</log>', CONVERT(NVARCHAR(MAX), @logXML) + '</log>')
 WHERE logID = @logID
 END
 END

 COMMIT TRANSACTION
END TRY

BEGIN CATCH
 IF @@TRANCOUNT <> 0
 BEGIN
 ROLLBACK TRANSACTION
 END

 DECLARE @err NVARCHAR(MAX)

 SET @err = ERROR_MESSAGE()

 UPDATE dbo.dbContactMergeDelete
 SET ExecutionStatus = 2
 , ExecutionError = @err
 WHERE LogID = @logID

 RAISERROR (
 'Failed to merge/delete contact record'
 , 16
 , 1
 )
END CATCH
GO

IF NOT EXISTS (
 SELECT a1.major_id
 FROM sys.database_permissions a1
 INNER JOIN sys.objects b1 ON b1.object_id = a1.major_id
 INNER JOIN sys.database_principals c1 ON c1.principal_id = a1.grantee_principal_id
 WHERE b1.name = 'ContactMergeDelete_udContactAddInfo'
 AND b1.type = 'P'
 )
BEGIN
 GRANT EXECUTE
 ON OBJECT::[dbo].[ContactMergeDelete_udContactAddInfo]
 TO [OMSRole]
 , [OMSAdminRole]
END


There are no details about whether we should erase data from security tables, but it would shrink the table if values are removed.

Original post by Steven Rodney: srodney@kingsleynapley.co.uk


Hi Robert. What I did was a proof of concept to see what we would have to do. The way we thought about the email /telephone/fax problem was to create a separate mapping table and insert data into it rather than altering the merge stored procedure too much. We will use the process

e.g.old contID = 101 DO NOT WANT TO KEEP, new ContID 12345


oldContID email = 'ErinJohnston@armyspy.com' code = 'HOME'

Code:

create table tmpContactEmailMapping
(
    newContID BIGINT NOT NULL
    , oldContID BIGINT NOT NULL
    , contEmail NVARCHAR(255) NULL
    , contCode NVARCHAR(15) NULL
    , contActive BIT NOT NULL
    , rowguid UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL CONSTRAINT DF_tmpContactEmailMapping_rowguid DEFAULT (NEWID()))
)

Table data

12345, 101, 'ErinJohnston@armyspy.com', 'HOME'

After all merging complete. Run an SELECT that LEFT JOINS onto table dbContactEmails.

Code:

SELECT
    a1.*
    , b1.*
FROM
    tmpContactEmailMapping a1
        LEFT JOIN
    dbContactEmails b1
        ON b1.contID = a1.newContID
        AND (LTRIM(RTRIM(b1.contEmail))) = (LTRIM(RTRIM(a1.contEmail)))
        AND (LTRIM(RTRIM(b1.contCode))) = (LTRIM(RTRIM(a1.contCode)))
WHERE
    1 = 1
    AND COALESCE((LTRIM(RTRIM(b1.contEmail))), '') = ''

This will give you a list of possible emails not associated with new contact.

You might want to lose the join on cont code just in case an email address has been put in as HOME and MAIN 

Do the same thing for phone numbers 

Original post by Steven Rodney: srodney@kingsleynapley.co.uk


I have added a file with my last replay as the posted reply looks unreadable

Code:

Hi Robert. What I did was a proof of concept to see what we would have to do.
The way we thought about the email /telephone/fax problem was to create a
separate mapping table and insert data into it rather than altering the merge
stored procedure too much. We will use the process

e.g.old contID = 101 DO NOT WANT TO KEEP, new ContID 12345

oldContID email = 'ErinJohnston@armyspy.com' code = 'HOME'

create table tmpContactEmailMapping
(
    newContID BIGINT NOT NULL
    , oldContID BIGINT NOT NULL
    , contEmail NVARCHAR(255) NULL
    , contCode NVARCHAR(15) NULL
    , contActive BIT NOT NULL
    , rowguid UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL CONSTRAINT DF_tmpContactEmailMapping_rowguid DEFAULT (NEWID()))
)

Table data

12345, 101, 'ErinJohnston@armyspy.com', 'HOME'

After all merging complete. Run an SELECT that LEFT JOINS onto table dbContactEmails.

SELECT
    a1.*
    , b1.*
FROM
    tmpContactEmailMapping a1
        LEFT JOIN
    dbContactEmails b1
        ON b1.contID = a1.newContID
        AND (LTRIM(RTRIM(b1.contEmail))) = (LTRIM(RTRIM(a1.contEmail)))
        AND (LTRIM(RTRIM(b1.contCode))) = (LTRIM(RTRIM(a1.contCode)))
WHERE
    1 = 1
    AND COALESCE((LTRIM(RTRIM(b1.contEmail))), '') = ''
    
This will give you a list of possible emails not associated with new contact.

You might want to lose the join on cont code just in case an email address has
been put in as HOME and MAIN  

Do the same thing for phone numbers