ID change in Dynamics AX data dictionary

Posted by on

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:

  1. Data are still in the database
  2. Table metadata in AOT contain the new ID
  3. 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:

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())
    {
        //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)
        {
            //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("Field %1 - ID changed (%2 -> %3)",
                            dictField.name(),
                            sqlDictionaryField.fieldId,
                            dictField.id()));
                    }
                }
            }
            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.

3 Comments

  1. Hi,

    I was wondering if you never had any issues with the recid’s.
    I’ve tried this job today but I’m having unique constraint problems on a specific table with a recid index.

    Kind regards,

    Kevin

  2. No, I haven’t, because the script touch the only thing – data in SqlDictionary table. It doesn’t touch any business data, any metadata (object ID’s or DB indices) or anything else.
    The error indicates some problem in your data (e.g. missing data upgrade script).

  3. Hi Martin, that’s a useful post, thanks for publishing it. When running it I found that in my scenario I was getting field ID clashes. This was where I have used a backup from my production DB in my testing environment.

    To get around this, I reversed the order of the field IDs, but used the core of your update script. Here’s the method I used for that reversal, which may be useful to others:

    List buildReverseFieldIdList(SysDictTable _dictTable)
    {
    FieldId fieldIdForList;
    List reverseFieldList = new List(Types::Class);
    DictField dictFieldForList;
    ;
    fieldIdForList = _dictTable.fieldNext(0);
    while (fieldIdForList)
    {
    dictFieldForList = _dictTable.fieldObject(fieldIdForList);
    if (dictFieldForList.isSql() && !dictFieldForList.isSystem())
    {
    reverseFieldList.addStart(dictFieldForList);
    }
    fieldIdForList = _dictTable.fieldNext(fieldIdForList);
    }
    return reverseFieldList;
    }

Comments are closed.