Cash Management - Bank Integration

For several years, Dynamics NAV has offered bank integration using Bank Data Conversion Services provided by AMC-Consult A/S. While this is a great service, it is limited to less than 70 supported banks in the US. So, if your bank is not one of the supported banks, does this mean you miss out on this great functionality? Not at all! With this functionality, custom files can be mapped within Dynamics NAV for bank statement imports or Positive Pay exports.

 Bank Statement Imports

With a few setups, you will be able to define the layout of your bank statement file for import into Dynamics NAV. For this example, we are importing a .CSV format file which is widely available for download from most banks.

 importing a .CSV format file

 1. Data Exchange Definition setup

          a. Create a new Data Exchange Definition with the following properties on the General tab:

    i. Code - User-defined code for this data exchange definition (CSVBANKSTMT, in this    example)

                    ii. Name

                    iii. File Type = Variable Text

                    iv. Type = Bank Statement Import

                    v. Reading/Writing XMLport = 1220

                    vi. Ext. Data Handling Codeunit = 1240

 

New Data Exchange Definition

           b. Populate the Column Definitions tab with the details of the CSV file format 

Populate the Column Definitions tab

 c. Update the Line Definitions tab to reflect the Line Type of Detail, with a Code and Name (recommended to be the same code given to the Data Exchange Definition), and the Column Count equal to the number of columns in the CSV file format. Click Field Mapping to map details of the CSV file to the bank statement. 

Update the Line Definitions tab

                     i. Table ID = 274 (Bank Acc. Reconciliation Line)

                    ii. Name = User-defined name for the mapping

                    iii. Mapping Codeunit = 1248

 iv. Field Mapping tab - Select the columns, defined earlier in the Column Definitions, that map to the specific fields in the Bank Acc. Reconciliation Line table

 2. Bank Export/Import Setup

          a. Create a new record for the CSV bank statement import with the following properties:

                    i. Code (recommended to be the same as the Data Exchange Definition)

                    ii. Name

                    iii. Direction = Import

                    iv. Processing Codeunit ID = 1270

                    v. Data Exch. Def. Code = code for the Data Exchange Definition (created in step 1)

 Create a new record for the CSV bank statement

 

3. Bank Account setup

a. For the bank(s) that will be importing the statement in the newly created import format, populate the Bank Statement Import Format on the Transfer tab with the code for the Bank Export/Import Setup (created in step 2) 

With these setups in place, the bank statement file can be imported in the Bank Reconciliation, using the Import Bank Statement button, to populate the Bank Statement Lines tab. These lines are not available for either automatic or manual matching to the Dynamics NAV bank ledger entries.

 Imported in the Bank Reconciliation

Positive Pay Exports

With setups similar to those completed for a bank statement import, a Data Exchange Definition can be created for the export of check details according to the bank's requirements for Positive Pay processing.

1. Data Exchange Definition setup

          a. Create a new Data Exchange Definition with the following properties on the General tab:

                    i. Code - User-defined code for this data exchange definition (POSPAY, in this example)

                    ii. Name

                    iii. File Type = Variable Text

                    iv. Type = Positive Pay Export

          b. Populate the Column Definitions tab based on the file format required by the bank

 Column Definitions tab

 

c. Update the Line Definitions tab to reflect the Line Type of Detail, with a Code and Name (recommended to be the same code given to the Data Exchange Definition), and the Column Count equal to the number of columns in the required file format. Click Field Mapping to map details from the NAV table to the required bank file.

 Click Field Mapping

                     i. Table ID = 1241 (Positive Pay Detail)

                    ii. Name = User-defined name for the mapping

                    iii. Pre-Mapping Codeunit = 1704

                    iv. Mapping Codeunit = 1705

   v. Field Mapping tab - Select the columns, defined earlier in the Column Definitions, that map to the specific fields in the Positive Pay Detail table

2. Bank Export/Import Setup

          a. Create a new record for the Positive Pay export with the following properties:

                    i. Code (recommended to be the same as the Data Exchange Definition)

                    ii. Name

                    iii. Direction = Export-Positive Pay

                    iv. Processing Codeunit ID = 1700

                    v. Data Exch. Def. Code = code for the Data Exchange Definition (created in step 1)

 new record for the Positive Pay export

 3. Bank Account setup

a. For the bank(s) that will be importing the statement in the newly created import format, populate the Positive Pay Export Code on the Transfer tab with the code for the Bank Export/Import Setup (created in step 2)

 With these setups in place, exports can be created as needed using the Positive Pay Export function from either the Payment Journal or the Bank Account card/list page. Update the Cutoff Upload Date, if needed, then click Export to generate the Positive Pay export file.

 

click Export to generate the Positive Pay export file

 When prompted, save the file to the proper location. The file can now be transmitted/uploaded to the bank.

 

Lisa Doran

Lisa Doran

Senior Application Consultant

Related Posts