Dynamics ax 2012: Sample code for fetching records in tmp table using query and Insert record set


public static server void constructData(ASMARMatricsReportingTmp    _tmp,
                                        TransDate                   _asOfDate,
                                        TransDate                   _startDate,
                                        ASMDateCriteria             _dateCriteria,
                                        str2500                     _salesPool,
                                        str2500                     _customerAccount,
                                        str2500                     _company,
                                        QueryRun                    _queryRun,
                                        MenuItemNameDisplay         _menuItemName)
{
    UserConnection              userConn;
    CustTable                   custTable;
    SalesTable                  salesTable, salesTableLocal;
    CustInvoiceJour             custInvoiceJourLocal;

    Query                   qry;
    QueryBuildDataSource    qbds, qbds1;
    QueryRun                qrun;
    container               conCompany, conCustAccount, conSalesPoolId;
    int                     i, counter;
    Map                     targetToSourceMap;

    delete_from _tmp;

    userConn                                        = new UserConnection();

    _tmp.setConnection(userConn);

    userConn.ttsbegin();

    if (_asOfDate && _startDate )
    {
        qry = new Query();
        qry.allowCrossCompany(true);
        if (_company)
        {
            conCompany = str2con(_company, ';', false);
            for (counter = 1; counter <= conLen(conCompany); counter++)
            {
                qry.addCompanyRange(conPeek(conCompany, counter));
            }
        }
        qbds = qry.addDataSource(tableNum(SalesTable));

        qbds1 = qbds.addDataSource(tableNum(CustInvoiceJour));
        qbds1.joinMode(JoinMode::OuterJoin);
        qbds1.relations(true);
        if (_dateCriteria == ASMDateCriteria::DocumentDate)
        {
            qbds.addRange(fieldNum(SalesTable, DeliveryDate)).value(queryRange( _startDate, _asOfDate));

        }
        if (_dateCriteria == ASMDateCriteria::TransactionDate)
        {
            qbds1.addRange(fieldNum(CustInvoiceJour, InvoiceDate)).value(queryRange( _startDate, _asOfDate));

        }
        if(_customerAccount)
        {
            qbds.addRange(fieldNum(SalesTable, CustAccount)).value(con2Str(str2con(_customerAccount, ';', false)));
        }
        if(_salesPool)
        {
            qbds.addRange(fieldNum(SalesTable, SalesPoolId)).value(con2Str(str2con(_salesPool, ';', false)));
        }

        qrun = new QueryRun(qry);
        while (qrun.next())
        {
            if ( qrun.changed(tableNum(SalesTable)))
            {
                salesTableLocal = qrun.get(tableNum(SalesTable));
                custInvoiceJourLocal = qrun.get(tableNum(CustInvoiceJour));

                _tmp.SalesId = salesTableLocal.SalesId;
                _tmp.CustAccount = salesTableLocal.CustAccount;
                _tmp.Invoice = salesTableLocal.dispInvoiceId();
                _tmp.InvoiceAccount = salesTableLocal.InvoiceAccount;
                _tmp.Name = salesTableLocal.SalesName;
                _tmp.PoolId = salesTableLocal.SalesPoolId;
                _tmp.Settled = salesTableLocal.Settled;
                _tmp.ShipDate = salesTableLocal.DeliveryDate;
                _tmp.TransDate = custInvoiceJourLocal.InvoiceDate;
                _tmp.CreationUser = salesTableLocal.createdBy;
                _tmp.ModificationUser = salesTableLocal.modifiedBy;
                _tmp.DataAreaName = salesTableLocal.dataAreaId;
                _tmp.insert();
            }
        }
    }
    else
    {
        warning("Start date or end date missing, Please enter and fetch again.");
    }
    userConn.ttsCommit();
}

Using Query::insert_record set:

