0 votes

When 2 records are merged, there is no clear trace of that merge, saying "this record has been deleted during the merge with THIS record".

The only solution I'm thinking about consists in looking at the sys_changed table.

So if I want to detect if a record has been merged into another one, my idea is to write a function that will search in sys_changed for a record updated by the same user at the same time (+/- 10seconds).

   CREATE FUNCTION [CUSTOM_MERGED_RECORD](@K_TABLE float, @KEY float)
RETURNS FLOAT AS 
BEGIN
    DECLARE @MERGED_KEY float;
    SELECT @MERGED_KEY = sc2.K_1
    FROM SYS_CHANGED sc
        inner join sys_changed sc2 
            on sc2.k_table = sc.k_table and sc2.USERNAME = sc.USERNAME 
            and sc2.K_1 != sc.K_1 and sc2.OPERATION = 'B' and sc2.K_2 = 0 and sc2.K_3 = 0
    WHERE 
        sc.OPERATION = 'D' and sc.K_TABLE = @K_TABLE AND sc.K_1 = @KEY
        and ABS(DATEDIFF(s, sc.D_CHANGE, sc2.d_change)) < 10

    RETURN @MERGED_KEY
END;

Is there a better solution?

asked in Database by (237 points)
edited by

3 Answers

+1 vote
Best answer

I found a way to track the merge of two contacts using the workflow :

function PrepareDuplicateMerge(key2, key1, dataSet) {
    storePrepareMerge(key2, key1);
}

function OnAfterDeleteCont(contactKey) {
    checkMergedContact(contactKey);
}

In the PrepareDuplicateMerge, I store the 2 keys and the date in a dedicated table
In the OnAfterDeleteCont, I check if the key is in the table, with a date in the last hour...
In my example, I stored the deleted key as well as the merged key in another dedicated table.

/**
* @summary PRO-2016/02-03486 : BOZAR - Intégration SRO Régie
* @author Pierre Le Cocquen
* @since 26/06/2017
* @description Store the keys of contacts about to be merged
* @param{number} : key1 : firsrt contact key
* @param{number} : key2 : second contact key
*/
function storePrepareMerge(key2, key1) {
    var now = getSQLDate(new Date()),
        sql = "INSERT INTO <#table name=CUSTOM_PREPARE_MERGED_CONTACTS> (K_CONTACT_1, K_CONTACT_2, D_PREPARE) values(:param1, :param2, :param3)"
    database.ExecSQL(sql, key1 + "\n" + key2 + "\n" + now, false);
}

/**
* @summary PRO-2016/02-03486 : BOZAR - Intégration SRO Régie
* @author Pierre Le Cocquen
* @since 26/06/2017
* @description If the deleted contact has been merged, store its key as well as the key of the merged contact
* @param{number} : deletedContactKey : key of the deleted contact
*/
function checkMergedContact(deletedContactKey) {
    // Check if the key of the deleted contact has been stored during the preparation of a merge
    var now = getSQLDate(new Date()),
        sql =
        "select K_CONTACT_2 as K_CONTACT_MERGED, 'K_CONTACT2' as MergedContact from <#table name=CUSTOM_PREPARE_MERGED_CONTACTS> where K_CONTACT_1 = :param1 and datediff(hour, D_PREPARE, getdate()) < 1\
         union \
         select K_CONTACT_1, 'K_CONTACT1' from <#table name=CUSTOM_PREPARE_MERGED_CONTACTS> where K_CONTACT_2 = :param2 and datediff(hour, D_PREPARE, getdate()) < 1",
        tempContext = Database.OpenTemporaryContext(),
        ds = Database.ExecuteSystemSQLQuery(0, tempContext, sql, deletedContactKey + "\n" + deletedContactKey, true, true, 1);

    // If the key of the deleted contact has been stored during the preparation of a merge
    if (ds && ! ds.IsEmpty) {
        // Store the key of the merged contact and the deleted contact
        ds.First;
        var mergedContactKey = ds.FieldByName("K_CONTACT_MERGED").AsFloat,
            sql = "INSERT INTO <#table name=CUSTOM_MERGED_CONTACTS> (K_CONTACT_DELETED, K_CONTACT_MERGED, D_SYNCHRONIZE) values(:param1, :param2, :param3)";
        database.ExecSQL(sql, deletedContactKey + "\n" + mergedContactKey + "\n" + now, false);

        // Delete the record in CUSTOM_PREPARE_MERGED_CONTACTS
        var mergedContact = ds.FieldByName("MergedContact").AsString,
            sql = "delete from  <#table name=CUSTOM_PREPARE_MERGED_CONTACTS> where K_CONTACT_1 = :param1 and K_CONTACT_2 = :param2",
            params = mergedContact === "K_CONTACT2" ? deletedContactKey + "\n" + mergedContactKey : mergedContactKey + "\n" + deletedContactKey;
        database.ExecSQL(sql,params, false);


    }
}

/**
* @summary PRO-2016/02-03486 : BOZAR - Intégration SRO Régie
* @author Pierre Le Cocquen
* @since 26/06/2017
* @description format a javascript date in SQL server format
* @param{date} : date : date to format
* @return{stringà formatted date
*/
function getSQLDate(date) {
    if (!date)
        date = new Date();

    function twoDigits(number) {
        return number < 10 ? "0" + number : "" + number;
    }
    function threeDigits(number) {
        return number < 10 ? "00" + number : (number < 100 ? "0" + number : "" + number);
    }

    var year = date.getYear(),
        month = twoDigits(date.getMonth() + 1),
        day = twoDigits(date.getDate()),
        hours = twoDigits(date.getHours()),
        minutes = twoDigits(date.getMinutes()),
        seconds = twoDigits(date.getSeconds()),
        milliSeconds = threeDigits(date.getMilliseconds());

    return year + "-" + month + "-" + day + " " + hours + ":" + minutes + ":" + seconds + "." + milliSeconds;
}
answered by (237 points)
selected by
Out of curiosity, are both events executed when you do AutoMerge?
Yes they are! (ouf...)
Well done then!
+1 vote

