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

Dual-write setup from Lifecycle Services fails at Application step

If you receive the issue with applying Dual-write for your D365FO environment following the standard procedure, you may need just to change one database parameter.

 Open SQL MS and switch this flip to True and keep default values for your database.

Now you can resume the process.

How to get back Import users button in D365FO

 

 You may start experiencing the following issue with button Import users.

It happens because Microsoft changed their approach to devboxes settings:

As of November 15, 2023, certificates in your Microsoft Entra tenant are no longer installed in new one-box development environments by default. 

You can find more detail on how to install a certificate in this article, but we can use a shortcut using D365fo.tools.

Here are the steps:

    

   1) Go to Azure portal and select app registration and create a new one (name the app to be the same as the environment name & leave settings at default) 

   2) The app registration needs to have the following: 

       a) 2 reply URLs > https://<fno-url> & https://<fno-url>/oauth 

       b) add the following API permissions to the App registration: 

Grant above permissions.

3) On the VM for the environment, open PowerShell 

   4) Run the following commands: 

        a) Install-Module D365fo.tools, and accept all the prompts 

        b) New-D365EntraIntegration -ClientId <client-id> (client-id is the app registration id that was created) 

   5) The above command will save a certificate, which then needs to be uploaded to the app registration that was created 

 

How to fix Admin account after refreshing data base in your dev box

 If you refresh your devbox database from another environment and need to set up its admin account to your user, you can do it by executing the following SQL command.

update USERINFO 
set NAME = 'Admin', 
    NETWORKDOMAIN ='https://sts.windows.net/', 
    NETWORKALIAS = 'a.voytsekhovskiy@mydomain.net', 
    ENABLE = 1, 
    SID = 'S-1-19-XXXXXXXXXX-XXXXXXXXXX-XXXXXXXXXX-XXXXXXXXXX-XXXXXXXXXX-XXXXXXXXXX-XXXXXXXXXX-XXXXXXXXXX-XXXXXXXXXX-XXXXXXXXXX', 
    IDENTITYPROVIDER ='https://sts.windows.net/'
where ID = 'ADMIN'

NB: use your own email address and SID.

Importing a bacpac file to a development box SQL

 Just to memorize some steps to import a database from a bacpac file.

1. Download and run the DacFramework.msi installer for Windows
2. Put the backup.bacpac file close to the SQL database, say, in the root folder on disk G:
3. Run the bat file. 

@echo Importing a bacpac data to SQL database
c:
cd C:\Program Files\Microsoft SQL Server\160\DAC\bin
SqlPackage.exe /a:import /sf:G:\backup.bacpac /tsn:localhost /tdn:AxDB_Restored /p:CommandTimeout=1200 /TargetTrustServerCertificate:True
pause

Take note about the last option: without it importing fails with the following error.