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;
    }

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

Dangerous bug in CustCollectionsPoolsListPage form AX2012/D365

There is a form CustCollectionsPoolsListPage where two data sources are outer joined to the root data source with no relations (no links).

If by any reason the initial query does not contain links for these two aforementioned, SQL starts producing a Cartesian product and generating a huge temporary table. The latter can potentially lead to SQL server crash, like it happened in our environment.

The following fix may leave much to be desired but at least it creates needed links in case they are absent.

On CustTable data source we have to add an additional check for the existing query.

public void executeQuery()
{
    element.populateAgingIndicators(selectedCustAging);

    // Use the query from the cue?
    if (!useInitialQuery 
                        // Begin
                        || !this.wblCheckQuery(this.query())
                        // End: 
                        )
    {
        this.query(element.addOriginalPoolQuery(listPageHelper.getCurrentPoolQuery()));
    }


    super();

    element.setButtonAccess();
    element.setGridColumnLabels();
}

// to avoid the cartesian product in case of absent link for this outer join
private boolean wblCheckQuery(Query _query)
{
    QueryBuildDataSource custAgingDs;
    QueryBuildDataSource custAgingLegalEntityDs;
    boolean ret = true;

    custAgingDs             = _query.dataSourceName(#CustAgingDsName);
    custAgingLegalEntityDs  = _query.dataSourceName(#CustAgingLegalEntityName);
    if (!custAgingDs || !custAgingLegalEntityDs || custAgingDs.linkCount() <= 0 || custAgingLegalEntityDs.linkCount() <= 0)
    {
        ret = checkFailed("Saved query is corrupted. Try to recreate the cue");
    }
    return ret;
}

From SQL perspective we can catch such an issue by the following query.

use tempdb
select * from sys.dm_db_session_space_usage spu
join sys.dm_exec_sessions s on s.session_id = spu.session_id
join sys.dm_exec_requests r on s.session_id = r.session_id
cross apply sys.dm_exec_sql_text(sql_handle) t 
order by internal_objects_alloc_page_count desc

How to get all related table ids from code

We can loop all relations on a table by code.

static void myGetRelatedTableNames(Args _args)
{
    myInExtCodeValueTable           myInExtCodeValueTable;
    int                             mapId;
    TableName                       relatedTableName;
    TableId                         relatedTableId;
    Set                             tablesIdsSet    = new Set(Types::Integer);
    Set                             tablesNamesSet  = new Set(Types::String);
    TableId                         tableId         = tableName2id(tableStr(myInExtCodeValueTable));
    Dictionary                      dictionary      = new Dictionary();
    SysDictTable                    dictTable       = dictionary.tableObject(tableId);
    DictRelation                    dictRelation    = new DictRelation(myInExtCodeValueTable.TableId);
    int                             mapCnt          = dictTable.relationCnt();
    container                       ret ;            
    str                             relationName;
    //create a maps of literals for all tables from the table relations
    // so that we could get tables names based on their ids
    // and if any new relation will be added to multiple external codes table
    // it is present automatically in this view
    for (mapId=1; mapId <= mapCnt; mapId++)
    {
        // elaborate if any table present many times
        relationName        = dictTable.relation(mapId);
        dictRelation.loadNameRelation(relationName);
        if(dictRelation)
        {
            relatedTableId      = dictRelation.externTable();
            relatedTableName    = tableId2pname(relatedTableId);
            tablesIdsSet.add(relatedTableId);
            tablesNamesSet.add(relatedTableName);
            info(strFmt("Table %1 - %2", relatedTableId, relatedTableName));
        }
    }
        
    ret = [tablesIdsSet.pack(), tablesNamesSet.pack()];
}

It can be useful in cases when we need, say, to open a form with a related record.

Here you can find a more elaborated example.