Skip to main content

Notifications

Announcements

Join us in the Community for an AMA: December 12th Register Today
News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Loop through an AOT qu...
Microsoft Dynamics AX (Archived)

Loop through an AOT query in X++

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hi 

I have an AOT query, which I use datasource for my form. In that query I have 4 ranges. When I run the form, it prompts as expected with the query window. 

Screenshot_5F00_18.png

It opens the form with the expected result. 

I have a button that must create a ledgerjournaltable and journaltrans out of the records from the form. 

Screenshot_5F00_19.png

When I click on the button It runs through ALL the records and not the filtered records. 

Here is my code for the Click() of the button:

void clicked()
{
    AxLedgerJournalTable                journalTable; // class
    AxLedgerJournalTrans                journalTrans; // class
    container                           acctPattern;
    container                           offSetAcctPattern;
    DimensionDefault                    dimensionDefault;
    ProjTable                           projTableLocal;
    MainAccount                         mainAccount;
    LedgerJournalTrans                  ledgerJournalTrans;
    DimensionDynamicAccount ledgerDimension, offsetLedgerDimension;
    Query           query = new Query(queryStr (NCProjForeCastCostHEN)); // Query name.
    QueryRun        qr;
    QueryBuildRange qbr;
    ;

    

    journalTable = new AxLedgerJournalTable();
    journalTrans = new AxLedgerJournalTrans();
    
    //Opret ny hensættelseskladde
    journalTable.parmJournalName("HEN");
    journalTable.save();
    
     // Kør query
    qr = new QueryRun(query);
    
    // Find range 
    qbr = query.dataSourceTable( tablenum (ProjForecastCost))
            .findRange( fieldNum (ProjForecastCost, ProjId));
      qbr = query.dataSourceTable( tablenum (ProjForecastCost))
            .findRange( fieldNum (ProjForecastCost, ModelId));
      qbr = query.dataSourceTable( tablenum (ProjForecastCost))
            .findRange( fieldNum (ProjForecastCost, StartDate));
      qbr = query.dataSourceTable( tablenum (ProjTable))
            .findRange( fieldNum (ProjTable, ProjInvoiceProjId));
    
    
    //Opretter kladdelinjer
     while (qr.next())
    {
        projTable = projTable::find(ProjForecastCost_1.ProjId);
        projTableLocal  = ProjTable::find(ProjForecastCost_1.ProjId);  

        dimensionDefault = projTableLocal.DefaultDimension;
        
        select RecId from mainAccount
        where mainAccount.MainAccountId == "93075";
  
            ledgerDimension = DimensionStorage::getDefaultAccount(mainAccount.RecId);
            offsetLedgerDimension = LedgerJournalName::find("HEN").OffsetLedgerDimension;
        ttsbegin;        
            ledgerJournalTrans.JournalNum = journalTable.parmJournalNum();
            ledgerJournalTrans.TransDate = systemDateGet();
            ledgerJournalTrans.Txt = "Hensat - " + projTable.Name;
            ledgerJournalTrans.AccountType = LedgerJournalACType::Ledger;
            ledgerJournalTrans.OffsetAccountType = LedgerJournalACType::Ledger;
            ledgerJournalTrans.LedgerDimension = DimensionDefaultingService::serviceCreateLedgerDimension(ledgerDimension, dimensionDefault);
            ledgerJournalTrans.OffsetLedgerDimension = DimensionDefaultingService::serviceCreateLedgerDimension(offsetLedgerDimension, dimensionDefault);    
            ledgerJournalTrans.CurrencyCode = ProjForecastCost_1.CurrencyId;
            ledgerJournalTrans.insert();

        ttsCommit;
        info(strFmt("Journalnr. %1 er oprettet.", journalTable.ledgerJournalTable().JournalNum)); 
    }

    super();
        


}

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Loop through an AOT query in X++

    I did this and it helped:

    do
    {
        projForecastCost = qr.get(tableNum(ProjForecastCost));
        if(!projForecastCost.RecId)
            continue;
        projTableLocal = qr.get(tableNum(ProjTable));
        if(!projTableLocal.RecId)
            continue;       
        
       
        dimensionDefault = projTableLocal.DefaultDimension;

        select RecId from mainAccount
        where mainAccount.MainAccountId == #MainAccount;

            ledgerDimension = DimensionStorage::getDefaultAccount(mainAccount.RecId);
            offsetLedgerDimension = LedgerJournalName::find(#HEN).OffsetLedgerDimension;
        ttsbegin;
            numberSeq                                   = NumberSeq::newGetVoucherFromId((ledgerjournalname.NumberSequenceTable));
            ledgerJournalTrans.JournalNum               = journalTable.parmJournalNum();
            ledgerJournalTrans.TransDate                = systemDateGet();
            ledgerJournalTrans.Txt                      = #TXT + projTableLocal.Name;
            ledgerJournalTrans.AccountType              = LedgerJournalACType::Ledger;
            ledgerJournalTrans.OffsetAccountType        = LedgerJournalACType::Ledger;
            ledgerJournalTrans.LedgerDimension          = DimensionDefaultingService::serviceCreateLedgerDimension(ledgerDimension, dimensionDefault);
            ledgerJournalTrans.OffsetLedgerDimension    = DimensionDefaultingService::serviceCreateLedgerDimension(offsetLedgerDimension, dimensionDefault);
            ledgerJournalTrans.CurrencyCode             = ProjForecastCost_1.CurrencyId;
            ledgerJournalTrans.ReverseEntry             = NoYes::Yes;
            ledgerJournalTrans.Voucher                  = numberSeq.voucher();       
            ledgerJournalTrans.insert();

        ttsCommit;
        
        //info(int2str(SysQuery::countLoops(ProjForecastCost_1_ds.queryRun())));
        
    }
    while (qr.next());


  • Martin Dráb Profile Picture
    Martin Dráb 230,056 Most Valuable Professional on at
    RE: Loop through an AOT query in X++

    Can you confirm that projForecastCost = qr.getNo(1) ignores the first record? It's hard to believe.

    Please make sure look at this line of code in debugger; if you just look at the final result of your code, the problem may be caused by bugs anywhere in subsequent code, so that would be a wrong process of debugging. You should completely exclude code inserting to ledgerJournalTrans, because it's not relevant to your question and therefore it doesn't belong here.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Loop through an AOT query in X++

    my queryrun looks like this now:

     //Opretter kladdelinjer
     while (qr.next())
    {
        projForecastCost = qr.getNo(1);
        projTableLocal = qr.getNo(2);
        
        
        //info(projForecastCost.ProjId);
       // projTableLocal  = ProjTable::find(projForecastCost.ProjId);
        dimensionDefault = projTableLocal.DefaultDimension;

        select RecId from mainAccount
        where mainAccount.MainAccountId == #MainAccount;

            ledgerDimension = DimensionStorage::getDefaultAccount(mainAccount.RecId);
            offsetLedgerDimension = LedgerJournalName::find(#HEN).OffsetLedgerDimension;
        ttsbegin;
            numberSeq                                   = NumberSeq::newGetVoucherFromId((ledgerjournalname.NumberSequenceTable));
            ledgerJournalTrans.JournalNum               = journalTable.parmJournalNum();
            ledgerJournalTrans.TransDate                = systemDateGet();
            ledgerJournalTrans.Txt                      = #TXT + projTable.Name;
            ledgerJournalTrans.AccountType              = LedgerJournalACType::Ledger;
            ledgerJournalTrans.OffsetAccountType        = LedgerJournalACType::Ledger;
            ledgerJournalTrans.LedgerDimension          = DimensionDefaultingService::serviceCreateLedgerDimension(ledgerDimension, dimensionDefault);
            ledgerJournalTrans.OffsetLedgerDimension    = DimensionDefaultingService::serviceCreateLedgerDimension(offsetLedgerDimension, dimensionDefault);
            ledgerJournalTrans.CurrencyCode             = ProjForecastCost_1.CurrencyId;
            ledgerJournalTrans.ReverseEntry             = NoYes::Yes;
            ledgerJournalTrans.Voucher                  = numberSeq.voucher();
            ledgerJournalTrans.insert();

        ttsCommit;
        info(strFmt("Journalnr. %1 er oprettet.", journalTable.ledgerJournalTable().JournalNum));
    }


  • Martin Dráb Profile Picture
    Martin Dráb 230,056 Most Valuable Professional on at
    RE: Loop through an AOT query in X++

    Then you have a bug in your code for iteration. I've checked your code above and although it calls qr.next(), it never reads any record from qr. If you get some records, you must be using a different piece of code and I can't comment on bugs in that unknown code.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Loop through an AOT query in X++

    It now iterate through the records after I have done what you described. But it jumps over the first record and iterate through the rest.

  • Martin Dráb Profile Picture
    Martin Dráb 230,056 Most Valuable Professional on at
    RE: Loop through an AOT query in X++

    I showed you how to get the query; you just have to change the datasource name if it's not NCProjForecastCostHEN. And you already posted code for iterating a query, so you seems to know how to do it. So what exactly is the problem?

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Loop through an AOT query in X++

    Hi Martin

    Thanks for your reply.

    For me your solution sounds like the right one, but Im not sure if I have understood how to implement it.

    I have drag and droped the query to my form datasource and in the query I have all the ranges.

    So my question is how I can run through the form records that are visible?

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 230,056 Most Valuable Professional on at
    RE: Loop through an AOT query in X++

    Your code takes creates a new query from the definition in AOT and doesn't set range values. If you don't set any range values (and they aren't set in AOT), you shouldn't be surprised that the data isn't filtered.

    It seems that your requirement is to take the query used by your form datasource, but that's not what your code does.

    Instead of creating a new query from scratch, take a reference of your form datasouce and extract the query from it. For example:

    NCProjForecastCostHEN_ds.queryRun().query()
  • Nasheet Siddiqui Profile Picture
    Nasheet Siddiqui 440 on at
    RE: Loop through an AOT query in X++

    In you code you are using an AOT query not getting the query of the form.

    Once you run the form, you are opening the query windows by your code to select the range values???

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Loop through an AOT query in X++

    Hi Nasheet,

    I just enter the values when the query window opens. The only code I have is the one I have attached. But Im not sure if its the right way I do :)

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join us in the Community for an AMA: December 12th Register Today

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,219 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,056 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans