web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Blogs / Goshoom. NET Dev Blog / ID change in Dynamics AX da...

ID change in Dynamics AX data dictionary

Views (5019)
Martin Dráb Profile Picture Martin Dráb 237,976 Most Valuable Professional

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.


This was originally posted here.

Comments

*This post is locked for comments