Thanks to Cynthia Priebe for allowing me to share her article on bank rec files with two columns!
Import your Wells Fargo Bank Statement to Bank Reconciliation in Microsoft Dynamics 365 Business Central with a Data Exchange Definition
Author: Cynthia Priebe, August 4, 2023
Senior Consultant & Trainer | Microsoft Certified D365 Business Central Functional Consultant | Microsoft Certified Trainer–MCT | Frequent Presenter Microsoft Dynamics 365 Business Central/NAV ERP; Accounting Automation
Microsoft Dynamics 365 Business Central (BC) Data Exchange Definitions are used to export or import data between BC and an outside source. One of the most popular uses is importing a bank statement into BC for use in a bank reconciliation. In most cases, extracting your data from the bank and using it with Assisted Setup’s Set up bank statement file import format works without changes needed to the Data Exchange Definition (DED). Unfortunately, the Wells Fargo transaction export file presented some challenges.
File contains amounts in 2 columns, need one to be blank
When opened in Notepad, the file contains 2 columns for the amounts. One for the Debit Amounts, and the other for the Credit Amounts. Both columns contain values for each transaction. One of the column values is 0.00, while the other column contains the transaction value. So that the DED will work correctly when importing the bank statement to the bank reconciliation, replace all 0.00 in the export file with blanks.
Open the file in Notepad. Go to Edit->Replace. Use 2 single quotes (apostrophes) to represent “blank” in the replace field. Replace all.
Use Replace in Notepad to get blanks instead of 0.00 in file
Assigning 2 columns to Amount in Field Mapping
In Column Definitions, only one amount definition is found. To modify the Column Definition to recognize 2 columns for the amounts, add Column 14 for Credits. To save changes, length must be added to each column definition. When Assisted Setup creates definitions, it does not assign a length to all the columns, but saving as a user, length is required.
Column Definitions in Business Central Data Exchange Definition
Mapping 2 columns to one field in BC
Field Mapping needs to be updated to accept an amount from either of the amount columns into the one field in BC.
Field Mapping Amount Columns in Business Central Data Exchange Definition
- On Column 13, select Optional (only one field will contain a value on each line). Set the Multiplier to negative one (-1.00) to change the sign of the amount coming in from the bank file. Select Overwrite Value so that if Column 14 contains the amount, it will overwrite the empty value in Column 13.
- Add Column 14, select Field ID 7 (Statement Amount). Select Optional (only one field will contain a value on each line).
Once these changes are complete, test.
Summary
A Data Exchange Definition can be used to successfully import Wells Fargo transaction activity to a Bank Reconciliation or to the Payment Reconciliation Journal. If you follow the steps above to modify the DED created by Assisted Setup and run Replace from Notepad for each file, you can use the Import Bank Statement action allowing you to efficiently reconcile your bank account to BC. I hope you agree that this is The Righter Way to automate Wells Fargo reconciliations in Microsoft Dynamics 365 Business Central.