Thursday, December 22, 2011

Delete duplicate records from table

Sometimes we lost unique identity check in the tables while doing some customization in Dynamics AX. It may happen due to failure of database synchronization.

Here is the code to remove the duplicate records from a table, I used Dimension table as an example;

static void deleteduplicate(Args _args)
{
    set fieldSet = new set(Types::Integer);

    // create dicindex from the unique index
    DictIndex dictIndex = new Dictindex(tablenum(Dimensions),indexnum(dimensions, DimensionIdx));
    ;

    // these are the fields from the index
    // add them to a set
    fieldset.add(fieldnum(Dimensions, DimensionCode));
    fieldset.add(fieldnum(Dimensions, Num));

    // set allow duplicates
    ReleaseUpdateDB::indexAllowDup(dictIndex);


    // delete duplicate records
    ReleaseUpdateDB::deleteDuplicatesUsingIds(tablenum(Dimensions), 1, fieldset);

    // re-enable index
    ReleaseUpdateDB::indexAllowNoDup(dictIndex);

    info('Done');
}