My 2p about ERP Solutions, Information Worker Solutions and other software products (mainly Microsoft Dynamics AX and Microsoft SharePoint).

30 November 2009

Links List November 2009

I decided to share all my interesting reads and resources month by month with my blog readers. You can find these posts by searching on the label Links. I'll try to order the resources in logical categories. If you would like to see some interesting stuff added in the next month, don't hesitate to post a comment.

So this is my Links post for November 2009.

Dynamics AX

  • User Controls Overview: User Controls are the primary method for adding new functionality to Enterprise Portal. User Controls are developed in Visual Studio. They are added to Enterprise Portal pages by the Dynamics User Control Web part. User Controls and the ASP.NET-based framework are a replacement for the Web Forms and Web Controls that were the primary method for adding functionality in earlier releases of Enterprise Portal. Web Forms and Web Controls are still supported in this version of Enterprise Portal. However, we recommend that any new development for Enterprise Portal be done with User Controls and the Visual Studio framework.
  • The X++ based Web UI Framework in Enterprise Portal will be Discontinued in Future Releases of Microsoft Dynamics AX
  • SysDictCoder: blog about Dynamics AX stuff.
  • Belgian Dynamics Community: Almost 3000 companies and organizations in Belgium are using Microsoft Dynamics-based solutions every day. More than 700 people in ICT companies are daily involved in consultancy, project management or software development around the Microsoft Dynamics platform. In this important ecosystem, the Belgian Dynamics Community (BDC), created in 2008, plays an important role as the largest independent community for Microsoft Dynamics in Belgium. The activities of BDC cover three major product lines: Microsoft Dynamics NAV, AX and CRM, but also includes solutions and products that surround the Microsoft Dynamics platform: Business  Intelligence, SharePoint, Mobile solutions,…
  • Supply Chain Management in Dynamics AX: Arrival overview: In Microsoft Dynamics AX 2009, the Arrival overview form is introduced to improve the overview of items expected to arrive as incoming goods. The new feature provides an overview of all expected incoming items and arrivals can be initialized based on the overview. The focus of this blog post is the functionality of the Arrival overview form and the receiving process.


Continue reading......

by Patrik Luca 0 comments

15 November 2009

Run a batch job with as dialog field value today

Business requirement: run a batch job with as dialog field value today

I you want to run a batch job daily, which has a date dialog field as input, you often don’t want the value in the date dialog to remain the same (being the value entered upon creation of the batch job), but to be equal to today (it is the day on which the batch job is run). However, you cannot enter a variable value in the dialog upon adding the batch job to the batch list in Dynamics AX.

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


}


Continue reading......

by Patrik Luca 3 comments

14 November 2009

Not like syntax in X++

Business Requirement: use of not like functionality in X++

The like condition allows you to use wildcards in where clauses of an SQL statement. The not like condition as supported in SQL is not supported in X++.

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


Continue reading......

by Patrik Luca 0 comments

02 November 2009

Find all relations on a Dynamics AX table object

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


Continue reading......

by Patrik Luca 2 comments

Patrik Luca, Ieper, BELGIUM
Feel free to use or spread all of the content on my blog. In return, linking back to my blog would be greatly appreciated. All my posts and articles are provided "AS IS" with no warranties.

Subscribe feeds via e-mail
Subscribe in your preferred RSS reader

Subscribe feeds rss Most Read Entries

Categories

Recommended Books


Subscribe feeds rss Recent Comments

This Blog is part of the U Comment I Follow movement in blogosphere. Means the comment field of this blog is made DOFOLLOW. Spam wont be tolerated.

Blog Archive

My Blog List

Followers

Links