How to see SubledgerJournalEntry records not committed yet by ttsCommit()

Sometimes we want to see records before they are inserted into data base. We can do it on the SQL side by means of with (NOLOCK) option.

Let's say we are debugging creation of SubledgerJournalEntry records. Place the following code in a new query in SQL Management Studio.

USE [AxDB]
GO

SELECT journal.ACCOUNTINGCURRENCYAMOUNT, journal.POSTINGTYPE, enum.NAME as PostingTypeName
 FROM SUBLEDGERJOURNALACCOUNTENTRY as journal with (NOLOCK)
 JOIN ENUMVALUETABLE as enum
  ON journal.POSTINGTYPE = enum.ENUMVALUE
  JOIN SubledgerJournalEntry as sub with (NOLOCK)
  ON journal.SUBLEDGERJOURNALENTRY = sub.RecId
  where sub.ACCOUNTINGEVENT = 5637159590
  AND enum.ENUMID = 1811

SELECT SubledgerJournalEntry, SUM(TransactionCurrencyAmount) as sumTrans, SUM(AccountingCurrencyAmount) as sumAcc, SUM(ReportingCurrencyAmount) as sumRep FROM 
SubledgerJournalAccountEntry with (NOLOCK)

inner join SubledgerJournalEntry with (NOLOCK)
on SubledgerJournalAccountEntry.SubledgerJournalEntry = SubledgerJournalEntry.RecId
and SubledgerJournalEntry.ACCOUNTINGEVENT = 5637159590
GROUP BY SubledgerJournalAccountEntry.SubledgerJournalEntry

So, now we can see it before they are committed by ttsCommit().

How to get all related table ids from code

We can loop all relations on a table by code.

static void myGetRelatedTableNames(Args _args)
{
    myInExtCodeValueTable           myInExtCodeValueTable;
    int                             mapId;
    TableName                       relatedTableName;
    TableId                         relatedTableId;
    Set                             tablesIdsSet    = new Set(Types::Integer);
    Set                             tablesNamesSet  = new Set(Types::String);
    TableId                         tableId         = tableName2id(tableStr(myInExtCodeValueTable));
    Dictionary                      dictionary      = new Dictionary();
    SysDictTable                    dictTable       = dictionary.tableObject(tableId);
    DictRelation                    dictRelation    = new DictRelation(myInExtCodeValueTable.TableId);
    int                             mapCnt          = dictTable.relationCnt();
    container                       ret ;            
    str                             relationName;
    //create a maps of literals for all tables from the table relations
    // so that we could get tables names based on their ids
    // and if any new relation will be added to multiple external codes table
    // it is present automatically in this view
    for (mapId=1; mapId <= mapCnt; mapId++)
    {
        // elaborate if any table present many times
        relationName        = dictTable.relation(mapId);
        dictRelation.loadNameRelation(relationName);
        if(dictRelation)
        {
            relatedTableId      = dictRelation.externTable();
            relatedTableName    = tableId2pname(relatedTableId);
            tablesIdsSet.add(relatedTableId);
            tablesNamesSet.add(relatedTableName);
            info(strFmt("Table %1 - %2", relatedTableId, relatedTableName));
        }
    }
        
    ret = [tablesIdsSet.pack(), tablesNamesSet.pack()];
}

It can be useful in cases when we need, say, to open a form with a related record.

Here you can find a more elaborated example.

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(dictTable.id(), fieldId);
            list.addEnd([fieldId, fieldId2name(dictTable.id(), 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)
{
    #define.FA1('MACH10DMO-000018')
    #define.FA2('MACH20DMO-000009')
    
    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)));
    }
    
}

Result: