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) / Adding Values on runtime
Microsoft Dynamics AX (Archived)

Adding Values on runtime

(0) ShareShare
ReportReport
Posted on by Mohamed Amir 95

Hello All;

Here is my query where i am joining two tables and want to calculate sum.

Can Somebody plz check and tell me how i can calculate sum , and whats wrong with my query....

Here is the query

while select AccountingCurrencyAmount,GeneralJournalEntry from generalJournalAccountEntry
join generalJournalEntry where generalJournalEntry.RecId == generalJournalAccountEntry.GeneralJournalEntry
&& generalJournalEntry.AccountingDate >= mkDate(1,1,2019) && generalJournalEntry.AccountingDate <= mkDate(28,2,2019)
&& generalJournalAccountEntry.LedgerDimension ==valueView2.ValueCombinationRecId
{

TotalValues += sum(generalJournalAccountEntry.AccountingCurrencyAmount);

info(strfmt('Sum %1 ledger dimension : %2',generalJournalAccountEntry.AccountingCurrencyAmount,TotalValues));
}

*This post is locked for comments

  • Suggested answer
    Mea_ Profile Picture
    Mea_ 60,278 on at
    RE: Adding Values on runtime

    You have a loop with select inside, so with minor changes you will get one value for all accounts and projects:

    while select valueView
    where valueView.DisplayValue == projId
       && valueView.DimensionAttribute == DimAttrProject.RecId
{
    select valueView2
        where valueView2.ValueCombinationRecId == valueView.ValueCombinationRecId
           && valueView2.DimensionAttribute    == DimAttrMainAccount.RecId
           && valueView2.DisplayValue          == cscMainaccount;

    if (valueView2.RecId)
    {
        select sum(AccountingCurrencyAmount) from generalJournalAccountEntry
            where generalJournalAccountEntry.LedgerDimension == valueView2.ValueCombinationRecId
                join generalJournalEntry order by AccountingDate Asc  
                    where generalJournalEntry.RecId == generalJournalAccountEntry.GeneralJournalEntry    
                       && generalJournalEntry.AccountingDate >= _startDate
                       && generalJournalEntry.AccountingDate <= _endDate;    

        totalValues += generalJournalAccountEntry.AccountingCurrencyAmount;
        }
}
    info(strFmt("%1",TotalValues));

    I highlighted changes I made 

  • Mohamed Amir Profile Picture
    Mohamed Amir 95 on at
    RE: Adding Values on runtime

    Hi ievgen;

    here is my complete code , What i want is to get total credit amount with the combination of Project and Main account.

    But the problem is i am getting a single value, job is returning all the records but my requirement is to sum all of them

    and display as a single record.... Can you check what's wrong with my code

    static void Job32(Args _args)

    {

      GeneralJournalAccountEntry generalJournalAccountEntry;

       GeneralJournalEntry        generalJournalEntry;

        DimensionAttributeLevelValueView valueView,valueview2;

       DimensionAttributeValueCombination combination;

       ProjId   projId;

       AccountingDate  _startDate = mkDate(1,1,2019);

        AccountingDate _endDate   =  mkDate(25,2,2019);              

       MainAccountNum   cscMainaccount;

       real TotalValues;

       DimensionAttribute DimAttrProject = DimensionAttribute::findByName('Project');

       DimensionAttribute DimAttrMainAccount = DimensionAttribute::findByName('MainAccount');

       projId = "OSM-06-01-01-0073";

       cscMainaccount = "33510002";

       TotalValues = 0.0;

    while select  valueView

           where valueView.DisplayValue == projId

           && valueView.DimensionAttribute == DimAttrProject.RecId

          {

                      select  valueView2

                      where valueView2.ValueCombinationRecId == valueView.ValueCombinationRecId

                      && valueView2.DimensionAttribute == DimAttrMainAccount.RecId

                      && valueView2.DisplayValue     ==   cscMainaccount;

              if(valueView2.RecId)

              {

                  select sum(AccountingCurrencyAmount) from generalJournalAccountEntry

                 where generalJournalAccountEntry.LedgerDimension == valueView2.ValueCombinationRecId

                 join  generalJournalEntry order by AccountingDate Asc  

                 where generalJournalEntry.RecId == generalJournalAccountEntry.GeneralJournalEntry    

                 && generalJournalEntry.AccountingDate >= _startDate

                 && generalJournalEntry.AccountingDate <= _endDate;    

                 TotalValues = generalJournalAccountEntry.AccountingCurrencyAmount;

                  info(strFmt("%1",TotalValues));

              }

    }

    }

  • Suggested answer
    Mea_ Profile Picture
    Mea_ 60,278 on at
    RE: Adding Values on runtime

    Does it even compile ? You can either use sum in select statement:

    select sum(AccountingCurrencyAmount) from generalJournalAccountEntry
    join generalJournalEntry 
        where generalJournalEntry.RecId == generalJournalAccountEntry.GeneralJournalEntry
           && generalJournalEntry.AccountingDate >= mkDate(1,1,2019) 
           && generalJournalEntry.AccountingDate <= mkDate(28,2,2019)
           && generalJournalAccountEntry.LedgerDimension == valueView2.ValueCombinationRecId;

TotalValues = generalJournalAccountEntry.AccountingCurrencyAmount;

info(strfmt('Sum %1 ledger dimension : %2',generalJournalAccountEntry.AccountingCurrencyAmount,TotalValues));


    or add them manually:

     

    while select AccountingCurrencyAmount from generalJournalAccountEntry
    join generalJournalEntry 
        where generalJournalEntry.RecId == generalJournalAccountEntry.GeneralJournalEntry
           && generalJournalEntry.AccountingDate >= mkDate(1,1,2019) 
           && generalJournalEntry.AccountingDate <= mkDate(28,2,2019)
           && generalJournalAccountEntry.LedgerDimension == valueView2.ValueCombinationRecId
{

    TotalValues += generalJournalAccountEntry.AccountingCurrencyAmount;

    info(strfmt('Sum %1 ledger dimension : %2',generalJournalAccountEntry.AccountingCurrencyAmount,TotalValues));
}

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!

Top 10 leaders for November!

Congratulations to our November super stars!

Community AMA December 12th

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

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,149 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans