Saturday, December 24, 2011

Manage, Update & Deploy Workflows in AX 2012

I found a very good video describing how we can manage, update and deploy workflows in AX 2012. This demo is demonstrating how workflow is configured and running in Purchase Requisition Process.

Link to video: Manage, Update & Deploy Workflows

Import sales orders from master and details text files

The are many code snippets which you can find easily over the internet to import sales orders from a text file or from excel. The purpose of this post is to share my experience of uploading data into sales order form from two different text files.

void ImportSalesOrders()
    Container                  con;
    CustTable                 custTable;
    SalesTable                salesTable;
    SalesLine                  salesLine;
    Headers                    headers;
    Stock                        details;

    num                           newSalesId;
    Description                custDescription;
    InventTable                inventTable;
    InventDim                  inventDim;
    InventSiteId               inventSiteId;
    InventLocationId         inventLocationId;

    CustAccount             custAccount;
    real                           itemPrice;
    ItemId                       itemId;

    InventDim                  frominventDim,ToinventDim;

    int                             x,y;
    NumberSeq               num;

    SysOperationProgress progressbar = new SysOperationProgress();
    int i;

    progressbar.setCaption('Importing Journals');
    for (i = 1; i <= 30000; i++)
        progressbar.setText(strfmt("@SYS105740", i));
        progressbar.setCount(i, 1);

    while select headers
        SalesTable = SalesTable::findDocumentNumber(headers.DocumentNumber);

            //create sales table

            num = NumberSeq::newGetNum(SalesParameters::numRefSalesId());
            newSalesId = num.num();

            if (salesTable::exist(newSalesId))
                throw error("@SYS23020");

            salesTable.SalesId = newSalesId;

            if(custDescription != "")
                salesTable.SalesName = custDescription;

            custAccount = headers.CustAccount;

            //fetch the customer details from cust table using account no selected from drop-down list
            select * from custTable where custTable.AccountNum == custAccount;

            salesTable.CustAccount = custTable.AccountNum;
            salesTable.InvoiceAccount = custTable.InvoiceAccount;
            salesTable.SalesType = SalesType::Sales;
            salesTable.SalesStatus = SalesStatus::None;
            salesTable.CurrencyCode = custTable.Currency;

            salesTable.CustGroup = custTable.CustGroup;
            salesTable.DeliveryDateControlType = SalesDeliveryDateControlType::SalesLeadTime;

            //Set dates as systemDateTime
            salesTable.ReceiptDateRequested = systemdateget();
            salesTable.ShippingDateRequested = systemdateget();

            salesTable.LanguageId = 'en-us';
            salesTable.DlvMode = '10';

            inventSiteId = headers.InventSiteId;
            inventLocationId = headers.InventSiteId;

            salesTable.InventSiteId = inventSiteId;
            salesTable.InventLocationId = inventLocationId;
            salesTable.DocumentNumber = headers.DocumentNumber;

            //Craete Sales Order

            while select details
            where headers.DocumentNumber == details.DocumentNumber

                // Create Sales Order Line
                salesLine.SalesId = salesTable.SalesId;

                itemId = details.ItemId;

                salesLine.ItemId = itemId;
                salesLine.SalesUnit = InventTable::find(salesLine.ItemId).salesUnitId();

                select * from inventTable where inventTable.ItemId == salesLine.ItemId;

                inventDim.InventSiteId = inventSiteId;
                inventDim.InventLocationId = inventSiteId;

                salesLine.InventDimId = InventDim::findOrCreate(inventDim).inventDimId;

                //Set Qty as 1 by default
                salesLine.SalesQty = details.Qty;
                salesLine.ConfirmedDlv = salesTable.ShippingDateConfirmed;
                salesLine.lineNum = SalesLine::lastLineNum(salesLine.salesId) + 1.0;
                salesLine.RemainInventPhysical = 1;
                salesLine.RemainSalesPhysical = 1;
                salesLine.DlvMode = '10';
                salesLine.SalesStatus = SalesStatus::None;
                salesLine.SalesPrice = details.UnitPrice;
                salesLine.LineAmount = salesLine.SalesQty * salesLine.SalesPrice;

                //Insert sales line items
    info(strfmt("%1 Header(s) imported with their details", x));

