Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / Bank Transaction Listi...
Microsoft Dynamics NAV (Archived)

Bank Transaction Listing upload

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

Hi NAV Guru,

My client bank transaction listing has a Debit Amount and Credit Amount columns. Looking at the Data Exchange Definition for Bank Reconciliation, there is only one "Statement amount" field.

Is there a way to change the data exchange definition column to sum up the debit and credit column?

Currently, for each row, if Debit amount is not zero than the Credit Amount is zero, or vice versa.

However, if i tag both column to "Statement Amount" field, then the Credit Amount will overwrite the Debit Amount.

I am using:

Reading/Writing XMLPort 1220

Ext. Data Handling Codeunit 1240

Any suggestion how to combine them?


Thank you.

Regards,


Susy

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Bank Transaction Listing upload

    Hi Kemal,

    For my client, the statement file from the Bank is already in negative for Debit and positive for Credit, hence I don't need to do any conversion.

    Sorry can't help.

    Apart from splitting the amount into debit and credit columns, does the file has a column which indicates whether a transaction is a debit/credit?

    Reason, I am asking, if such column exists, you can use the "Negative Sign Identifier" to map it.

    see this posting.

    forum.mibuso.com/.../nav-2016-importing-bank-account-statement-using-data-exchange-definitions

    Regards,

    Susy

  • K_G Profile Picture
    K_G 130 on at
    RE: Bank Transaction Listing upload

    Hi Susy,

    both my debit and credit columns appearing as positive values, hence why it is split into debit and credit. How did you get a positive debit value and a positive credit value into a single value and positive or negative.

    Thanks in advance for whatever help you can provide.

    thank you

    Kemal

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Bank Transaction Listing upload

    Thank you all for your help.

    After much testings, finally figures out how to make it work.

    Map Debit Amount to Statement Amount (tick Optional, so that it will ignore Blank value)

    Map Credit Amount to Statement Amount (tick Optional, so that it will ignore Blank value)

    My mistake:

    1. In Field Mapping, I didn't tick optional, and try to find a way to convert blank to zero, when that is done, all the debit amount will keep getting overwritten by credit amount.

    2. Under Column Definition, I put in "Negative-Sign Identifier" = -

       This cause all the values to be wiped out and becomes zero.

    After deleting everything and start again for the xx times, it finally work.

    Hopefully, this post will help someone else who is new to NAV.

    Regards,

    Susy

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Bank Transaction Listing upload

    Hi Amol,

    I assume you mean the "Statement amount" amount field in NAV? How do you make that field to be negative and positive field? I check the standard transformation rule and there is no function to convert it.

    Thanks.

    Regards,

    Susy

  • ManishS Profile Picture
    ManishS 6,578 on at
    RE: Bank Transaction Listing upload

    The statement which you are going to import, or the statement you received from bank.

  • Suggested answer
    Amol Salvi Profile Picture
    Amol Salvi 18,694 on at
    RE: Bank Transaction Listing upload

    You need to put statement amount with sign +ve or -ve

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Bank Transaction Listing upload

    Thanks Manish.

    Do you mean to put the statement amount as -ve or +ve during the field mapping, or do i need to change the code unit?


    Regards,

    Susy

    Bank-Statement-Field-Mapping.PNG

  • ManishS Profile Picture
    ManishS 6,578 on at
    RE: Bank Transaction Listing upload

    try putting statement amount as -ne or +ve.

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

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans