Friday, September 13, 2013

How to compare two records of the same table

Amongst other mundane chores, from time to time AX consultants and programmers need to check if two table records are indentical and what the difference is if they are not.

Here is a small project for AX 2012 with tmxTableBufferOperation class that provides the following static methods: 
  • getOneRecordFieldList(Common _record1) 
  • getTwoRecordsFieldList(Common _record1, Common _record2) 
  • areIdentical(Common _record1, Common _record2)
  • getDifference(Common _record1, Common _record2)
Their names are mnemonical so you won't misunderstand what they do.

There is also a batch demonstrating how to use them:

The main idea is to use reflection DictTable class to enumerate all the fields of the given table buffer and then populate containers with its field ids, names and values.

static public List getTwoRecordsFieldList(Common _record1, Common _record2)
    Common              buffer1;
    Common              buffer2;

    List                list        = new List(Types::Container);   //list of all the fields, field names and values
    List                bufferList  = new List(Types::Container);   //final list of all the fields, field names and values of these two records
    ListEnumerator      le;

    int                 i;
    fieldId             fieldId;
    DictTable           dictTable;
    DictField           dictField;

    container           c;

    if(_record1.TableId != _record2.TableId)
        error(strFmt('Both records are supposed to be of the same table type!'));

    dictTable = new DictTable(_record1.TableId);

    if (dictTable)
        // create the list of all the fields in the table
        for (i = dictTable.fieldCnt(); i; i--)
            fieldId = dictTable.fieldCnt2Id(i);
            dictField = new DictField(, fieldId);
            list.addEnd([fieldId, fieldId2name(, fieldId)]);

        buffer1 = dictTable.makeRecord();
        buffer2 = dictTable.makeRecord();

        select buffer1 where buffer1.RecId == _record1.recId;
        select buffer2 where buffer2.RecId == _record2.recId;

        le = list.getEnumerator();
        while (le.moveNext())
            c = le.current();
            bufferList.addEnd([[conPeek(c,1), conPeek(c,2), buffer1.(conPeek(c,1))],
                               [conPeek(c,1), conPeek(c,2), buffer2.(conPeek(c,1))]]);
    return bufferList;

And how you can use it.

static void tmxCompareFixedAssets(Args _args)
    List                    list;
    ListEnumerator          le;
    container               c;
    list = TmxTableBufferOperation::getDifference(AssetTable::find(#FA1), AssetTable::find(#FA2));
    le      = list.getEnumerator();
    info('Difference 1 <> 2');
    while (le.moveNext())
        c = le.current();
        info(strFmt("%1: %2 <> %3", conPeek(conPeek(c,1), 2), conPeek(conPeek(c,1), 3),  conPeek(conPeek(c,2), 3)));



Y.K. said...

I implemented something similar on AX 2009 (albeit with a different use - storing certain change history in the database). Some things I've noticed can be used to improve the code:

A. strfmt will throw an uncaughtable error if the field is something it can't handle (like arrays). This will in particular hit AX 2009 and previous with their Dimension field arrays. DictField.arraySize() gives us the array, and if we want we can continue inside the array and find the needed difference (note that table.(fieldnum)[arrayIndex] syntax doesn't work as is - it gives us the entire array).

B. If the table contains unusual type fields (container, class, etc.) same crash happens.

C. areIdentical() isn't needed as is - we can just use buf2con() on both sides and compare.

D. The current code loops over all fields once to get them into the list and again to get the differences. The second loop can be avoided if we use a different data class there - Set::create(list.pack()) actually works (on 2009 at least, didn't test on 2012), and then Set::difference().

E. getTwoRecordsFieldList() may be better off with a server modifier and returning a container (list.pack()).

F. selects should of course use firstonly.

wojzeh said...

Thank you Y.K. for your input!