Windows 8 | Microsoft Dynamics AX Applications

Read/Write data using Excel

static void Write2ExcelFile(Args _args)

{ 

    InventTable             inventTable;

    SysExcelApplication     application; 

    SysExcelWorkbooks       workbooks;

    SysExcelWorkbook        workbook;

    SysExcelWorksheets      worksheets;

    SysExcelWorksheet       worksheet;

    SysExcelCells           cells;

    SysExcelCell            cell;

    int                     row;

    ;

    application         = SysExcelApplication::construct();

    workbooks           = application.workbooks();

    workbook            = workbooks.add();

    worksheets          = workbook.worksheets();

    worksheet           = worksheets.itemFromNum(1);

    cells               = worksheet.cells();

    cells.range('A:A').numberFormat('@');

    cell                = cells.item(1,1);

    cell.value("Item");

    cell                = cells.item(1,2);

    cell.value("Name");

    row                 = 1;

    

    while select inventTable

    {

        row++;

        cell = cells.item(row, 1);

        cell.value(inventTable.ItemId);

        cell = cells.item(row, 2);

        cell.value(inventTable.ItemName);

    }

    

    application.visible(true);

}

Reading Data from Excel File

static void ReadExcel(Args _args)

{ 

    SysExcelApplication     application;

    SysExcelWorkbooks       workbooks;

    SysExcelWorkbook        workbook;

    SysExcelWorksheets      worksheets;

    SysExcelWorksheet       worksheet;

    SysExcelCells           cells;

    COMVariantType          type;

    int                     row;

    ItemId                  itemid;

    Name                    name;

    FileName                filename;

    ;

    application             = SysExcelApplication::construct();

    workbooks               = application.workbooks();

    //specify the file path that you want to read

    filename                = "C:\\item.xls";

    try

    {

        workbooks.open(filename);

    }

    catch (Exception::Error)

    {

        throw error("File cannot be opened.");

    }

    workbook        = workbooks.item(1);

    worksheets      = workbook.worksheets();

    worksheet       = worksheets.itemFromNum(1);

    cells           = worksheet.cells();

    

    do

    {

        row++;

        itemId = cells.item(row, 1).value().bStr();

        name = cells.item(row, 2).value().bStr();

        info(strfmt('%1 - %2', itemId, name));

        type = cells.item(row+1, 1).value().variantType();

    }

    while (type != COMVariantType::VT_EMPTY);

        application.quit();

}

AIF run manually using job

static void processAIF(Args _args)
{
    ;
    // read the messages
    new AifGateWayReceiveService().run();

    // process the messages in queue
    new AifInboundProcessingService().run();

    // process messages in queue
    new AifOutboundProcessingService().run();

    // send messages
    new AifGateWaySendService().run();
}

101 Dynamics AX Interview Questions

101 Microsoft Dynamics AX Interview Questions

Mukesh Hirwani

Follow me @

BlogSpot: http://mukesh-ax.blogspot.in/

Microsoft Community: http://community.dynamics.com/product/ax/axtechnical/b/mukeshhirwani_dynamicsax/default.aspx

Download your copy from here:

https://skydrive.live.com/redir?resid=DED394F0A5DBC52D!250&authkey=!AHZjNaRsY2K8FWg

Update Customer/Vendor dimensions | AX 2012

Upload using csv which contains columns customer account number and all active dimension, if particular dimension is not applicable for a customer/vendor then it has to be left as blank.

Read full post at : http://dynamicsnavax.blogspot.com.au/2012/07/import-default-dimension-into-table-eg.html

static void ImportCustomerDimension(Args _args)
{
    AsciiIO     asciiIO;
    Filename    filename;
    NoYesId     skipFirstLine;
    Container   line;
    Dialog      dialog;
    DialogField dialogFileName, dialogSkipFirstLine;
    DimensionAttribute              dimAttr;
    DimensionAttributeSetItem       dimAttrSetItem;
    DimensionEnumeration            dimensionSetId;
    Container                       combinedContainer;
    CustTable                       custTable;
    CustAccount                     custAccount;
    Counter                         counter;
    DimensionAttributeValue         dimAttributeValue;
    //Dialog
    dialog                  = new Dialog("Import Default Dimension");
    dialogFileName          = dialog.addField(extendedTypeStr(Filenameopen), "File name");
    dialogSkipFirstLine     = dialog.addField(extendedTypeStr(NoYesId), "Skip first line");

    if (dialog.run())
    {
        filename      = dialogFileName.value();
        skipFirstLine = dialogSkipFirstLine.value();
    }
    asciiIO = new AsciiIO(filename, 'R');
    if (!asciiIO || asciiIO.status() != IO_Status::Ok )
    {
        throw error (strfmt("@SYS19312",filename));
    }

    asciiIO.inRecordDelimiter('\r\n');
    asciiIO.inFieldDelimiter(',');
    if (skipFirstLine)
        line = asciiIO.read();
    while (asciiIO.status() == IO_status::Ok)
    {
        line = asciiIO.read();
        if (line)
        {
            //get customer account number
            custAccount = conpeek(line,1);
            counter = 1; //Dimension starting point
            combinedContainer = conNull();
            dimAttributeValue = null;
            
            //Build dimension container
            dimensionSetId = DimensionCache::getDimensionAttributeSetForLedger();
            while select dimAttr
                order by Name
                    where dimAttr.Type != DimensionAttributeType::MainAccount
                join RecId from dimAttrSetItem
                    where dimAttrSetItem.DimensionAttribute == dimAttr.RecId &&
                          dimAttrSetItem.DimensionAttributeSet == dimensionSetId
            {
                counter++;
                //only insert into container if it has a value
                if (conPeek(line, counter))
                {
                    combinedContainer += [dimAttr.Name, conPeek(line, counter)];
                    //info(strFmt("%1 %2",dimAttr.Name, conPeek(line, counter)));
                    dimAttributeValue = AxdDimensionUtil::validateFinancialDimensionValue(dimAttr, conPeek(line, counter));
                }
            }
            //insert the count in the first value of the container - number of dimensions divided by 2 - since we have name and value
            combinedContainer = conIns(combinedContainer, 1, int2str(conLen(combinedContainer)/2));
            ttsBegin;
            custTable = CustTable::find(custAccount, true);
            custTable.DefaultDimension = AxdDimensionUtil::getDimensionAttributeValueSetId(combinedContainer);
            custTable.update();
            ttsCommit;
        }
    }
}

