How table and class customizations are stored in UtilElements?

In my previous article on finding all the layers on which an object has been modified, I explained how the UtilElements system table can be used for this. You can read that post here.

Some of my readers mailed me asking that they were not getting the correct results when querying the UtilElements table. I clarified what the issue could be.

This post will discuss further on the UtilElements table and what are the things one need to be aware of when querying this table.

Let us first see how table customizations are stored in the UtilElements table.

Let us take the Address table as an example.

On my system, I have just added a comment in the axAddress() method. As you can see in the below image, the table is shown as existing on the usr layer and you can also see the actual changes.


Now I'll query the UtilElements table where name is Address.

while select utilLevel from utilElements
where utilElements.name == tableStr(Address) &&
utilElements.recordType == UtilElementType::Table
{
print utilElements.utilLevel;
}

A user may be expecting it to print sys, syp & usr but the output is only sys & syp.

The reason is that all table method modifications are stored as a record entry where UtilElementType == UtilElementType::TableInstanceMethod and Name == name of the method added/modified.

So one may ask, how to say that this method is on the Address table. The answer lies in the ParentId field. This field holds the Id of the actual object to which this change is associated with. So we will modiy our above query like this

while select utilLevel from utilElements
where utilElements.parentId == tableNum(Address) &&
utilElements.recordType == UtilElementType::TableInstanceMethod &&
utilElements.name == identifierStr(axAddress)
{
print utilElements.utilLevel;
}

Now the output will be sys & usr because the axAddress() method exists on the sys layer and havs been modified on the usr layer. Since there are no changes to it on the syp layer, it is not printed.

In the above query, the utilElements.parentId == tableNum(Address) line can be omitted.

Still, we have not got our expected result. We expect an output as sys, syp & usr.

The best way for this is to simplify our code as

while select utilLevel from utilElements group by utilLevel
where utilElements.parentId == tableNum(Address) ||
utilElements.Name == tableStr(Address)
{
print utilElements.utilLevel;
}

In the above query, we are querying for all records whose parentId is Adress table's id or Address table itself. We do a grouping by UtilElevel because there may me many records, we are just interested in the layers.

At the table level, the following modifications are stored as seperate record types.

  • Field modification - UtilElementType::RecordTableField;

  • Field group modification - UtilElementType::TableFieldGroup;

  • Index modification - UtilElementType::TableField;

  • Relation modification - UtilElementType::TableRelation;


The same reasoning also apply for classes.

If you modify a class method, you should query it where RecordType == UtilElementType::ClassInstanceMethod or simply query where parentId == the Id of your class.

A full copy of the Forms and Reports are stored for each layer.

So for forms, a simple query will return the expected result.

while select utilLevel from utilElements group by utilLevel
where utilElements.Name == formStr(SalesTable) &&
utilElements.recordType == UtilElementType::Form
{
print utilElements.utilLevel;
}

So next time you have to query the UtilElements table, remember how modifications to tables and classes are stored and frame your query to extract the correct information. Simple rule of thumb, use combination of (parentId + Name) in your query to extract the list of all layers on which an object has been customized.

Hope this helps. That is all for today but do check back again soon.

Comments

Popular posts from this blog

How to add empty ranges in query

Get selected records in Dynamics AX 2012

The field with ID '0' does not exist in table - Cause and resolution.