public static server void constructDataTmp(ASMARMatricsReportingTmp    _tmp,
                                        TransDate                   _asOfDate,
                                        TransDate                   _startDate,
                                        ASMDateCriteria             _dateCriteria,
                                        str2500                     _salesPool,
                                        str2500                     _customerAccount,
                                        str2500                     _company)
{
    UserConnection          userConn;
    CustTable               custTable;
    SalesTable              salesTable, salesTableLocal;
    CustInvoiceJour         custInvoiceJourLocal;

    Query                   qry;
    QueryBuildDataSource    qbdsSalesTable, qbdsCustInvoicejour;
    QueryRun                qrun;
    container               conCompany, conCustAccount, conSalesPoolId;
    int                     i, counter;
    Map                     targetToSourceMap;
    DataAreaId              dataAreaId;

    delete_from _tmp;

    userConn = new UserConnection();

    _tmp.setConnection(userConn);

    userConn.ttsbegin();

    if (_asOfDate && _startDate )
    {
        if (_company)
        {
            conCompany = str2con(_company, ';', false);
            for (counter = 1; counter <= conLen(conCompany); counter++)
            {
                dataAreaId = conPeek(conCompany, counter);
                changecompany(dataAreaId)
                {
                    qry = new Query();
                    qbdsSalesTable = qry.addDataSource(tableNum(SalesTable));

                    qbdsSalesTable.addSelectionField(fieldNum(SalesTable, SalesId));
                    qbdsSalesTable.addSelectionField(fieldNum(SalesTable, CustAccount));
                    qbdsSalesTable.addSelectionField(fieldNum(SalesTable, InvoiceAccount));
                    qbdsSalesTable.addSelectionField(fieldNum(SalesTable, SalesName));
                    qbdsSalesTable.addSelectionField(fieldNum(SalesTable, SalesPoolId));
                    qbdsSalesTable.addSelectionField(fieldNum(SalesTable, Settled));
                    qbdsSalesTable.addSelectionField(fieldNum(SalesTable, DeliveryDate));
                    qbdsSalesTable.addSelectionField(fieldNum(SalesTable, createdBy));
                    qbdsSalesTable.addSelectionField(fieldNum(SalesTable, modifiedBy));
                    qbdsSalesTable.addSelectionField(fieldNum(SalesTable, dataAreaId));


                    if (_dateCriteria == ASMDateCriteria::DocumentDate)
                    {
                        qbdsCustInvoicejour = qbdsSalesTable.addDataSource(tableNum(CustInvoiceJour));
                        qbdsCustInvoicejour.addSelectionField(fieldNum(CustInvoiceJour, InvoiceId));
                        qbdsCustInvoicejour.addSelectionField(fieldNum(CustInvoiceJour, InvoiceDate));
                        qbdsCustInvoicejour.joinMode(JoinMode::OuterJoin);
                        qbdsCustInvoicejour.relations(true);
                        qbdsCustInvoicejour.addRange(fieldNum(SalesTable, DeliveryDate)).value(queryRange( _startDate, _asOfDate));

                    }
                    if (_dateCriteria == ASMDateCriteria::TransactionDate)
                    {
                        qbdsCustInvoicejour = qbdsSalesTable.addDataSource(tableNum(CustInvoiceJour));
                        qbdsCustInvoicejour.addSelectionField(fieldNum(CustInvoiceJour, InvoiceId));
                        qbdsCustInvoicejour.addSelectionField(fieldNum(CustInvoiceJour, InvoiceDate));
                        qbdsCustInvoicejour.joinMode(JoinMode::InnerJoin);
                        qbdsCustInvoicejour.relations(true);
                        qbdsCustInvoicejour.addRange(fieldNum(CustInvoiceJour, InvoiceDate)).value(queryRange( _startDate, _asOfDate));

                    }
                    if(_customerAccount)
                    {
                        qbdsSalesTable.addRange(fieldNum(SalesTable, CustAccount)).value(con2Str(str2con(_customerAccount, ';', false)));
                    }
                    if(_salesPool)
                    {
                        qbdsSalesTable.addRange(fieldNum(SalesTable, SalesPoolId)).value(con2Str(str2con(_salesPool, ';', false)));
                    }

                    targetToSourceMap = new Map(Types::String, Types::Container);
                    targetToSourceMap.insert(fieldStr(ASMARMatricsReportingTmp, SalesId), [qbdsSalesTable.uniqueId(), fieldStr(SalesTable, SalesId)]);
                    targetToSourceMap.insert(fieldStr(ASMARMatricsReportingTmp, CustAccount), [qbdsSalesTable.uniqueId(), fieldStr(SalesTable, CustAccount)]);
                    targetToSourceMap.insert(fieldStr(ASMARMatricsReportingTmp, Invoice), [qbdsCustInvoicejour.uniqueId(), fieldStr(CustInvoiceJour, InvoiceId)]);
                    targetToSourceMap.insert(fieldStr(ASMARMatricsReportingTmp, InvoiceAccount), [qbdsSalesTable.uniqueId(), fieldStr(SalesTable, InvoiceAccount)]);
                    targetToSourceMap.insert(fieldStr(ASMARMatricsReportingTmp, Name), [qbdsSalesTable.uniqueId(), fieldStr(SalesTable, SalesName)]);
                    targetToSourceMap.insert(fieldStr(ASMARMatricsReportingTmp, PoolId), [qbdsSalesTable.uniqueId(), fieldStr(SalesTable, SalesPoolId)]);
                    targetToSourceMap.insert(fieldStr(ASMARMatricsReportingTmp, Settled), [qbdsSalesTable.uniqueId(), fieldStr(SalesTable, Settled)]);
                    targetToSourceMap.insert(fieldStr(ASMARMatricsReportingTmp, ShipDate), [qbdsSalesTable.uniqueId(), fieldStr(SalesTable, DeliveryDate)]);
                    targetToSourceMap.insert(fieldStr(ASMARMatricsReportingTmp, TransDate), [qbdsCustInvoicejour.uniqueId(), fieldStr(CustInvoiceJour, InvoiceDate)]);
                    targetToSourceMap.insert(fieldStr(ASMARMatricsReportingTmp, CreationUser), [qbdsSalesTable.uniqueId(), fieldStr(SalesTable, createdBy)]);
                    targetToSourceMap.insert(fieldStr(ASMARMatricsReportingTmp, ModificationUser), [qbdsSalesTable.uniqueId(), fieldStr(SalesTable, modifiedBy)]);
                    targetToSourceMap.insert(fieldStr(ASMARMatricsReportingTmp, DataAreaName), [qbdsSalesTable.uniqueId(), fieldStr(SalesTable, dataAreaId)]);
                    ttsBegin;
                    Query::insert_recordset(_tmp, targetToSourceMap, qry);
                    ttsCommit;
                }
            }
        }


    }
    else
    {
        warning("Start date or end date missing, Please enter and fetch again.");
    }
    userConn.ttsCommit();
}


Comments

Post a Comment

Popular posts from this blog

D365: SSRS Report Development/ Customization

Image
Hi guys, Today in this blog I will be demonstrating the procedure for the SSRS Reports development and customization in D365FO. Before proceeding with the Development or customization make sure that you have created a new model for your customization since we will be using extensions approach in our customization because An extension enables you to add functionality to existing model elements and source code. Coming back to the main objective of this blog which is to Develop SSRS reports in D365. One point to keep in mind is that there is no change on How you develop query based report and RDP based report in D365FO . The only change is you have to use extensions for standard report. If you are developing brand new report based on custom tables, there is no change. So in this Blog I will discuss How to customize an existing SSRS Report in D365 using Extensions approach step by step: Step#1:  The first step is to create a new model, Below are the steps to create a new model: Na...
Read more

D365: X++ code to add custom lookup on worker to show specific workers team workers only

/// <summary>             ///             /// </summary>             /// <param name = "_formReferenceControl"></param>             /// <returns></returns>             public Common lookupReference( FormReferenceControl _formReferenceControl)             {                 SysReferenceTableLookup      sysTableLookup = SysReferenceTableLookup ::newParameters( tablenum ( HcmWorker ), _formReferenceControl, true );                 Query ...
Read more