p EXPORT TO CSV WITH DATE DIRECT JOB X++ : VIKAS MEHTA :MICROSOFT DYNAMICS AX X++ CODE

EXPORT TO CSV DIRECT JOB X++ WITH DATE : VIKAS MEHTA

public static void export2csvInvJourTrans(InventJournalId _invJournalId)

{

    container           con, conHeader;

    InventJournalTrans  invJourTrans;

    InventJournalTable  invJourTable;

    InventDim           invDim;

    CommaIo              io;

    Dialog              dialog;

    DialogField         dlgFileName;

    Filename            csvfileName;

    FileIOPermission    permission;

    int                 recordCount = 0;

    SysOperationProgress exportprogress = new SysOperationProgress();

    #File

    #AviFiles

    ;

    try

    {

        exportprogress.setCaption("Exporting journal lines to CSV file....");

        exportprogress.setAnimation(#AviTransfer);

        // Header Column

        conHeader = ["JournalId","Date","ItemId","Description","LineNum","Site","Warehouse","Location","SerialNumber","OnHand","Counted","Quantity"];

        dialog = new Dialog("Export to CSV");

        dialog.filenameLookupFilter(["Excel Files","*.csv"]);

        dlgFileName = dialog.addField(extendedTypeStr(FilenameSave),"File Name");

        if(dialog.run())

        {

            csvfileName = dlgFileName.value();

            if(csvfileName)

            {

                permission = new FileIOPermission(csvfileName,#io_write);

                permission.assert();

                io = new CommaIo(csvfileName,#io_write);

                io.inFieldDelimiter(',');

                if(!io || io.status() != IO_Status::Ok)

                {

                    throw error("Error in opening file!");

                }

                // Write the header column

                io.writeExp(conHeader);

                invJourTable = InventJournalTable::find(_invJournalId);

                if(!invJourTable.Posted)

                {

                    recordcount = 1;

                    while select * from invJourTrans where invJourTrans.JournalId == _invJournalId

                    {

                         // Create rows

                        invDim   = InventDim::find(invJourTrans.InventDimId);

                        con= [ _invJournalId,

                               date2str(invJourTrans.TransDate,123,-1,-1,-1,-1,-1),

                               invJourTrans.ItemId,

                               invJourTrans.itemName(),

                               invJourTrans.LineNum,

                               invDim.InventSiteId,

                               invDim.InventLocationId,

                               invDim.wMSLocationId,

                               invDim.inventSerialId,

                               num2str(invJourTrans.InventOnHand,1,2,1,0),

                               num2str(invJourTrans.Counted,1,2,1,0),

                               num2str(invJourTrans.Qty,1,2,1,0)

                            ];

                        io.writeExp(con);

                        recordCount++;

                    }

                }

                CodeAccessPermission::revertAssert();

            }

            else

            {

                info("Invalid file name specified!");

            }

        }

    }

    catch(Exception::Error)

    {

        throw error("Erorr in exporting journal lines.");

    }

}

THE FIELD WITH ID '0' DOES NOT EXIST IN TABLE - SOLUTION :DYNAMICS AX X++ CODE

THE FIELD WITH ID '0' DOES NOT EXIST IN TABLE - SOLUTION :DYNAMICS AX X++ CODE 

ISSUE :

THE FIELD WITH ID '0' DOES NOT EXIST IN TABLE - SOLUTION.

SOLUTION:

YOU WILL GET THIS ERROR WHEN ONE OF THE FIELDS IS NOT MAPPED PROPERLY MOSTLY IN THE MAPS BEING USED.

MICROSOFT DYNAMICS AX 2012 : HOW TO USE QUERYHAVINGFILTER IN AX 2012: QUERY WITH GROUP CONDITION

How to use QueryHavingFilter in AX 2012

 QueryHavingFilter:

Consider the following scenario. The CUSTTABLE table has a field called CUSTGROUP, indicating the customer group the customer belongs to. We would like to get a list of all customer groups that have less than 4 customers in them.
Traditionally, in AX queries, we can group by the CUSTGROUP field, COUNT the RecIds. However, there was no way to filter on that counted RecId field. However, in SQL, the having statement gives you that ability:

SELECT CUSTGROUP, COUNT(*) FROM CUSTTABLE

        GROUP BY CUSTGROUP

        HAVING COUNT(*) < 4

In AX you can count, group by, but you'll need to loop over the results and check the counter manually if you want to filter values out. So, in AX 2012, a new query class was added: QueryHavingFilter, that lets you do just that:

static void QueryHaving(Args _args)

{

    Query                   query;

    QueryBuildDataSource    datasource;

    QueryBuildRange         range;

    QueryHavingFilter       havingFilter;

    QueryRun                queryRun;

    int                     counter = 0, totalCounter = 0;

    CustTable               custTable;

   

    query = new Query();

    datasource = query.addDataSource(tableNum(CustTable));

    datasource.addSelectionField(fieldNum(CustTable, RecId),

            SelectionField::Count);

    datasource.orderMode(OrderMode::GroupBy);

    datasource.addGroupByField(fieldNum(CustTable, CustGroup));

   

    havingFilter = query.addHavingFilter(datasource, fieldStr(custTable, RecId),

            AggregateFunction::Count);

    havingFilter.value('<4 o:p="">

   

    queryRun = new QueryRun(query);

    while (queryRun.next())

    {

        custTable = queryRun.getNo(1);

        info(strFmt("Group %1: %2", custTable.CustGroup, custTable.RecId));

    }

}

Note that in this code example, I added a selection field on RecId and used SelectionField::Count. This is not necessary for the having filter to work, the only reason it is in the code example is to be able to show it in the infolog (ie to have the count value available). So it is independent of the HavingFilter!