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 / Finance | Project Operations, Human Resources, ... / how to get balance acc...
Finance | Project Operations, Human Resources, ...
Answered

how to get balance according to credit and debit in the bank statement report

(0) ShareShare
ReportReport
Posted on by M_R 125
Hello, here i am going to add balance according to the credit(-ve), debit(+ve) and current balance in bankAccountstatement report
for the first row balance should be calculated as current balance - credit or current balance + debit .
but from the second row balance of the previous row is considered as  Prev balance, now the calculation will be PrevBalance +debit or PrevBalance - credit
in the code i have declared PrevBalance is
real PrevBalance;
the code is..........
 
 while select forupdate bankAccountStatementTmp
            {
                select firstonly ledgerJournalTrans
                    where ledgerJournalTrans.RecId == bankAccountStatementTmp.SourceRecId;
                bankAccountStatementTmp.Txt = ledgerJournalTrans.Txt;
                bankAccountStatementTmp.XTSDebit = ledgerJournalTrans.AmountCurDebit;
                bankAccountStatementTmp.XTSCredit = ledgerJournalTrans.AmountCurCredit;
                            
                    ttsbegin;
                   
                    if(bankaccountstatementtmp.XTSCredit)
                    {
                                bankAccountStatementTmp.XTSCorrectedAmountCur = PrevBalance - bankAccountStatementTmp.XTSCredit;
                   
                    }
                    else if(bankAccountStatementTmp.XTSDebit)
                    {
                               bankAccountStatementTmp.XTSCorrectedAmountCur = PrevBalance +  bankAccountStatementTmp.XTSDebit;
                    
                    }
                    else
                    {
                         bankAccountStatementTmp.XTSCorrectedAmountCur = PrevBalance -  bankAccountStatementTmp.XTSDebit +  bankAccountStatementTmp.XTSDebit;
                    }
              
                PrevBalance = bankAccountStatementTmp.CurrentBalance - bankAccountStatementTmp.XTSCorrectedAmountCur;
                
                bankAccountStatementTmp.update();
                ttscommit;
            }
 
 
 
But getting the outpur like this
  • Verified answer
    M_R Profile Picture
    M_R 125 on at
    how to get balance according to credit and debit in the bank statement report
    Thanks for your suggestions!
     
    get balance through the expression of 
     
    =Fields!CurrentBalance.Value - RunningValue(Fields!Credit.Value,sum,Nothing) + RunningValue(Fields!Debit.Value,sum, "BankAccountStatementDS")
     
  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,219 Super User 2024 Season 2 on at
    how to get balance according to credit and debit in the bank statement report
    Hi M_R,
     
    Can you confirm if the column Balance is printed using the value in the field bankAccountStatementTmp.XTSCorrectedAmountCur?
    Have you checked if the sorting of the while loop is the same as sorting of the output on the report?
    From where do you get the Opening balance value? The first time in the loop, the PrevBalance variable might not be filled. At least, this is not provided in the part of the coding you shared in your question.
     
    In your coding, the second else should never be hit. When would you expect that this would be the path and what is the purpose of adding and subtracting the same amount?
  • M_R Profile Picture
    M_R 125 on at
    how to get balance according to credit and debit in the bank statement report
    Yes i have verified if the field bankAccountStatementTmp.SourceRecId, for credit and debit, but now facing issue with balance
     
    can we do it through the expression if we could store the balance as current balance.
     
    stilll trying with running value expression with current balance,
    but we want running value of balance + debit - credit
     
    not getting how it could be?
     
  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,219 Super User 2024 Season 2 on at
    how to get balance according to credit and debit in the bank statement report
    Hi M_R,
     
    When reading your question, I think this is a duplicate or continuation of your other question where we discussed already some details. Solved: bankaccountstatement report (dynamics.com)
     
    Anyway, you marked the previous post as solved and it looks like you made some progress. Have you used the debugger to find out what exactly is being executed and what values are being filled when e.g. data is being returned from select statements? Have you verified if the field bankAccountStatementTmp.SourceRecId has a value and if it is the correct one to search for a related ledgerJournalTrans record?
  • Suggested answer
    Giorgio Bonacorsi Profile Picture
    Giorgio Bonacorsi 487 on at
    how to get balance according to credit and debit in the bank statement report
    Hello,
     
    Can we have more information about the requirements? The bank account statement is performed using the BankAccountTrans.
    So, I'm assuming, reading your code, that you talk about a statement of your customer/vendor's bank account. However, as a Finance consultant (I'm not a developer), I can try to give some tips:
     
    It's not exit a transaction where it's not at least in debit or credit:
     
    lll
     
    Try to take the previous balance, as simpler as inheriting of the previous loop value:
     
    PrevBalance = bankAccountStatementTmp.CurrentBalance
     
    Thank you, I hope that was helpful for you.
     
    Kind regards,
    Giorgio Bonacorsi

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

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans