Find all relations on a Dynamics AX table object ~ IT Pro Ramblings

Find all relations on a Dynamics AX table object

by Patrik Luca 2 comments

Business requirement: find all relations defined on a Dynamics AX table object

In Dynamics AX, relations can be defined on a table object to connect tables with each other: it lets MorphX know how the data model looks like. Next to relations defined on the table itself, it is also possible that a relation on an Extended data type connects tables in Dynamics AX.

The class below reads the data dictionary and creates a csv file with all relations defined on the table or defined on extended data types existing for that table. As such you get an overview of the metadata of the AX data dictionary. You can also run the script for all tables in your data model of your Dynamics AX application. To do so, don’t specify a table when starting the class.

Solution: run class which creates a csv file with all relations for a given table

The next parts describe in detail all needed X++ methods in the class.

classDeclaration

class TableRelationsExport extends RunBaseBatch 
{

    FilePath                        outputFilePath;  
    FileName                        outputFileName;

    TableName                       tableName;
    AsciiIo                         diskFile;

    container                       filecon; 
    DialogRunBase                   dialog; 
    DialogField                     dialogFilePath; 
    DialogField                     dialogFileName; 
    DialogField                     dialogTableName;
    Dictionary                      dictionary; 
    SysDictTable                    dictTable; 
    SysDictRelation                 dictRelation; 
    SysDictField                    dictField; 
    SysDictType                     dictType;
    int                             currentTableId; 
    TableName                       currentTable; 
    int                             nbrOfRecords; 
    TableName                       relatedTable; 
    str                             relationOn; 
    str                             strRelation;
    Counter                         progressCounter;
    #DEFINE.CurrentVersion(3) 
    #LOCALMACRO.CurrentList 
        outputFilePath, 
        outputFileName, 
        tableName 
    #ENDMACRO 
}

Method addFileName

This method is used to store the values of the file and path entered in the dialog in variables.

void addFileName()
{
    ; 
    dialogFilePath                  
        = dialog.addFieldValue(typeid(FilePath)
                               ,this.outputFilePath());
    dialogFileName                  
        = dialog.addFieldValue(typeid(FilenameSave)
                               ,this.outputFileName()
                               ,"@SYS53125"); 

    dialogFileName.allowEdit(false);
}

Method addTableName

This method is used to store the value of the tableName entered in the dialog in a variable.

void addTableName()
{
    ; 
    dialogTableName                 
        = dialog.addFieldValue(typeid(TableName)
                               ,this.tableName()
                               ,"@SYS11906");
}

Method defineFile

This method is used to write the output to an ASCII file. The field delimiter and record delimiter are set with this method.

void defineFile()
{
    ; 
    DiskFile = new AsciiIo(outputFileName,'W'); 
    if (!diskFile)
        throw error("@SYS26757"); 
    diskFile.outRecordDelimiter('\r\n');
    diskFile.outFieldDelimiter(';');
}

Method dialog

This method creates the dialog which will allow entering a value for the path where the output file should be saved. Also a specific table can be entered for which you want to look up all relations, or if left empty, relations will be shown for all tables in your Dynamics AX application.

