Loop through an AOT query in X++

(0) Share

Report

Posted on by Community Member

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.

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.

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

I have the same question (0)

All responses (11)

Answers (1)

Nasheet Siddiqui 440 on at

Like (0)

Report

Please show the code in which you apply values to the ranges.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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 :)

Was this reply helpful? Yes No
Nasheet Siddiqui 440 on at

Like (0)

Report

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???

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report
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()

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
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)); }

Was this reply helpful? Yes No
Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No