Bank reconciliation auto-matching in Business Central matches very few transactions

Problem:

With the latest wave release for Business Central, I am required to stop using the bank reconciliation I am accustomed to and start using the bank reconciliation with auto-match.

I created my Bank Statement Import using the “Set up a bank statement file import format” wizard. It only created a Data Exchange Definition with 3 columns. When I use it to import my bank transactions into my bank reconciliation, the auto match is not at all accurate. I don’t understand why I was pushed into this new bank reconciliation format. Is there anything I can do to make this better?

When I discussed the matching problem with Cari Corozza, my teammate at New View Strategies, she told me one of the problems is the Document No. in the bank file is not included in the DED when using the wizard to create it. The payment application rules are designed to use the Document/Ext. Document No. for matching, and since the document no. is not in the import, it is not available for the auto-matching to use in the bank reconciliation.

The wizard as helpful as it is, is not complete. Having the document no. for matching when you have many transactions of the same amount within the period selected for date tolerance would be very helpful.

Solution:

Add “Document No.” to the Data Exchange Definition created by the Bank Statement Import wizard.

Looking at the sample transaction export from bank account used to create the data exchange definition, we can see that column 9 contains the Check No.

Let’s add this column to the data exchange definition and map it to the corresponding field in Business Central. Go to Column Definitions, click on Show more options and select New Line on the line below where you need to insert the new column.

Assign the column number matching the column number in the transaction file. Give the column a name that corresponds to the column name in the file. Since I don’t know how long this field might be in my file and we use a comma as field delimiter, I am going to set the length of the column to 30. This will allow for plenty of characters in this field.

To map the column of data to a field in Business Central, go to Line Definitions and select the Field Mapping action. Insert Column No. 9 by selecting New Line from Show more options on the line below where the column needs to be inserted. Select Document No. as the Field ID and indicate this field is optional since not all records will have a value in this column.

Every bank downloads data differently, so remember this is just an example to illustrate the steps you need to take. Your Column and Fields Mappings will be different. But, what will be the same is that you need to determine what column to add, where to insert it and how to define the mapping.

Summary

Adding the Document No. from the bank file to the import format should increase the auto-matches you get when you import your bank transactions to the bank reconciliation or the Payment Reconciliation Journal. You will begin to realize the value in the new bank reconciliation format.

I hope this new information speeds you along on your journey to using BC The Righter Way^TM