In the previous blog post we have explored how to tell how many records do we have per company account using a T-SQL Query. But AX does have a concept which SQL cannot cope with, and makes it a bit harder to tell the active record count in ValidTimeState tables.

SQL record count by company

The following job can pull back this value for us, in order to validate if data migration row counts are matching between AX 2012 and D365FO.

static void WIK_findValidTimeStateKey_Tables(Args _args)
{
    DictTable           dictTable;
    Dictionary          dict = new Dictionary();
    TableId             tableId;
    Common              common;
    date                currentDate = systemDateGet();
 
    setPrefix('Record count for ValidTimeStateKey tables');
    tableId = dict.tableNext(0);
 
    while (tableId)
    {
        dictTable = new DictTable(tableId);
        if (!dictTable.isTmp() && !dictTable.isTempDb() && !dictTable.isView()
            && (dictTable.configurationKeyId() ? isConfigurationKeyEnabled(dictTable.configurationKeyId()) : true)
            && dictTable.isValidTimeStateTable())
        {
            common = dictTable.makeRecord();
            select validTimeState(currentDate) count(RecId) from common;
 
            if (common.RecId)
            {
                info(strFmt('%1\t%2', dictTable.name(), common.RecId));
            }
        }
 
        tableId = dict.tableNext(tableId);
    }
}