Object dialog()
{
    DialogGroup             dialogGeneral;
    DialogGroup             dialogTable;
    ; 
    dialog = super(); 
    dialog.allowUpdateOnSelectCtrl(true); 
    dialog.caption(strFmt("Create file with 
                           table relations"));
    dialogGeneral   = dialog.addGroup("@SYS19924"); 

    this.addFileName(); 
    dialogTable = dialog.addGroup("@SYS11906"); 
    this.addTableName(); 
    return dialog;
}

Method dialogSelectCtrl

This method sets the output file name.

public void dialogSelectCtrl()
{
    ; 
    dialogFileName.value(fileNameTrim(dialogFilePath.value()) 
                                      + \\Tablerelations_ 
                                      + curext() 
                                      + ".csv");
}

Method getFromDialog

GetFromDialog() is called if the button OK is pressed in the dialog.

public boolean getFromDialog()
{
    boolean ok          = true;
    ; 
    outputFilePath = fileNameTrim(dialogFilePath.value());
    outputFileName = fileNameTrim(dialogFileName.value());
    tableName = dialogTableName.value(); 
    return ok;
}

Method getLast

public void getLast()
{
    super(); 
    if (!WinAPI::showMruAllowed())
    {
        outputFilePath = '';
        outputFileName = '';
    }
}

Method initDictField

This method initializes a dictField object for non system fields.

void initDictField(tableId _tableId, fieldId _fieldId)
{
    ; 
    dictField = new DictField(_tableId, _fieldId);
    // only for non system fields
    if (!dictField.isSystem())
    {
        dictType = new DictType(dictField.typeId());
    }
    else
    {
        dictType = null;
    }
}

Method initDictionary

This method initializes a dictionary and dictTable object.

void initDictionary()
{
    ; 
    dictionary = new Dictionary();
    dictTable = dictionary.tableObject(
               dictionary.tableNext(0));
}

Method initDictRelation

This method initializes a dictRelation object. Could be a relation on a table or one on an Extended Data Type.

void initDictRelation(tableId _tableId = -9999)
{
    ; 
    if (_tableId != -9999)
    {
        dictRelation    = new DictRelation(_tableId);
    }
    else
    {
        dictRelation = dictType.relationObject();
    }
}

Method outputFileName

Filename outputFileName(Filename _outputFileName 
                                = outputFileName)
{
    outputFileName = _outputFileName;
    return outputFileName;
}

Method outputFilePath

Filename outputFilePath(Filepath _outputFilePath 
                                = outputFilePath)
{
    outputFilePath = _outputFilePath;
    return outputFilePath;
}

Method pack

public container pack()
{
    return [#CurrentVersion, #CurrentList];
}

Method printDictRelations

This method will write the relations defined on a table if the input parameter is Yes, else it will write the relations defined on an Extended Data Type.

void printDictRelations(NoYes _onTable)
{
    int                 relationId;
    int                 relationLine;
    int                 relationCnt;
    int                 linesCnt;
    str                 str1, str2, str3;
    ; 
    // get relations on table defined
    if (_onTable == NoYes::Yes)
    {
        relationCnt = dictTable.relationCnt();
    }
    else
    {
        // only one relation on EDT
        relationCnt = 1;
    }
    for (relationId=1; relationId <= relationCnt; 
         relationId++)
    {
        strRelation = ""; 

        // print relations
        if (_onTable == NoYes::Yes)
        {
            dictRelation.loadNameRelation(
                dictTable.relation(relationId));
            linesCnt = dictRelation.lines();
        }
        else
        {
            linesCnt = dictRelation.lines();
        } 

        if (_onTable == NoYes::Yes)
        {
            relationOn = "relation on table";
            relatedTable 
                = tableid2name(dictRelation.externTable());
        }
        else
        {
            relationOn = "relation on EDT";
            relatedTable 
                = tableid2name(dictRelation.table());
        } 

        for (relationLine=1; relationLine <= linesCnt;
             relationLine++)
        {
            if (_onTable == NoYes::Yes)
            {
                str3 = strRelation ? " && " : "";
                strRelation 
                    = strRelation
                    + str3
                    + dictTable.name()
                    + "."
                    + fieldid2name(dictTable.id()
                          ,dictRelation.lineTableValue(
                              relationLine)
                                  )
                    + "=="
                    + tableid2name(dictRelation.externTable())
                    + "."
                    + fieldid2name(dictRelation.externTable()
                          ,dictRelation.lineExternTableValue(
                              relationLine)
                                  );
            }
            else
            {
                //For FIELD and THISFIXED 
                //dictRelation.lineExternTableValue(i) 
                //will give you the related field
                str1 = dictRelation.lineType(relationLine) 
                           == TableRelation::ExternFixed ? "" 
                           : dictTable.name();
                str2 = dictRelation.lineType(relationLine) 
                           == TableRelation::ExternFixed 
                           ? int2str(
                                 dictRelation.lineTableValue(
                                     relationline)
                                    ) 
                           : "." + dictField.name();
                str3 = strRelation ? " && " : "";
                strRelation 
                    = strRelation
                      + str3
                      + str1
                      + str2
                      + "=="
                      + tableid2name(dictRelation.table())
                      + "."
                      + fieldid2name(dictRelation.table()
                          ,dictRelation.lineExternTableValue(
                               relationLine));
            }
        }
        this.printRecord();
    }
}

Method printRecord

void printRecord()
{
    ; 
    filecon = connull();
    filecon = conins(filecon, conlen(filecon)+1, 
        currentTableId);
    filecon = conins(filecon, conlen(filecon)+1,
        currentTable);
    filecon = conins(filecon, conlen(filecon)+1,
        nbrOfRecords);
    filecon = conins(filecon, conlen(filecon)+1, 
        relatedTable);
    filecon = conins(filecon, conlen(filecon)+1, 
        relationOn);
    filecon = conins(filecon, conlen(filecon)+1, 
        strRelation);
    diskFile.write(filecon);
}

Method printTableInformation

This method will write the tableName and how many records exist for the table to the output file.

void printTableInformation(dictTable _dictTable)
{
    Common                  common;
    ; 
    common = _dictTable.makeRecord();
    select count(recId) from common; 
    currentTableId = _dictTable.id();
    currentTable = _dictTable.name();
    nbrOfRecords = common.RecId;
}

Method progressUpdate

protected void progressUpdate(str _text, int _progressCounter)
{
    ; 
    progressCounter = _progressCounter;
    progress.setCount(progressCounter);
    progress.setText(_text);
}

Method run

void run()
{
    #macrolib.AviFiles 
    int                         i; 
    ; 
    this.initDictionary();
    this.defineFile(); 
    // print headers
    filecon = connull();
    filecon = conins(filecon, conlen(filecon)+1, 
                         "Id Current Table");
    filecon = conins(filecon, conlen(filecon)+1, 
                         "Current Table");
    filecon = conins(filecon, conlen(filecon)+1, 
                         "Number of Records");
    filecon = conins(filecon, conlen(filecon)+1, 
                         "Related Table");
    filecon = conins(filecon, conlen(filecon)+1, 
                         "Relation on");
    filecon = conins(filecon, conlen(filecon)+1, 
                         "Relation");
    diskFile.write(filecon); 

    this.progressInit(
        strFmt("@SYS14164",outPutFileName)
        ,dictionary.tableCnt(),#Avifilecopy); 
    while (dictTable && dictTable.id())
    {
        // output asked for a specific table
        if (tableName)
        {
            if(dictTable.id() != tableName2id(tableName))
            {
                // get next table
                dictTable 
                    = dictionary.tableObject(
                        dictionary.tableNext(dictTable.id()));
                continue;
            }
        } 

        // don't check relations on system tables 
        // nor on views nor on temporary tables
        if (dictTable.isSystemTable()
            || dictTable.isView()
            || dictTable.isTmp())
        {
            // get next table
            dictTable = dictionary.tableObject(
                dictionary.tableNext(dictTable.id()));
            continue;
        }
        // print table headers
        this.printTableInformation(dictTable);
        // print related table headers and relations on it
        this.initDictRelation(dictTable.id());
        if (dictRelation)
        {
            this.printDictRelations(NoYes::Yes);
        } 

        // loop over fields searching for EDT 
        // and print relations on it
        for (i=1; i<=dictTable.fieldCnt(); i++)
        {
            dictType = null;
            this.initDictField(dictTable.id(), 
                                   dictTable.fieldCnt2Id(i));
            if (dictType)
            {
                this.initDictRelation();
                if (dictRelation)
                {
                    this.printDictRelations(NoYes::No);
                }
            }
        }
        // get next table
        if (!tableName)
        {
            dictTable 
                = dictionary.tableObject(
                      dictionary.tableNext(dictTable.id()));
        }
        else
        {
            break;
        }
        this.progressUpdate(
            strFmt("@SYS56445",dictTable.name())
            ,dictTable.id());
    }
}

Method tableName

TableName tableName(TableName _tableName = tableName)
{
    return tableName;
}

Method unpack

public boolean unpack(container packedClass)
{
    boolean             ret;
    integer             version = conpeek(packedClass,1);
    ; 
    switch(version)
    {
        case #CurrentVersion:
            [version, #CurrentList] = packedClass;
            break;
        default:
            ret = false;
    }
    return ret;
}

Method validate

public boolean validate()
{
    boolean ret; 
    ret = super(); 
    if (!outputFilePath)
        ret = checkFailed("@SYS72246");
    if (!outputFileName)
        ret = checkFailed("@SYS18624"); 
    return ret;
}

Method description

client server static ClassDescription description()
{
    return "Make file with table relations";
}

Method main

static void main(Args _args)
{
    TableRelationsExport     tableRelationsExport 
                                 = new TableRelationsExport();
    ;     tableRelationsExport.getLast();
    if (tableRelationsExport.prompt())
    {
        tableRelationsExport.run();
    }
}

02 November 2009