Getting OnDelete value for Table metadata browser

 Thanks to Martin Drab's article New metadata API – Goshoom.NET Dev Blog, I refactored one method of my Table Metadata browser by using Microsoft.Dynamics.AX.Metadata.MetaModel API to loop through the list of table relations.

public static wzhRelatedTablesTmp populateRelatedTables(TableId _tableId, FieldId _fieldId)
    {
        wzhRelatedTablesTmp                     wzhRelatedTablesTmp;

        System.Collections.IEnumerable          relations;
        System.Collections.IEnumerator          enumRelations;
        System.Collections.IEnumerable          constraints;
        System.Collections.IEnumerator          enumConstraints;
        mtdModel.AxTableRelation                relation;
        mtdModel.AxTableRelationConstraint      constraint;
        mtdModel.AxTableRelationConstraintField constraintField;
        mtdModel.AxTable                        table;
        TableName                               relatedTableName    = tableId2Name(_tableId);
        FieldName                               relatedFieldName    = fieldId2Name(_tableId, _fieldId);
        System.Collections.IEnumerable          tables              = mtdSupport::GetAllTables();
        System.Collections.IEnumerator          enumTables          = tables.GetEnumerator();

        // Loop through all tables in the system
        while (enumTables.MoveNext())
        {
            table           = enumTables.Current as mtdModel.AxTable;
            relations       = mtdSupport::GetTableRelations(table.Name) as System.Collections.IEnumerable;
            enumRelations   = relations.GetEnumerator();
            
            while (enumRelations.moveNext())
            {
                relation = enumRelations.Current as  mtdModel.AxTableRelation;
               
                if (relation && relation.RelatedTable == relatedTableName)
                {
                    enumConstraints     = relation.Constraints.GetEnumerator();
                    while (enumConstraints.moveNext())
                    {
                        constraint = enumConstraints.Current as mtdModel.AxTableRelationConstraint;
                        if (constraint is mtdModel.AxTableRelationConstraintField)
                        {
                            constraintField = constraint as mtdModel.AxTableRelationConstraintField;
                            // Check if the relation is with a given table
                            if (constraintField && constraintField.RelatedField == relatedFieldName)
                            {
                                wzhRelatedTablesTmp.clear();
                                wzhRelatedTablesTmp.Id                         = tableName2Id(table.Name);
                                wzhRelatedTablesTmp.FieldName                  = constraintField.Field;
                                wzhRelatedTablesTmp.FieldId                    = fieldname2id(wzhRelatedTablesTmp.Id, constraintField.Field);
                                wzhRelatedTablesTmp.RelatedTableName           = table.Name;
                                wzhRelatedTablesTmp.Label                      = table.Label;
                                wzhRelatedTablesTmp.RelatedTablePName          = SysLabel::labelId2String(table.Label);
                                wzhRelatedTablesTmp.RelationName               = relation.Name;
                                wzhRelatedTablesTmp.Role                       = relation.Role;
                                wzhRelatedTablesTmp.RelatedTableRole           = relation.RelatedTableRole;
                                wzhRelatedTablesTmp.RelatedTableCardinality    = relation.RelatedTableCardinality;
                                wzhRelatedTablesTmp.IsEDTRelation              = relation.EDTRelation;
                                wzhRelatedTablesTmp.Cardinality                = relation.Cardinality;
                                wzhRelatedTablesTmp.RelationshipType           = relation.RelationshipType;
                                wzhRelatedTablesTmp.OnDeleteAction             = relation.OnDelete.ToString();
                                wzhRelatedTablesTmp.insert();
                            }
                        }
                    }
                }
            }
        }
        return wzhRelatedTablesTmp;
    }

Tables metada browser

 Following my previous article I add a simple form to browse tables metada, like, fields, relations, their attributes, etc.

It has the old good horizontal and vertical splitters to facilitate your browsing.

Filtering by attributes like System or Data entity lets rapidly find required subset of tables and fields.

Do not forget that each grid can be exported to Excel.

All data are saved in three InMemory temporary tables, and all the logic is implemented in one class wzhTableTools.

So, you can easily elaborate this solution if some other metada need to be exposed.

