Auto Bank Reconciliation NAV 2016 from Variable Text Format

Hi All,

You can exchange data between Microsoft Dynamics NAV and external files or streams in connection with common business tasks, such as sending and receiving electronic documents and importing and exporting bank files.

Before you can send and receive electronic documents or import and export bank files, you must set up the Data Exchange Framework to process the involved data files or streams.


Auto Bank Reconciliation NAV 2016 for variable text using Data Exchange Framework

Below are the steps for Importing Variable Text CSV Bank File .

Step 1: Search for Data Exchange Definition Page and click New.



Step 2: Enter the following details :

• Enter the Code and Name
• Select File Type as Variable Text. ( There are options for Fixed Text, XML, Json)
• Select Type as bank Statement Import.
• File Encoding as Windows
• Column Separator  as Comma.
• You can specify No. of Header Lines in the file, if there are any. If yes then are there any Header and Footer Tags. So that rows can be skipped during Import.
• Select Reading/Writing XMLport as 1220

• Select Ext. Data Handling Codeunit as 1240

Step 3: Column Definition Tab : Here we need to define all the fields in the bank statement file with the format of each field.

• Enter Column No. & Name
• Select Datatype of Colum whether text or date, Decimal, etc.
• If its Date Column Specify the  Date Format
• If its Date & Decimal Columns Specify the Data formatting Culture ( en-US, etc.) for Regional Formats.
• You can give other information for columns as well. ( Length, Description, Padding, etc.)

Step 4: Now define Line Definitions and Field mapping with Bank Account Ledger Entry.

• Select Line Type as Detail. ( Available options are Detail, Header, Footer as files can have Header and Footer rows)
• Enter Code and Name.
• Enter Column Count as 4 as have selected four columns.
• Now got to Field Mapping

Step 5 : Field Mapping

• Click on New
• Select Table ID as 274 for Bank Acc. Reconciliation Line.
• Give some descriptive Name.
• Select Mapping Codeunit as 1248

 

• Now Add Field Mapping Lines as shown below. We need to Map the Bank Statement File Fields with Bank Account Ledger Entry Fields.

Now it should look like this after mapping all four fields



Step 6: Now Add the Data Exchange Definition in Bank Export/Import Setup.

• Enter Code and Name
• Select Direction as Import
• Select Data Exch. Def. Code as GIROCSV which we created.

Step 7: Now tag the same on Bank Account Master.




 Step 8: Create Bank Acc. Reconciliation

• Search for Bank Acc. Reconciliation Page 
• Click on New
• Select Bank Account No.
• Enter Statement No.
• Select Statement Date for which you are doing Reconciliation.
• Right Hand side will show the Open Bank Account Ledger Entries.
• Now Click on Import Bank Statement and Select the CSV bank Statement File.

Sample Data of CSV file
01-01-2016,Payment To 1,108017,-37500
01-01-2016,Payment To 2,108018,-8250
31-01-2016,Payment To 3,108001,-25000

After Importing.




Step 9 : Auto Reconcile by clicking on Match Automatically marked in Red. Give the Tolerance days and Click Ok.



3 rows are in Green which got matched.



Matching is done on some criteria and priority. Please check below link for the same
https://msdn.microsoft.com/en-us/library/dn414563(v=nav.90).aspx


Thanks & Regards,
Nandesh Gowda