Skip to main content

Notifications

Announcements

Check out the New Agent Creator Community
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / Bank Statement Import ...
Microsoft Dynamics NAV (Archived)

Bank Statement Import - Make positive values negative

(0) ShareShare
ReportReport
Posted on by DavidW Azzure IT 50

Hi all,

First time post due to some serious head scratching. I'm fairly savvy with Data Exchange Definitions (more Import than Export, I've found out that quite a lot of UK payment export formats cannot be achieved by them as confirmed by Microsoft Advisory team). Anyway, that's getting off topic!

I have created a Statement Import data exchange definition in NAV 2016 - Version GB Dynamics NAV 9.0 which imports. On review of the HSBC statement provided by the client, column 15 - Credit Amount contains positive values, and Column 16 Debit Amounts contain negative values. NAV is expecting these the other way round. I have resolved the Debit issue without too much issue by using a transformation rule under the Field Mapping FastTab to remove - from values imported. To do this I created a new Transformation Rule Card with the following parameters:

Transformation Type: Replace

Find Value: -

Replace Value:

This will change -500.00 to 500.00

Now I want to create a new transformation rule for column 15 which will turn a positive value into negative e.g. 250.00 to -250.00

Can this be achieved using Transformation Rules or will it have to be achieved via a CodeUnit?

I'm a Functional Consultant so hoping it's the first, but if it's not possible please let me know.

Thanks in advance! 

*This post is locked for comments

  • Rajendra sahu Profile Picture
    Rajendra sahu 130 on at
    RE: Bank Statement Import - Make positive values negative

    Hi Prajeesh - How to set this multiplier ? can you share step by step Thanks in advance .(it will be thankful if you share by screen shot)

  • Suggested answer
    Prajeesh Nair_DMC Profile Picture
    Prajeesh Nair_DMC 5 on at
    RE: Bank Statement Import - Make positive values negative

    You don't need any customization for this. Add the hidden column  Multiplier in the Field Mapping Page. This can be used to change the value you get in the Credit or Debit to change the sign or keep one. I have no idea regarding when this was added.

    Annotation-2019_2D00_11_2D00_04-124830.png

  • AndreaNardiello Profile Picture
    AndreaNardiello 119 on at
    RE: Bank Statement Import - Make positive values negative

    Hi,

    thanks for sharing the solution!

    Anyway, FOB is not available anymore with the link you provided.

    Could you please upload it again?

    It will be very kind of you!

    Thanks

  • DavidW Azzure IT Profile Picture
    DavidW Azzure IT 50 on at
    RE: Bank Statement Import - Make positive values negative

    Hi Hannes,

    I don't think this can be achieved using standard functionality in Data Exchange Definition but I took the same approach as you suggested and created two custom fields with a validation to flip the signs and it worked perfectly.

    Thanks!

  • Verified answer
    DavidW Azzure IT Profile Picture
    DavidW Azzure IT 50 on at
    RE: Bank Statement Import - Make positive values negative

    Hi AnamB, I've uploaded the files to a file sharing service.

    This is the Data Exchange Definition I created for HSBC which you can import into NAV from the Data Exchange Definition page of NAV by selecting the import feature in the ribbon.
    https://nofile.io/f/rhFWG863YXj/HSBC+Statement+Import.xml

    This is the sample HSBC statement file that the definition has been created on:
    https://nofile.io/f/fdTYzt3YhAt/Sample+HSBC+Statement.csv

    This is the modified object in NAV (Table 274 Bank Acc. Reconciliation Line) which you will need to import to get the import to work successfully. Two new custom fields have been added to the table 50001 and 50002 which the Debit and Credit columns of the .CSV file have been mapped to in the Data Exchange Field Mapping page. On Validate, it takes the values of these and multiplies them by minus 1 which converts the sign i.e. -500*-1=500 and 250*-1=-250.
    https://nofile.io/f/sTROUKrjqVt/Table_274_BankAccRecLine_DWModified.fob

    To get this to work you will need to complete the steps as following:
    1) Import the modified object of table 274. Please remember to take a backup of the original object prior to import of my customised version in case you need to revert back to it.
    2) Go to Data Exchange Definitions in NAV and select Import Data Exchange Definition icon from the ribbon and select the Data Exchange .xml file I created.
    3) Complete the Bank Export/Import step and the Bank Account Card setup as outlined in the link I provided earlier in the thread but so they point at the definition HSBC_STMT_IMP
    4) Create a new bank rec and select Import Statement in the ribbon and select the relevant file or my sample statement file.

    This solution works in NAV 2016 and 2017. I haven't tried 2018 but it should also work in there.

    I hope this helps!

  • Community Member Profile Picture
    Community Member on at
    RE: Bank Statement Import - Make positive values negative

    Not a problem.. thanks so much for the link above. Appreciate you helping out.

  • DavidW Azzure IT Profile Picture
    DavidW Azzure IT 50 on at
    RE: Bank Statement Import - Make positive values negative

    Hi AnamB, apologies, it won't let me attach the file formats. I'll post them online so you can access them and share a link.

  • DavidW Azzure IT Profile Picture
    DavidW Azzure IT 50 on at
    RE: Bank Statement Import - Make positive values negative

    Hi AnamB, of course I'm happy to help. There is a real lack of knowledge out there on setting up Data Exchange Definitions so happy to pass on my knowledge to others:

    A sample of a statement file for HSBC import is attached. I have also included a the Data Exchange Definition I created for this today. I was going to explain in detail why the setup is completed as it has been but that information already exists online - see this link for step by step instructions on creating a statement for import:

    https://community.dynamics.com/nav/b/dynamicsnavcloudfronts/archive/2017/05/03/import-a-csv-file-for-bank-reconciliation-in-dynamics-nav-2017

    If you follow these steps you will be able to create a working statement import for most banks. In the UK, I have created these for Barclays, RBS, NatWest, Yorkshire Bank, Bank of America etc. HSBC is a little trickier to produce as on their statement the receipts are positive and the payments are negative. NAV expects these the other way round. I don't think it's possible to change them using a Transformation Rule (some functionality that can be used in conjunction with Data Exchange Definitions) but requires a modification to table 274 Bank Acc. Reconciliation Line. To make this change will require a Development licence. I have made a change to this table and waiting on one of my developer colleagues to ratify the code (I'm a functional consultant, not a developer), but I'm happy to share this with you once I know it's correct.

  • Community Member Profile Picture
    Community Member on at
    RE: Bank Statement Import - Make positive values negative

    Just realized you're working with HSBC statement as well, I'm having trouble trying to set up the import/export format. Would you be willing to share the format you used for CSV file?

    Thanks and good luck! Your post helped me with my future potential problem as well.

  • DavidW Azzure IT Profile Picture
    DavidW Azzure IT 50 on at
    RE: Bank Statement Import - Make positive values negative

    Hi Hannes,

    Thanks for the response. I have encountered this method previously of adding the two new fields on the table with a function to correct the positive/negative identifiers - this is a neat solution.

    I'm hoping someone can share their experience achieving it with standard functionality but I'm not convinced it can be achieved.

    Thanks again!

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

Check out the New Agent Creator Community

Quick Links

Jainam Kothari – Community Spotlight

We are honored to recognize Jainam Kothari as our June 2025 Community…

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > 🔒一 Microsoft Dynamics NAV (Archived)

#1
Community Member Profile Picture

Community Member 108

#2
Mohamed Amine Mahmoudi Profile Picture

Mohamed Amine Mahmoudi 100 Super User 2025 Season 1

#3
manish.yadav Profile Picture

manish.yadav 6

Last month Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans