How to revised(synchronization) table and field ID in Dynamics AX data dictionary

if a table ID or field ID is changed in Dynamics AX, data are lost during synchronization, because the table with the old ID (and containing data) is dropped at the beginning and then a new table is created with the same structure but a different ID.The same is valid analogically for table fields.The data loss can be prevented in several ways (e.g. by data export and reimport), but one solution is really simple and painless. Realize how the situation looks in AX after the ID change (e.g. after installation of a layer with different IDs) but before the database synchronization: Data are still in the database Table metadata in AOT contain the new ID Data in SqlDictionary table contain the old ID So it’s not necessary to export data or to put old IDs down, all information is still in the system. You just have to secure that synchronization is not run prematurely.You can identify all changed IDs by comparing values in AOT with values in SqlDictionary. And the update of SqlDictionary to the new ID can prevent the regeneration of database objects during synchronization. I use several scripts for this purpose, this is the simplest one:


The other day we upgraded to Cumulative Update 3 for Dynamics AX 2012. After that we got some problems in the SqlDictionary table - several table and field IDs did not much those in the AOT anymore.One of our developers found this post, which contained a job fixing such issues. We had to correct the job a bit, otherwise it failed when trying to process Views or update field IDs that had been "swapped" during upgrade (e.g. before: fieldId1 = 6001, fieldId2 = 6002; after installing CU3: fieldId1 = 6002, fieldId2 = 6001).This is the final version of the job. I know the change violates DRY principle, but for an ad-hoc job it is probably OK


static void THK_fixTableAndFieldIds(Args _args)
{
    Dictionary dictionary = new Dictionary();
    SysDictTable dictTable;
    DictField dictField;
    TableId tableId;
    FieldId fieldId;
    SqlDictionary sqlDictionaryTable;
    SqlDictionary sqlDictionaryField;

    setPrefix("Update of data dictionary IDs");
    tableId = dictionary.tableNext(0);
    ttsbegin;

    while (tableId)
    {
        dictTable = new SysDictTable(tableId);

        setPrefix(dictTable.name());

        if (!dictTable.isSystemTable() && !dictTable.isView())
        {
            //Finds table in SqlDictionary by name in AOT, if ID was changed.
            //Empty field ID represents a table.
            select sqlDictionaryTable
                where   sqlDictionaryTable.name     == dictTable.name()
                &&      sqlDictionaryTable.fieldId  == 0
                &&      sqlDictionaryTable.tabId    != dictTable.id();

            if (sqlDictionaryTable)
            {
                info(dictTable.name());
                //Updates table ID in SqlDictionary
                if (ReleaseUpdateDB::changeTableId(
                                                    sqlDictionaryTable.tabId,
                                                    dictTable.id(),
                                                    dictTable.name())
                                                   )
                {
                    info(strFmt("Table ID changed (%1 -> %2)", sqlDictionaryTable.tabId, dictTable.id()));
                }
            }

            fieldId = dictTable.fieldNext(0);

            //For all fields in table
            while (fieldId)
            {
                dictField = dictTable.fieldObject(fieldId);

                if (dictField.isSql() && !dictField.isSystem())
                {
                    //Finds fields in SqlDictionary by name and compares IDs
                    select sqlDictionaryField
                        where   sqlDictionaryField.tabId    == dictTable.id()
                        &&      sqlDictionaryField.name     == dictField.name()
                        &&      sqlDictionaryField.fieldId  != 0
                        &&      sqlDictionaryField.fieldId  != dictField.id();

                    if (sqlDictionaryField)
                    {
                        //Updates field ID in SqlDictionary
                        if (ReleaseUpdateDB::changeFieldId(
                                                            dictTable.id(),
                                                            sqlDictionaryField.fieldId,
                                                            -dictField.id(),
                                                            dictTable.name(),
                                                            dictField.name())
                                                           )
                        {
                            info(strFmt("Pre-update: Field %1 - ID changed (%2 -> %3)",
                                dictField.name(),
                                sqlDictionaryField.fieldId,
                                -dictField.id()));
                        }
                    }
                }
                fieldId = dictTable.fieldNext(fieldId);
            }

            fieldId = dictTable.fieldNext(0);

            //For all fields in table
            while (fieldId)
            {
                dictField = dictTable.fieldObject(fieldId);

                if (dictField.isSql() && !dictField.isSystem())
                {
                    select sqlDictionaryField
                        where   sqlDictionaryField.tabId    == dictTable.id()
                        &&      sqlDictionaryField.name     == dictField.name()
                        &&      sqlDictionaryField.fieldId  < 0;

                    if (sqlDictionaryField)
                    {
                        //Updates field ID in SqlDictionary
                        if (ReleaseUpdateDB::changeFieldId(
                                                            dictTable.id(),
                                                            sqlDictionaryField.fieldId,
                                                            -sqlDictionaryField.fieldId,
                                                            dictTable.name(),
                                                            dictField.name())
                                                           )
                        {
                            info(strFmt("Final update: Field %1 - ID changed (%2 -> %3)",
                                dictField.name(),
                                sqlDictionaryField.fieldId,
                                -sqlDictionaryField.fieldId));
                        }
                    }
                }
                fieldId = dictTable.fieldNext(fieldId);
            }
        }
        tableId = dictionary.tableNext(tableId);
    }
    ttscommit;
}

If my memory isn’t failing me, this script works in AX4 – AX2012, but Axapta 3.0 doesn’t have changeTableId() a changeFieldId() methods in ReleaseUpdateDB and you have to implement them by yourself.

It is often forgotten that object IDs exist also in business data in database – one example is the ID of a table to which a document is attached. Ignoring this issue can affect database integrity, which is again felt by users as a data loss. One of possible solutions is to use my DataReferenceSearcher – although it actually doesn’t fix the found references, it helps you to write necessary data upgrade scripts.