void importData()
    Container               con;
    // I created these two table headers and details to make the import process fast I had huge data files to import
    Headers                 headers;
    Stock                   details;

    TextBuffer              tbHeader = new TextBuffer();
    TextBuffer              tbDetails = new TextBuffer();

    int                     cntHeader, cntDetails;
    int                     numLinesHeader, numLinesDetails;
    container               inLineHeader, inLineDetails;

    int i;


    numLinesHeader = tbHeader.numLines();
    numLinesDetails = tbDetails.numLines();

    delete_from headers;
    delete_from details;

        inLineHeader = str2Con(tbHeader.nextToken(true));

        for(cntHeader = 1; cntHeader < numLinesHeader; ++cntHeader)
            inLineHeader = str2Con(tbHeader.nextToken(true));

            headers.DocumentNumber = conpeek(inLineHeader, 1);
            headers.CustAccount = conpeek(inLineHeader, 2);

        inLineDetails = str2Con(tbDetails.nextToken(true));

        for(cntDetails = 1; cntDetails < numLinesDetails; ++cntDetails)
            inLineDetails = str2Con(tbDetails.nextToken(true));

            details.DocumentNumber = conpeek(inLineDetails, 1);
            details.ItemId = conpeek(inLineDetails, 2);
            details.Qty = conpeek(inLineDetails, 3);
            details.UnitPrice = conpeek(inLineDetails, 4);
            details.Discount = conpeek(inLineDetails, 5);
            details.UnitDiscount = conpeek(inLineDetails, 6);


Thursday, December 22, 2011

Dynamics AX and database synchronization error

Due to huge customization in application we may come across database and application synchronization failure issue. Sometimes you will see this error when you add some new fields in CustTable or SalesTable, this is just for an example you can have this issue in any table or view.

Following job will forcefully synchronize all the tables in AOT.

static void forceDbSynchronize(Args _args)
    Dictionary                             dict;
    int                                        idx, lastIdx, totalTables;
    TableId                                  tableId;
    Application                            application;
    SysOperationProgress           progress;
    StackBase                            errorStack;
    ErrorTxt                                 errorTxt;

    application = new Application();
    dict = new Dictionary();
    totalTables = dict.tableCnt();
    progress = new SysOperationProgress();
    errorStack = new StackBase(Types::String);

    lastIdx = 0;
        for (idx = lastIdx+1; idx <= totalTables; idx++)
            tableId = dict.tableCnt2Id(idx);

            lastIdx = idx;
            application.dbSynchronize(tableId, false, true, false);
    catch (Exception::Error)
        errorTxt = strFmt("Error in table '%1' (%2)", tableId, dict.tableName(tableId));

    errorTxt = errorStack.pop();
    while (errorTxt)
        errorTxt = errorStack.pop();
    info('Sychrnonization is now done.');

Delete duplicate records from table

Sometimes we lost unique identity check in the tables while doing some customization in Dynamics AX. It may happen due to failure of database synchronization.

Here is the code to remove the duplicate records from a table, I used Dimension table as an example;

static void deleteduplicate(Args _args)
    set fieldSet = new set(Types::Integer);

    // create dicindex from the unique index
    DictIndex dictIndex = new Dictindex(tablenum(Dimensions),indexnum(dimensions, DimensionIdx));

    // these are the fields from the index
    // add them to a set
    fieldset.add(fieldnum(Dimensions, DimensionCode));
    fieldset.add(fieldnum(Dimensions, Num));

    // set allow duplicates

    // delete duplicate records
    ReleaseUpdateDB::deleteDuplicatesUsingIds(tablenum(Dimensions), 1, fieldset);

    // re-enable index


Format the system date in Dynamics AX

The following code show the dates in format like December 22, 2011

static void datesJob(Args _args)
    str dd, mm, yy, dt;

    dt = date2Str(systemDateGet(), 123, DateDay::Digits2, DateSeparator::Slash, DateMonth::Digits2,
           DateSeparator::Slash, DateYear::Digits4);

    dd = substr(dt, 0, 2);
    mm = substr(dt, 4, 2);
    yy = substr(dt, 7, 4);

    dt = mthname(str2int(mm)) + ' ' + dd + ', ' + yy;

    print   dt;

Monday, December 19, 2011

Dynamics AX – Use definition groups to import data

It’s perfectly correct that we always learn when we want to or when we are in need of achieving goals. This happened to me for last couple of weeks when I was looking for different ways to import data in Dynamics AX 2009. I came to know that we can use data definition groups under Administration > Periodic > Data export/import > Definition groups to import data from excel or csv files.

Here you can find the complete information to import data using data definition group.

Any questions and comments will highly be appreciated!

How to get the filtered datasource query after pressing Ctrl +G

Question: How to get filtered query after using filter by grid option in grid

 Grid showing filter records                                           Grid showing complete list of records
Answer: You can get the query which is created after providing filter criteria like this;