I am going to go into some basics of Bank reconciliation matching rules and dive into both functional/technical detail.
In my previous post I did a summary and provided some useful links.
There are two main parts to setting up bank reconciliation matching rules.
Step 1: Find statement lines – this allows you to filter down on the statement lines you want to match for.
Step 2: Find bank documents allows you to both filter and match against the bank documents (bank/cheques transactions).
Notice how there is Basic criteria which allows you to set up quick/basic rules for matching. In the background it actually inserts a record in the bank rules. You could set some of those flags manually by adding them to the grid.
If we go to the backend and look browse the table BankReconciliationMatchRuleLine, you will see that setting a basic criteria has inserted a system generated record type. This is just shown as a flag or field on the form but it is an actual record in the table.
When the auto matching is run – it does a dynamic query which does a join between the statement line and the bank document line. Also any range filters, such as the day difference or the penny difference.
- You can only match 1 statement line to 1 document line. Microsoft does have a plan to allow 1 to many matching. No release date has been announced.
- When you are manually matching 1 statement to multiple document lines – it has to be a perfect amount match. No penny difference is allowed.
“To make correction, only one bank document can be marked”. You can however comment that line at your own risk – the penny difference goes to the first line on the bank document side.
3. If you are doing auto match and there is a penny difference – make sure you have set up the Allowed penny difference on the Bank account form.
4. If you are using bank reconciliation for multi company, make sure to create an inbound port for each company. Set the Restrict to company field.
Otherwise, you will have it try to import to the BC proxy accounts default company.
5. If your file/s contain more than one bank account statement – then make sure in the dialog you set the statement format but leave the bank account field empty. Otherwise, it will set the bank account to the one specified in the dialog.
When you leave the bank account field empty in the dialog it tries to resolve the Bank account from the file by the following order:
It does the search in the following order.
i. Bank name in Statement with bank account number; - ie. Use bank account number and name from the file. Name isn’t being imported in the some bank formats.
ii. Routing code or SWIFT code or IBAN with bank account number; In most of my implementation this isn’t set – usually set when doing international dealings.
iii. Bank account number ie. Bank account number from the file. Probably the most common way of resolving it.
iv. Bank name in statement, or Routing code or SWIFT code or IBAN
The method that does the search is here: