web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

Community site session details

Community site session details

Session Id :

X++ job to export data using custom delimiter using commaIO in Ax2012

Chaitanya Golla Profile Picture Chaitanya Golla 17,225

Hi,

Following job when executed exports open sales order data(based on AOT query - SalesUpdate) from AX with pipe "|" as delimiter and also provides the record count along with time consumed for the process to complete.

static void CG_ExportData(Args _args)
{
    SalesTable              salesTable;
    SalesLine               salesLine;
    InventDim               inventDim;
    InventLocation          inventLocation;
    Query                   salesTableQuery = new Query(queryStr(SalesUpdate));
    QueryBuildDataSource    qbdsSalesTable, qbdsSalesLine;
    QueryBuildRange         qbrSalesStatus, qbrSalesType;
    QueryRun                salesQueryRun;
    CommaIo                 commaIO;
    Dialog                  dialog  = new Dialog();
    DialogField             dialogField;
    int                     startTime, endTime, recCount;
    container               line;
    FileIoPermission        perm;
    Filepath                filename;
    #File

    dialog.caption("File Browser");
    dialogField = dialog.addField(extendedTypeStr(FilenameSave), "Choose File", "Choose file to export");
    dialog.filenameLookupFilter(['csv','*.csv']);

    if(dialog.run())
    {
        filename = dialogField.value();

        startTime = WinAPI::getTickCount();
        recCount  = 0;

        perm = new FileIoPermission(filename, "W");
        perm.assert();
        commaIO = new CommaIo(filename, "W");

        commaIO.outFieldDelimiter("|");        

        if( !fileName || commaIO.status() != IO_Status::Ok)
        {
            throw error("File Cannot be opened");
        }
        
        // Writes header to the file
        commaIO.write("CompanyID", "SalesId", "Customer", "Type", "Location", "ItemId", "size", "Qty");

        qbdsSalesTable = salesTableQuery.dataSourceTable(tableNum(SalesTable));
        qbdsSalesLine  = salesTableQuery.dataSourceTable(tableNum(SalesLine));

        // Filters the sales orders that have order types as Sales orders
        qbrSalesType = SysQuery::findOrCreateRange(qbdsSalesTable, fieldNum(SalesTable, SalesType));
        qbrSalesType.value(queryValue(SalesType::Sales));

        // Filters the sales orders that are in open status
        qbrSalesStatus = SysQuery::findOrCreateRange(qbdsSalesTable, fieldNum(SalesTable, SalesStatus));
        qbrSalesStatus.value(queryValue(SalesStatus::Backorder));

        // Filters the sales order lines that are in open status
        qbrSalesStatus = SysQuery::findOrCreateRange(qbdsSalesLine, fieldNum(SalesLine, SalesStatus));
        qbrSalesStatus.value(queryValue(SalesStatus::Backorder));

        salesQueryRun = new QueryRun(salesTableQuery);

        while (salesQueryRun.next())
        {
            salesTable   = salesQueryRun.get(tableNum(SalesTable));
            salesLine    = salesQueryRun.get(tableNum(SalesLine));
            inventDim    = salesQueryRun.get(tableNum(InventDim));

            line = [salesTable.dataAreaId, salesTable.SalesId, salesTable.CustAccount, enum2str(salesTable.SalesType), inventDim.InventLocationId,
                    salesLine.ItemId, inventDim.InventSizeId, salesLine.SalesQty];

            commaIO.write(line);

            recCount++;
        }
    }
    else
    {
        return;
    }

    CodeAccessPermission::revertAssert();

    endTime = WinAPI::getTickCount();
    info(strFmt("Executed for %1 minutes and exported %2 sales order lines", ((endTime - startTime)/1000)/60, recCount));
}


INPUT:

ExportData1.png

OUTPUT:

ExportData2.png

Regards,

Chaitanya Golla

Comments

*This post is locked for comments