Challenge: I still cannot find how to get OnDelete action type for a relation. :(

Class

internal final class wzhTableTools
{
    public static wzhTablesTmp populateTables()
    {
        Dictionary      dict        = new Dictionary();
        int             numOfTables = dict.tableCnt();
        DictTable       dictTable;
        TableId         tableId;
        int             j;
        wzhTablesTmp    wzhTablesTmp;

        // Loop through all tables in the system
        for (j=1 ; j<= numOfTables; j++)
        {
            tableId                     = dict.tableCnt2Id(j);
            dictTable                   = dict.tableObject(tableId);

            wzhTablesTmp.clear();
            wzhTablesTmp.Id             = tableId;
            wzhTablesTmp.Name           = dictTable.name();
            wzhTablesTmp.IsTmp          = dictTable.isTmp();
            wzhTablesTmp.IsView         = dictTable.isView();
            wzhTablesTmp.PName          = dictTable.label();
            wzhTablesTmp.IsAbstract     = dictTable.isAbstract();
            wzhTablesTmp.IsDataEntity   = dictTable.isDataEntity();
            wzhTablesTmp.IsMap          = dictTable.isMap();
            wzhTablesTmp.IsTempDb       = dictTable.isTempDb();
            wzhTablesTmp.IsSystem       = dictTable.isSystemTable();
            wzhTablesTmp.IsSQL          = dictTable.isSql();

            wzhTablesTmp.insert();
        }

        return wzhTablesTmp;
    }

    public static wzhFieldsTmp populateFields(TableId _tableId)
    {
        Dictionary      dict        = new Dictionary();
        DictTable       dictTable   = dict.tableObject(_tableId);
        int             numOfFields = dictTable.fieldCnt();
        int             j;
        FieldId         fieldId;
        DictField       dictField;
        wzhFieldsTmp    wzhFieldsTmp;

        // Loop through all fields in the table
        for (j=1 ; j<= numOfFields; j++)
        {
            fieldId                 = dictTable.fieldCnt2Id(j);
            dictField               = dictTable.fieldObject(fieldId);

            wzhFieldsTmp.clear();
            wzhFieldsTmp.Id                     = fieldId;
            wzhFieldsTmp.Name                   = dictField.name();
            wzhFieldsTmp.Type                   = dictField.baseType();
            wzhFieldsTmp.EDT                    = extendedTypeId2name(dictField.typeId());
            wzhFieldsTmp.IsVisible              = dictField.visible();
            wzhFieldsTmp.IsMandatory            = dictField.mandatory();
            wzhFieldsTmp.IsAllowEdit            = dictField.allowEdit();
            wzhFieldsTmp.IsAllowEditOnCreate    = dictField.allowEditOnCreate();
            wzhFieldsTmp.EDTPName               = extendedTypeId2Pname(dictField.typeId());
            wzhFieldsTmp.PName                  = dictField.label();
            wzhFieldsTmp.IsSystem               = dictField.isSystem();
            wzhFieldsTmp.IsSQL                  = dictField.isSql();
            wzhFieldsTmp.insert();
        }
        return wzhFieldsTmp;
    }

    public static wzhRelatedTablesTmp populateRelatedTables(TableId _tableId, FieldId _fieldId)
    {
        Dictionary      dict        = new Dictionary();
        int             numOfTables = dict.tableCnt();
        DictTable       dictTable;
        DictRelation    dictRelation;
        TableId         tableId;
        int             i, j;
        int             linesCnt;
        int             relationLine;
        int             c;
        str             relName;
        wzhRelatedTablesTmp wzhRelatedTablesTmp;
        
        // Loop through all tables in the system
        for (j=1 ; j<= numOfTables; j++)
        {
            tableId         = dict.tableCnt2Id(j);
            dictTable       = dict.tableObject(tableId);
            dictRelation    = new DictRelation(tableId);
            
            // Loop through all table relations
            int relCount    = dictTable.relationCnt();

            for (i = 1; i <= relCount; i++)
            {
                relName = dictTable.relation(i);
                dictRelation.loadNameRelation(relName);
                
                if (dictRelation && dictRelation.externTable() == _tableId)
                {
                    linesCnt = dictRelation.lines();
                    for (relationLine=1; relationLine <= linesCnt; relationLine++)
                    {
                        // Check if the relation is with AMDeviceTable.DeviceId
                        if (dictRelation.lineExternTableValue(relationLine) == _fieldId)
                        {
                            c++;
                            wzhRelatedTablesTmp.clear();
                            wzhRelatedTablesTmp.Id                         = c;
                            wzhRelatedTablesTmp.FieldId                    = dictRelation.lineTableValue(relationLine);
                            wzhRelatedTablesTmp.RelatedTableName           = dictTable.name();
                            wzhRelatedTablesTmp.RelatedTablePName          = dictTable.label();
                            wzhRelatedTablesTmp.RelationName               = relName;
                            wzhRelatedTablesTmp.Role                       = dictRelation.Role();
                            wzhRelatedTablesTmp.RelatedTableRole           = dictRelation.RelatedTableRole();
                            wzhRelatedTablesTmp.RelatedTableCardinality    = dictRelation.RelatedTableCardinality();
                            wzhRelatedTablesTmp.IsEDTRelation              = dictRelation.EDTRelation();
                            wzhRelatedTablesTmp.Cardinality                = dictRelation.Cardinality();
                            wzhRelatedTablesTmp.RelationshipType           = dictRelation.relationshipType();
                            wzhRelatedTablesTmp.RelatedFieldName           = fieldId2Name(tableId, wzhRelatedTablesTmp.FieldId);
                            wzhRelatedTablesTmp.insert();
                        }
                    }
                }
            }
        }
        return wzhRelatedTablesTmp;
    }

}

Form

[Form]
public class wzhTables extends FormRun
{
    public void clearFields()
    {
        delete_from wzhFieldsTmp;
        delete_from wzhRelatedTablesTmp;
        wzhFieldsTmp_ds.research();
        wzhRelatedTablesTmp_ds.research();
    }

    public void clearRelatedTables()
    {
        delete_from wzhRelatedTablesTmp;
        wzhRelatedTablesTmp_ds.research();
    }

    public void populateTables()
    {
        wzhTablesTmp.setTmpData(wzhTableTools::populateTables());
        wzhTablesTmp_ds.research();
    }

    public void populateFields()
    {
        wzhFieldsTmp.setTmpData(wzhTableTools::populateFields(wzhTablesTmp.Id));
        wzhFieldsTmp_ds.research();
    }

    public void populateRelatedTables()
    {
        wzhRelatedTablesTmp.setTmpData(wzhTableTools::populateRelatedTables(wzhTablesTmp.Id, wzhFieldsTmp.Id));
        wzhRelatedTablesTmp_ds.research();
    }

    [DataSource]
    class wzhTablesTmp
    {
        public int active()
        {
            int ret;
    
            ret = super();
            element.clearFields();
            element.populateFields();
    
            return ret;
        }

        public void init()
        {
            super();
            element.populateTables();
        }

    }

    [DataSource]
    class wzhFieldsTmp
    {
        public int active()
        {
            int ret;
    
            ret = super();
            element.clearRelatedTables();
            element.populateRelatedTables();
    
            return ret;
        }

    }

}

Find all tables with relations to a given field

 Sometimes we need to know which tables are related to a given table on a given field.

Unfortunately, none of AI generative tools I tried was able to provide me with a working code. So, I wrote this code manually based on Microsoft doc and some articles.

static void findAllTablesWithRelations(TableId _tableId, FieldId _fieldId)
    {
        Dictionary      dict = new Dictionary();
        DictTable       dictTable;
        DictRelation    dictRelation;
        TableId         tableId;
        int             i, j;
        int             linesCnt;
        int             relationLine;
        int             c;
        int             numOfTables = dict.tableCnt();
        boolean         relationFound;

        // Loop through all tables in the system
        for (j=1 ; j<= numOfTables; j++)
        {
            relationFound   = false;
            tableId         = dict.tableCnt2Id(j);
            dictTable       = dict.tableObject(tableId);

// Uncomment the following to get specific table types
            //if( dictTable.isAbstract() || 
            //    dictTable.isDataEntity() || 
            //    dictTable.isMap() || 
            //    dictTable.isTempDb() || 
            //    dictTable.isTmp() || 
            //    dictTable.isView())
            //{
            //    continue;
            //}

            dictRelation    = new DictRelation(tableId);
            str tableName   = dictTable.name();
            
            // Loop through all table relations
            int relCount = dictTable.relationCnt();
            //info(strFmt("Number of relations %1", relCount));

            for (i = 1; i <= relCount; i++)
            {
                str relName     = dictTable.relation(i);
                dictRelation.loadNameRelation(relName);
                
                if (dictRelation && dictRelation.externTable() == _tableId)
                {
                    linesCnt = dictRelation.lines();
                    for (relationLine=1; relationLine <= linesCnt; relationLine++)
                    {
                        // Check if the relation is with AMDeviceTable.DeviceId
                        if (dictRelation.lineExternTableValue(relationLine) == _fieldId)
                        {
                            c++;
                            info(strFmt("%1 : %2 : %3", c, dictTable.name(), fieldId2Name(tableId, dictRelation.lineTableValue(relationLine))));
                            relationFound = true;
                            break;
                        }
                    }
                }
                // just one relation is enough
                if(relationFound)
                {
                    break;
                }
            }
        }
    }

You can replace tableId and fieldId with your own values. For example, for GroupId table:

internal final class GetRelatedTables
{
    public static void main(Args _args)
    {
        TableId tableId = tableNum(SuppItemGroup);
        FieldId fieldId = fieldNum(SuppItemGroup, GroupId);

        info(strFmt("Searching related tables for %1.%2 ", tableId2Name(tableId), fieldId2Name(tableId, fieldId)));
        GetRelatedTables::findAllTablesWithRelations(tableId, fieldId);
        info('Search completed');
    }
}

The output:

Search completed

7 : AMInventItemTemplate : PurchInventSuppItemGroupId

6 : AMInventItemTemplateStaging : PurchInventSuppItemGroupId

5 : InventTableModule : SuppItemGroupId

4 : SuppItemTable : AccountCode

3 : VendTable : SuppItemGroupId

2 : RetailMassUpdateWorksheetLine : Purch_SuppItemGroupId

1 : CustTable : SuppItemGroupId

Searching related tables for SuppItemGroup.GroupId