Hello Pierre,

Ok I add to implement something like you did to detect merged contact, I did some improvements to your solution.

First here is the SQL to create the tables :

CREATE TABLE [ADMINEFFICY].[CUSTOM_PREPARE_MERGED_CONTACTS](
 [K_CONTACT_1] [float] NOT NULL,
 [K_CONTACT_2] [float] NOT NULL,
 [D_PREPARE] [datetime] NOT NULL
);

CREATE TABLE [ADMINEFFICY].[CUSTOM_MERGED_CONTACTS](
 [K_CONTACT_DELETED] [float] NOT NULL,
 [K_CONTACT_MERGED] [float] NULL,
 [D_SYNCHRONIZE] [datetime] NULL
);

Then here is the Workflow code I used, I add the entity detection in the PrepareDuplicateMerge in order to avoid to log Companies or Projects merging. I also removed the Date parts to find SQL Date I preferred to used directly the SQL Date functions

function PrepareDuplicateMerge(key2, key1, dataSet) {
    if (dataSet.Locate('FIELDNAME', 'K_CONTACT', 0)) {
        //Do this only for Contacts
        storePrepareMerge(key2, key1);
    }
}

function OnAfterDeleteCont(contactKey) {
    checkMergedContact(contactKey);
}

/**
* @summary PRO-2016/01-03474 :
 *    base on PRO-2016/02-03486 : BOZAR - Intégration SRO Régie
* @author Pierre Le Cocquen
* @since 26/06/2017
* @description Store the keys of contacts about to be merged
* @param{number} : key1 : firsrt contact key
* @param{number} : key2 : second contact key
*/
function storePrepareMerge(key2, key1) {
    var sql = 'INSERT INTO <#table name=CUSTOM_PREPARE_MERGED_CONTACTS> (K_CONTACT_1, K_CONTACT_2, D_PREPARE) values(:param1, :param2, <#IFDB DB="OR" THEN="sysdate"><#IFDB DB="MS" THEN="getDate()">)';
    database.ExecSQL(sql, key1 + "\n" + key2, false);
}

/**
* @summary PRO-2016/02-03486 : BOZAR - Intégration SRO Régie
* @author Pierre Le Cocquen
* @since 26/06/2017
* @description If the deleted contact has been merged, store its key as well as the key of the merged contact
* @param{number} : deletedContactKey : key of the deleted contact
*/
function checkMergedContact(deletedContactKey) {
    // Check if the key of the deleted contact has been stored during the preparation of a merge
    var sql = "select K_CONTACT_2 as K_CONTACT_MERGED, 'K_CONTACT2' as MergedContact from <#table name=CUSTOM_PREPARE_MERGED_CONTACTS> where K_CONTACT_1 = :param1 and datediff(hour, D_PREPARE, getdate()) < 1 \
                union \
                select K_CONTACT_1 as K_CONTACT_MERGED, 'K_CONTACT1' as MergedContact from <#table name=CUSTOM_PREPARE_MERGED_CONTACTS> where K_CONTACT_2 = :param2 and datediff(hour, D_PREPARE, getdate()) < 1";
    var tempContext = Database.OpenTemporaryContext();
    var ds = Database.ExecuteSystemSQLQuery(0, tempContext, sql, deletedContactKey + "\n" + deletedContactKey, true, true, 1);

    // If the key of the deleted contact has been stored during the preparation of a merge
    if (ds && !ds.IsEmpty()) {
        // Store the key of the merged contact and the deleted contact
        ds.First();
        var mergedContactKey = ds.FieldByName("K_CONTACT_MERGED").AsFloat;
        var sql = 'INSERT INTO <#table name=CUSTOM_MERGED_CONTACTS> (K_CONTACT_DELETED, K_CONTACT_MERGED, D_SYNCHRONIZE) values(:param1, :param2, <#IFDB DB="OR" THEN="sysdate"><#IFDB DB="MS" THEN="getDate()">)';
        database.ExecSQL(sql, deletedContactKey + "\n" + mergedContactKey, false);

        // Delete the record in CUSTOM_PREPARE_MERGED_CONTACTS
        var mergedContact = ds.FieldByName("MergedContact").AsString;
        var sql = "delete from  <#table name=CUSTOM_PREPARE_MERGED_CONTACTS> where K_CONTACT_1 = :param1 and K_CONTACT_2 = :param2";
        var params = (mergedContact === "K_CONTACT2" ? deletedContactKey + "\n" + mergedContactKey : mergedContactKey + "\n" + deletedContactKey);
        database.ExecSQL(sql, params, false);
    }
}
answered by (1.9k points)
Good enhancements.
Fine!
0 votes

Pierre.

I'm afraid your solution is not water tight for the automatic merge of a multiple records. You will have multiple merges in a short period, and so it will be difficult to match them.

If the merge happend recently (last 14 days), you should be able to find a trace in the log files of the application server (COM+)

DSoftServer_yyyymmdd.log

>S1: MergeRelations 82117-82110
<S1 C1: (140 ms (0)) MergeRelations 82117-82110

In theory, you could make a scheduled process that on daily basis parses the log file and log all merges. I know, it's not cool.

For the moment, I have no better idea then what you proposed.

answered by (7.2k points)
This solution can't be used in our cloud. Furthermore, the DSoftServer log file can be too big.
1,225 questions
1,493 answers
1,828 comments
328 users