Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics NAV forum
Answered

Please advise. How do I get the Bank Statement Lines to be filled up in Navision 2018?

Posted on by 3,950

Hi Community,

How do I get the Bank Statement Lines to be filled up in Navision ?

Please give me a step by step way of doing it. Thank you!

pastedimage1649669359317v1.png

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Please advise. How do I get the Bank Statement Lines to be filled up in Navision 2018?

    Josh,

    I copied my handout steps:

    pastedimage1651350034367v3.jpeg
    SETUP:
    1. Data Exchange Definition.

    2. Bank Export/Import Setup.

    3. Fields to be updated on Bank Account Card.

    Setup in Detail
    1) Data Exchange Definition
    Step 1: Go to the Data Exchange Definition page following the below path:
    Departments/Administration/Application Setup/General/ Data Exchange Definition page
    Here, we need to specify a data exchange setup that enables us to import a specific bank statement file.
    pastedimage1651350182293v51.png
    Step 2: Click on 'New'.
    Step 3: The ‘Data Exchange Definition' page will open. Enter the following details In the General Tab.
    • Code= TEST BANK IMPORT (Any relevant Code of your choice)

    • Name= TEST BANK IMPORT (Any relevant Name of your choice)

    • File Type= Variable Text

    • Type= Bank Statement Import

    • Reading/Writing Xml Port= 1220

    • Ext Data Handling Code unit= 1240

    • File Encoding = WINDOWS

    • Column Separator = Comma

    pastedimage1651350225098v52.png
    And the following details in the Posting Line Definitions Tab:
    1. Line Type: Detail.

    2. Code = Test (Any relevant Code of your choice)

    3. Name = TEST BANK IMPORT (Any relevant Name of your choice)

    4. Column Count= 7 (The Number of columns in the Bank Statment to be imported. For my e.g., it's 7.)

    pastedimage1651350249081v53.png
    Add the following details in the Posting Column Definitions Tab for all columns from the .CSV bank statement file, which we need to map in Dynamics NAV.
    • Enter Name of Column, Data Type of Column, Data Format, Data Formatting Culture.

    pastedimage1651350279142v54.png
    Step 4: Click on the Field Mapping button in the Line Definition FastTab. Now the Field Mapping window will open. Click on NEW.
    pastedimage1651350339998v55.png
    Enter the following details to map the Posting Exchange with the Bank Reconciliation Line Table, through which we will import the Bank Statement.
    General FastTab:
    Table= 274
    Name = Test CSV for Bank Recon (Any relevant Name of your choice)
    Mapping Code unit = 1248

    Field Mapping FastTab:

    • In Column No. click on the drop-down, then select the first line. It will point to the Column Definition from the Data Exchange Definition page.

    • Then, go to the Field ID column, click the dropdown. Field lookup will open. Select the field, then click on 'OK'.

    pastedimage1651350392656v56.png
    • Follow the same Procedure for all Lines.

    • Now the Field Mapping FastTabwill look something like this:

    pastedimage1651350416661v57.png
    2) Bank Import/Export Setup

    Step 6: Now go to Bank Export-Import Setup.

    Path: Departments/Administration/Application Setup/General/Task/Bank Export-Import Setup. Click on New.
    pastedimage1651350449018v58.png
    Enter the following details:
    • Code = TEST BANK IMPORT

    • Name = Test for Bank Recon

    • Direction = Import

    • Processing Code Unit Id= 1270

    • Processing Code Unit Name= Exp. Launcher Gen. Jnl.

    • In the Exchange Definition Code, Click the drop-down & Select = TEST BANK IMPORT

    pastedimage1651350034386v21.png
    Now, you can exit this window.
    3) Bank Accounts

    Step 7: Go to the Bank Account Card.

    Path: - Departments/Financial management/Cash Management/Bank Accounts
    pastedimage1651350543359v60.png
    Select the Bank and click on Edit. For my example, I have selected "WWB-EUR" bank account.
    pastedimage1651350565607v61.png
    Transaction
    4) Create Bank Reconciliation

    Step 1: On the Navigation pane, click Departments and then click Financial Management.

    Step 7: Click Cash Management and then click Bank Account Reconciliations.
    pastedimage1651350034391v27.png
    Step 3: In the Home FastTab, click New.
    pastedimage1651350034393v29.png

    Header

    Step 4: In the Bank Account No. field, select the bank account code.
    Step 5: The Statement No. and the Balance Last Statement fields are filled in automatically based on the information on the bank account card.
    Step 6: In the Statement Date field, enter the date of the statement.
    Step 7: In the Statement Ending Balance field, enter the balance from the bank’s statement.

    If you have a file that has the current bank statement, proceed to fill the lines automatically.

    pastedimage1651350034395v31.png
    Lines
    Step 7: On the Lines, Fast Tab, users perform the actual reconciliation by entering and adjusting the posted transactions and the transactions on the bank statement until they agree with one another.

    The lines include the following fields:

    • Transaction Date: Specifies the posting date of the bank account or check ledger entry on the reconciliation line when the Suggest Lines function is used.

    • Type: This shows the type of ledger entry or a difference to be reconciled on this line.

    The options are as follows:

    Bank Account Ledger Entry: Used to reconcile an entry from a bank account ledger.
    Check Ledger Entry: Used to reconcile an entry from a check ledger.
    Difference: Used to record an amount in the bank reconciliation that is not posted in Microsoft Dynamics NAV to balance the reconciliation.
    • Description: This shows a description of the transaction on the reconciliation line.

    • Statement Amount: This shows the transaction amount that appears on the bank's statement that shows on the reconciliation line.

    • Applied Amount: This shows the transaction amount on the reconciliation line that is applied to the bank account or the check ledger entry.

    • Difference: This shows the difference between the amount in the Statement Amount field and the Applied Amount field for the reconciliation line.

    • Value Date: You can use this field to enter the official date when funds are available and when interest is calculated on the amount on the reconciliation line.

    • Document No.: Shows the document number that is specified on the original bank account ledger entry.

    • Check No.: Shows the Check number (specified as the document number) used on the original check ledger entry.

    • Applied Entries: Specifies whether the transaction on the bank's statement on this reconciliation line is applied to one or more bank account or check ledger entries.

    Import Bank Statement

    Step 8: On the Actions tab, in the Functions group, choose Import Bank Statement.

    pastedimage1651350034397v33.png
    Step 9: Locate the file, and then choose Open to import the bank transactions into the lines of the Bank Acc. Reconciliation window.
    The format of the file is specified in the Bank Statement Import Format field in the Bank Account Card window.

    The below format is used for Importing the Bank Statement in our example. I am attaching a sample used to import in my example.

    Match Bank Statement Lines with Bank Account Ledger Entries

    To automatically match bank statement lines with bank account ledger entries:

    Step 10: On the Home tab, in the Matching group, choose Match Automatically. The Match Bank Entries window opens.
    pastedimage1651350034400v35.png

    Step 11: In the Transaction Date Tolerance (Days) field, specify the span of days before and after the bank account ledger entry posting date within which the function will search for matching transaction dates in the bank statement.

    Step 12: Choose the OK button to start the Match Automatically function.

    All bank statement lines and bank account ledger entries that can be matched change to green font, and the applied check box in the right pane is selected.

    To remove a match, select the bank statement line, and then, on the Home tab, in the Matching group, choose to Remove Match.
    pastedimage1651350034402v37.png
    Just for Information:

    To manually match bank statement entries with bank account ledger entries:

    1. In the left pane, select a bank statement line.

    2. In the right pane, select one or more banks account ledger entries that can be matched with the selected bank statement line. To choose multiple lines, press and hold the Ctrl key.

    3. On the Home tab, in the Matching group, choose Match Manually. The selected bank statement line and the selected bank account ledger entries change to green font, and the applied check box in the right pane is selected.

    4. Repeat steps 3 through 5 for all bank statement lines that are not matched.

    5. To remove a match, select the bank statement line, and then, on the Home tab, in the Matching group, choose Remove Match.

    5) Post an open transaction that is reflected in the Bank Statement

    Step 1: Let’s add an additional entry on the left pane: Bank Statement Lines.

    Step 2: In the bank account reconciliation, select the line with the entry to be posted.
    pastedimage1651350034404v39.png
    Step 3: On the Actions tab, in the Functions group, choose Transfer to General Journal. In the Gen. Journal Template field, select the journal template. In the Gen. Journal Batch field, select the journal batch.
    pastedimage1651350034406v41.png
    Step 3: Open the General Journal. The entry appears on the top line. Notice that the Bank account number has been inserted. Enter the General Ledger account as the balancing account, and then post the journal.
    pastedimage1651350034408v43.png
    Step 4: Again, click on Match Automatically to match all the lines.

    6) Post Bank Account Reconciliation

    Step 1: Open the relevant bank account reconciliation.
    Step 2: Update the Statement Ending Balance.
    Step 3: On the reconciliation lines, verify that all the fields in the Difference column should be 0.00, and the Total Difference field at the bottom should be 0.00.
    pastedimage1651350034411v45.png
    Step 4: On the Actions tab, in the Posting group, choose Post.
    • After the bank reconciliation, has been posted, a bank account statement is created. To view the bank account statement, open the relevant bank account card, and from the Navigate tab, in the Bank Acc. group, choose Statements.

    pastedimage1651350034412v47.png

    Step 4: Click on Edit to view the Bank Statement Entries.

    pastedimage1651350034414v49.png
    Hope this helps.
    Thanks,
    Steve
    [View:/cfs-file/__key/communityserver-discussions-components-files/34/6866.Bank-Statement-Import-File.xlsm:320:240]
  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Please advise. How do I get the Bank Statement Lines to be filled up in Navision 2018?

    thanks

  • joshtechsolutions Profile Picture
    joshtechsolutions 3,950 on at
    RE: Please advise. How do I get the Bank Statement Lines to be filled up in Navision 2018?

    Hi Kim,

    May I see a screenshot of where this setting in General Ledger Setup is?

    Thank you!

  • joshtechsolutions Profile Picture
    joshtechsolutions 3,950 on at
    RE: Please advise. How do I get the Bank Statement Lines to be filled up in Navision 2018?

    Thanks Josh, for your reply.. Hmm... The strange thing is that when my accounts selected Suggest Lines and input the month Jan, there was not results in the bank Statements.

    I had confirmed with her that we have yet to do any Jan Bank Recon.

    Is there anyway I can check whether it has been Bank Reconcile?

    Thank you so much.

  • Suggested answer
    Dallefeld Profile Picture
    Dallefeld 11,417 User Group Leader on at
    RE: Please advise. How do I get the Bank Statement Lines to be filled up in Navision 2018?

    To be clear, your setting in General Ledger Setup must the Auto Match to download the csv that Josh mentions and the Suggest Lines is used if you are not auto matching.

  • Verified answer
    JAngle Profile Picture
    JAngle 33,139 on at
    RE: Please advise. How do I get the Bank Statement Lines to be filled up in Navision 2018?

    Simply way is to use the “suggest lines” page action. Choose your dates and other filter conditions. The most common is to get a csv download from the business bank account. Using data exchange definitions you map the csv file. The bank account card can then use a chosen import method. On the bank reconciliation you then use the import statement page action. Choose your csv file and the data is added according to the data exchange definition.

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,768 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,985 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans