Solution: adapt the run method of your batch class

Let’s take as example a periodic job to clean up the sales update history in Dynamics AX (Accounts receivable > Periodic > Clean up > Sales update history clean up). You can enter a Created until date to specify until which date the history should be deleted. Suppose you want to run this as a daily batch job at night, which should delete all history until today. Entering a date value of today and adding the job to the batch list wouldn’t achieve what you want. Tonight, it would delete all history until today, but tomorrow, all history would be deleted until the day before, since your Created until date would still be the same in the job in your batch list.

To solve this add following code in the run method of the involved class (in our example SalesParmCleanUp)

void run() 
{
    #OCCRetryCount 
   
    // if class is run in batch mode, set the 
    // createdDate dialog field equal to todays date
    if(this.isInBatch())     
    {
        createdDate = SystemDateGet(); 
    } 
    
    try   
    { 

        ttsbegin; 
  
        this.deleteSalesParmTables();
  
        ttscommit;     
    }

    …
}

Solution: use the exclamation mark

Suppose you want to find all customers in the CustTable whose Name doesn’t start with Light. As the not like condition is not supported in X++, you need to achieve this by using the exclamation mark. The exclamation mark is used throughout Dynamics AX to filter records not equal some value. In our example this would become:

static void UseOfNotLike(Args _args)
{
    CustTable           custTable;
    ;
    
    while select custTable
    where !(custTable.Name like "Light*")
    {
        info(strFmt("Customer account: %1, Name: %2"
                    ,custTable.AccountNum
                    ,custTable.Name));
    }
}

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