Working with Dynamics AX Table Meta Data

Background
Recently I had to create a custom database log for a client. Dynamics AX does come with a database log feature (with the proper license key). However, my client wanted something different to allow easier reporting and tracking of changes. The log must be searchable, filterable, and sortable based on any field. For instance, if the CustTable updates were being logged, the user must be able to search, filter, and/or sort by CustGroup, AccountNum, or any other field that was logged. The standard log does not have this capability.

It was decided that in order to achieve these goals, each table to be logged (source) should have its own log table. The log table would mirror the table structure of the source table, with some additional log specific fields, such as log type, date and time of log, changed by, etc. The custom log needed to be able to record the same table events as the standard log (inserts, updates, deletes, and rename keys).

Problem
I had to have a way to perform a field-to-field data transfer from the source table to the log table (ie. source.AccountNum --> log.AccountNum). My initial thought was to copy the data using the intrinsic "data" method on table buffer object. This method allows a table buffer (record) to be passed to the "data" method of another (empty) table buffer to be copied field for field, with the exception of system fields like RecId, RecVersion, etc. It turns out that a field-to-field data copy only works if the two table buffers are of the same table. Unfortunately, even if the field names were the same, the "data" method does not copy the data if the table buffers objects were for different tables.

I needed to write my own data copy function. My function had to be generic, because I didn't want to hard-coded the field name and the mappings between the source and log tables. Additionally, I didn't want to have to update my code each time a new field was added or removed from the source table. In order to make the log function in a generic way, I needed to the meta data within Dynamics AX.

Solution
Below is the code for the static method I created to transfer the data from the source table to the log table. I've placed comments in red to explain the interesting parts of the code.

Basically the method loops through the fields of the source table, and assigns it to the corresponding field in the log table. By using the SysDictTable class, I'm able to retrieve the field list for the source table. This allows me to "see" all the fields, including newly added fields. I also do not have to deal with deleted fields, because they won't appear in the field list.

The challenging part of this method was dealing with array fields. These are fields that are based on an extended data type that is an array. A prime example of this type of field is the "Dimension" field found in many master and transactions tables. Each array element translates to a single field in the table in the database. In Dynamics AX, an array field appears as a single field would. It has a field ID just like any other field, with one exception. An array field has what's called an "extended field ID". This is the combination of the field's ID and the element's position or index in the array. Therefore, to properly retrieve the data for an array field, you must properly address the field's array element to get the correct field ID. This can be done via the "fieldId2Ext" function.

static void copyDataToLog(Common _fromTable, Common _toTable)
{
   SysDictTable fromDictTable;
   SysDictField fromDictField;
   SysDictTable toDictTable;
   SysDictField toDictField;
   int fields;
   int fieldIndex;
   int arraySize;
   int arrayIndex;
   int fromFieldId;
   int toFieldId;

   ;
   
   // Instantiate the SysDictTable object. The SysDictTable class
   // extends the DictTable class. By using the SysDictTable class,
   // instead of the DictTable class, you get the ability override methods,
   // and get the field list, among other things.
   //
   // I use the SysDictField class in the same way as I use the
   // SysDictTable class to process and the retrieve meta data
   // about the fields. It provides information about each field.
   
   fromDictTable = new SysDictTable(_fromTable.TableId);
   toDictTable = new SysDictTable(_toTable.TableId);
   
   // Loop through the field list for the source table.
   // Note that the loop excludes the system fields, such as
   // RecId, RecVersion, CreatedDate, CreatedBy, etc. Each
   // table will have its own set of system fields, which will be
   // populated by Dynamics AX when the record is inserted.
   
   fields = fromDictTable.fieldCnt();

   for (fieldIndex = 1; fieldIndex <= fields; fieldIndex++)
   {
       fromDictField = new SysDictField(fromDictTable.id(),fromDictTable.fieldCnt2Id(fieldIndex));

       if (!fromDictField.isSystem())
       {   
           // Some tables contain fields that inherit from array extended data
           // types. An example of an array field is the "Dimension" field,
           // found in tables throughout the database.
           // Each element in the array field corresponds to a real field
           // in the database. The concept of field ID still exists for array
           // fields, but with a little twist. The field ID for each
           // array element is called an "extended field ID". This extended
           // field ID is the combination of the field ID and the array index
           // for the element.
           // The function "fieldId2Ext" provides a way to get an array field's
           // field ID.
           
           if (fromDictField.arraySize() > 1)
           {
               // Field is an array (ie. Dimension).
               arraySize = fromDictField.arraySize();
               
               // Loop through each array field's element to copy its value
               // to the corresponding log array field's element.
               
               for (arrayIndex = 1; arrayIndex <= arraySize;arrayIndex++)
               {
                   fromFieldId = fieldId2Ext(fromDictField.id(),arrayIndex);
                   toFieldId = fieldId2Ext(fieldName2Id(_toTable.TableId, fromDictField.name()), arrayIndex);                
                   
                   // Note that field values may be assigned and retrieved using
                   // the field ID instead of the field name.
                   
                   _toTable.(toFieldId) = _fromTable.(fromFieldId);
               }
           }
           else
           {   
               // Use the "fieldName2Id" function to convert the field name
               // from the source table to the corresponding field ID for the
               // log table. This is possible because the field names in the log
               // table are the same as the source table.
               
               toFieldId = fieldName2Id(_toTable.TableId,fromDictField.name());

               _toTable.(toFieldId) = _fromTable.(fromDictField.id());
           }
       }
   }
}

Conclusion
Using this static method, I'm able to copy the source table fields to the log table fields. This method illustrates one possible usage of system meta data as provided by the "Dict*" classes to perform generic work. These classes provided the necessary information about the table and fields to perform the task without the need to hard-code table or field names. There are several other "Dict" classes that provide information about other application objects.