Microsoft Dynamics AX

Career day speaker at Mensa HungarIQa

The Mensa high IQ society in Hungary has started a series of sessions dedicated for showing the various IT professions in detail on a career day. As a proud member of the society I have the pleasure to show what do I do as Software Engineer and Solution Architect for Enterprise Resource Manager systems, what are my day-to-day activities with Microsoft Dynamics AX 2012 and Dynamics 365 Finance and Operations.

Mensa career day

This series of events are organized in order to help people who are just about to make the decision of their life about what to become when they graduate after high school. This is also to aid people, who are considering a career change. As a Mensa HungarIQa career day speaker my task is to talk about what skillsets are required to be successful in this corner of IT, what are the advantages and difficulties chasing a path like this in the industry, what perks and benefits do we get.

I also plan to demonstrate a range of tools that we use on a daily basis to show how wide range of knowledge one has to have that can be applied to a multitude of other jobs in case they consider a switch again:

  • business processes (finance, supply chain, distribution, manufacturing, etc.)
  • DevOps / Version control
  • Visual Studio development
  • Microsoft Azure
  • Virtualization and systems management / monitoring
  • Artificial Intelligence and Machine Learning models
  • Web technologies (REST, OData, etc.)
  • and others…

Active record count in ValidTimeState tables

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.

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',, common.RecId));
        tableId = dict.tableNext(tableId);

SQL record count by company

As part of our Data Upgrade from AX 2012 to Dynamics 365 Finance and Operations we had to ensure a way of telling how many records do we have per data area. Data import via entities are company-based, so we had to find out a reliable way to tell if everything has been successfully transferred. The following script can tell exactly that, show the SQL record count by company:

DECLARE @tableName NVARCHAR(255);
-- Temporary table for storing record counts
CREATE TABLE #jdm_count (TableName NVARCHAR(255), Company NVARCHAR(4), RecordCount int)
-- Cursor for getting list of User-created tables
              + '.' + QUOTENAME(
       FROM sys.objects as sOBJ
              sOBJ.type = 'U'
              AND sOBJ.is_ms_shipped = 0x0
       ORDER BY SCHEMA_NAME(sOBJ.schema_id),;
OPEN cur_name
-- Loop tables
FETCH NEXT FROM cur_name INTO @tableName
       -- Construct SQL Statement for getting company-specific record count
       SELECT @statement = 'SELECT ''' + @tableName + ''' AS [TableName]'
              + IIF(COL_LENGTH(@tableName, 'DATAAREAID') IS NOT NULL, ', DATAAREAID AS [Company]', ', '''' AS [COMPANY]')
              + ',COUNT(*) AS [RowCount] FROM ' + @tableName + ' WITH (NOLOCK)'
              + IIF(COL_LENGTH(@tableName, 'DATAAREAID') IS NOT NULL, ' GROUP BY [DATAAREAID]', '')
              + ' HAVING COUNT(*) > 0';
       -- Insert statement results in temporary table
       INSERT INTO #jdm_count (TableName, Company, RecordCount)
              EXEC sp_executeSQL @statement;
       FETCH NEXT FROM cur_name INTO @tableName
CLOSE cur_name
-- Display results
SELECT * FROM #jdm_count
ORDER BY RecordCount DESC, TableName, Company
DROP TABLE #jdm_count
Go to Top