Wednesday, 5 November 2014

Deep dive - bank reconciliation matching rules [AX 2012 R3]

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.

http://dynamicsnavax.blogspot.com.au/2014/10/advanced-bank-reconciliation-ax-2012-r3.html

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.

image

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.

image

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.

image

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.

FAQ

  1. 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.
  2. 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.

MatchError2014-10-23_0720

     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.

image

     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.

image

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:

\Classes\BankStatementBankAccountIdentify\doSearch

7 comments:

Veronika Filonenko said...

Hi,
are you saying that you've tested the import of one file with multiple bank accounts and the system did the split it self?

Munib Ahmed said...

yes. It finds the bank account based on the search order listed above.

Veronika Filonenko said...

Thanks a lot.

Do you know if this feature existed in the R2 version? Or its new since R3?

Just curious, because we have made a huge mod on the bank file to separate all accounts to different files..

Veronika Filonenko said...

Hi Munib,

I have last question for you. How does the correction on reconciliation process work? I cannot find where the set up are done?
And I have this message :
"The bank document type Check does not support correction.'

Munib Ahmed said...

Hi Veronika,
Sorry but on both question. Don't know without looking into it.

Alison Curtis said...

Hi - Does anyone know if the 1 to many matching can now be done in R3? I see in this post from 2014 that it wasn't possible but wonder if it is now as I cannot get a rule that works to match 1 bank statement line to multiple bank documents.
Thanks Alison

Munib Ahmed said...

I haven't seen it being released.