Delete transactional data | AX 2012

void handleTable(SysDictTable sysDictTable)
{
    TableGroup      tableGroup;

    if (tableSet.in(sysDictTable.id()))
        return;
    tableSet.add(sysDictTable.id());

    if (sysDictTable && !sysDictTable.isTmp() && !sysDictTable.isMap())
    {
        tableGroup = sysDictTable.tableGroup();

        // Handle company specific tables to be deleted
        if (sysDictTable.dataPrCompany())
        {
            switch(tableGroup)
            {
                case TableGroup::Transaction:
                case TableGroup::WorksheetHeader:
                case TableGroup::WorksheetLine:
                //FIX - Support new AX2012 transaction table types
                case TableGroup::TransactionHeader:
                case TableGroup::TransactionLine:
                    this.handleTransTable(sysDictTable);
                    break;
                default:
                    this.handleNonTransTable(sysDictTable);
                    break;
            }
        }
        else
        {
            // Handle global tables to be deleted
            switch(tableGroup)
            {
                case TableGroup::Transaction:
                case TableGroup::WorksheetHeader:
                case TableGroup::WorksheetLine:
                //FIX - Support new AX2012 transaction table types
                case TableGroup::TransactionHeader:
                case TableGroup::TransactionLine:
                    this.handleGlobalTransTable(sysDictTable);
                    break;
                default:
                    break;
            }
        }
    }
}

Dynamics Ax - Update using AIF | Get Document Hash

Generate <_DocumentHash> while updating data thru AIF

In Dynamics Ax, version 2009 onwards Document hash is mandatory to update the records using AIF.

When you read data, AIF returns a document hash field. This field contains a hash of all the RecId and RecVersion values for each record that is returned. When you send the data back into AIF to update a record, it recalculates the document hash from the database records in the update and compares it to the document hash in the inbound message. If the data has changed, for example, if a record was updated or added, the calculated document hash will differ from the document hash in the inbound document and AIF will return an error.

When you use the document hash, you only have to read and submit one field for comparison instead of reading and submitting the RecId and RecVersion values for each record. However, if a concurrency error occurs when you use the document hash, AIF can only report that a record was changed and not which record changed.

While implementing some of the scenarios we may even need to perform update in 1 step this could be only possible when are able to generate <_DocumentHash> using X++. Below code describes same.
I have created a test class which extends to AxdBaseRecordInfo and have created one method. In the method I am passing a recId for LedgerJournalTrans and it should return documenthash.

class MyHashCode  extends AxdBaseRecordInfo

{

}

public static str getHashCode(RecId     _recId)

{

    AxdBaseUpdate           axdBaseUpdate = AxdbaseUpdate::construct();

    Map                          dataSourceMap;

    Query                       query = new Query(QueryStr(AxdLedgerGeneralJournal));

    AxdBaseRecordInfo      topAxdBaseRecordInfo;

    QueryRun                  _queryRun;

    str                           documentHash;

    QueryBuildRange         qr;

    LedgerJournalTrans     ledgerJournalTrans;

    RecId                        recId;

    ;

    recId                       = _recId;

    

    select firstOnly ledgerJournalTrans

        where ledgerJournalTrans.RecId == recId;

    dataSourceMap               = new Map(Types::Integer,Types::Integer);

    

    axdbaseUpdate.buildDataSourceParentMap(dataSourceMap, query.dataSourceNo(1)) ;

    query.dataSourceNo(1).addRange(fieldnum(LedgerJournalTrans, RecId)).value(queryValue(recId));

    topAxdBaseRecordInfo        = new AxdBaseRecordInfo(ledgerJournalTrans,1,dataSourceMap);

    documentHash                = topAxdBaseRecordInfo.getRecordHash();

    return documentHash;

}

Job

static void generateDocHash(Args _args)

{

    ;

    info(MyHashCode::getHashCode(5637158049));

}

Above code has been designed to support AX 2012 changes, if you are looking how to perform same in AX 2009 then follow :
http://community.dynamics.com/product/ax/axtechnical/b/axsantoshkumar/archive/2008/07/14/dynamics-ax-update-using-aif-to-get-the-document-hash.aspx

Happy DAXing :)