Automatic Matching Rules
By setting up Automatic Matching Rules, users are able to filter for, and match the values of any field from the imported statement to any relevant table in Business Central. Furthermore, Automatic Matching Rules enable users to find the relevant sub-ledger or ledger account for application.
Advanced Payment Reconciliation contains pre-loaded rules, which can be changed, removed or added to. You can also create your own rules directly from the Automatic Matching Rules Page.
The default Automatic Matching Rules use the additional three reference fields added by the payment reconciliation. It is essential to make sure these fields get updated when importing. Our setup wizard includes a step to do this. Otherwise, you will have to create the rules manually.
Accessing Automatic Matching Rules
You can access the Automatic Matching Rules from the Payment Reconciliation Page. Follow the below steps to access Automatic Matching Rules from the Payment Reconciliation Page:
-
Click on the navigation menu item Rules.
-
Click on the navigation menu item Automatic Matching Rules.
- The Automatic Matching Rules List will open.
You can also access the Automatic Matching Rules from the bank account list and card.
From the Automatic Matching Rules List, you have two options for accessing the individual rules for editing:
- Clicking the ID field value for the rule you wish to view or edit; or
- Clicking Manage on the navigation menu and then clicking Edit or view.
Before creating new Automatic Matching Rules, or editing existing ones, it is important to have a basic understanding of the associated fields.
Automatic Matching Rule - Card
Here you can create or edit Automatic Matching Rules. The card consists of a header where you specify key details about this rule and the lines defining the field(s) from the statement line used for matching.
Header Fields
Field | Description |
---|---|
ID | This is an automatically generated number. |
Enabled | Specifies if the rule is enabled. You can only edit a rule that is not enabled. |
Matching Table | Specifies the table against which to match. For example, if your customers specify their account number as a reference, you can select the customer table. |
Debit/Credit | Defines if this rule is used for credits only, debits only, or both. Remember that debits show as positive numbers on the bank statement. Debits are often customer payments. Credits are negative numbers on the bank statement, which are often payments to your vendors. |
Priority | Sorts the sequence of rules to run through when you have multiple rules that apply to a given line. |
No. of Hits | Specifies the number of times this rule has been used for a match since it got created to indicate its effectiveness. |
Account Type | The record type to which the statement line will match against (e.g., Customer, Vendor, Bank Account, G/L Account). |
Account No. Field | The field from the Matching Table that will be assigned as the "Account No." on the journal line resulting from the matching. |
Table Filter | Sets a filter on the matching table for the records included in this matching rule. For example, If you only want to search on Vendor Bank Accounts that are enabled for EFT, you can utilise table filters on the matching rule. |
Sub-Ledger Auto Apply Methods 1 to 3 | These fields are used to control the application of open entries to the receipt/payment line and are explained in the next section. |
Sub-Ledger Auto Apply Methods
The Sub-Ledger Auto Apply Method fields specify how to automatically apply the incoming receipt/payment line to the customer/vendor/employee sub-ledger. The program first tries the method specified in Sub-Ledger Auto Apply Method 1. If this is not successful, it moves to the method specified in Sub-Ledger Auto Apply Method 2 and then Sub-Ledger Auto Apply Method 3 if Sub-Ledger Auto Apply Method 2 is unsuccessful.
Value | Description |
---|---|
None | The entries will not get applied - you will need to apply the entries manually. |
Apply to Oldest on Account | The oldest open transactions on the Account will be applied to the line. |
Use Transaction References (one entry) | This option applies when the matching table is the sub-ledger (Customer Ledger Entry, Vendor Ledger Entry, Employee Ledger Entry). The program will apply if the entry has a remaining amount. This option can only apply to a single open entry. |
Use Transaction References & Apply to Oldest | This option is similar to Use Transaction References (one entry) except it can apply to multiple open entries if multiple entries meet the criteria of the transaction references. |
Use Transaction References & Amount (one entry) | This option is similar to Use Transaction References (one entry) except it will only apply to the entry if the remaining amount is the same as the payment amount. |
Equal to Account Balance Due | This option is similar to Apply to Oldest in that it will apply to the oldest entries. However, it will only apply if the remaining amount of the open entries that are due at the payment date are equal to the payment amount. The program first tries entries up to the due date, then entries up to the due date plus the tolerance specified in setup and then without a due date. |
Any transaction with same amount (one entry) | This option is similar to Use Transaction References (one entry) except that it does not require the matching table to be the sub-ledger. It can only match to a single entry if there is only one open transaction with the same amount. |
Lines
There will always be at least one line per rule. The lines represent the matching fields from the statement that are used to filter against the matching table.
Field | Description |
---|---|
Bank Recon Field | The field to match from the imported statement. You will noticed that the default rules mostly used the additional three reference fields as mentioned above. |
Matching field | The field from the matching table the Bank Recon Field is matched against. |
Transfer to Gen. Jnl Field | Allows you to specify that this column should be transferred to the general journal when the entries are transferred. You can only select fields that are already setup in the Custom Fields page. |
Split Text | Specifies that you want the app first to split the field using a text separator (like a space or a comma). Usually, you split the text when the field contains several references, and you want to separate them so you can use them in isolation for matching. You can only use the Split Text for one of the lines on a matching rule. You can either split the text from Left to Right or Right to Left. The direction is important because the app will step through the split references until a match gets made. |
Text Separator | Specifies the text separator when you are using Split Text. You do not need to set a value if a space separates the text. |
Matching Method | Whole: the statement field value has to match to the whole matching field value. Starts with: Find the record where the Matching field begins with the Statement field value. Ends with: Find the record where the Matching field ends with the Statement field value. Contains: Find the record where the Matching field contains the Statement field value. |
Transformation Method | Specifies if the program should do transformation before matching. You can specify to use numerical characters only, or a Transformation Rule |
Transformation Rule | This is the rule that will be applied if Transformation Method is set to Transformation Rule. |
Matching Using | Specifies what part of the Bank Recon Field gets used for matching. This value is used in conjunction with the matching precision when the selected option is not equal to All Characters. You must specify either Leading Characters or Trailing Characters when the Transformation Method is Split Text. |
Matching Precision | Specifies the number of characters the app will use from the Bank Recon Field for matching. This field is compulsory when the Transformation Method is Split Text or when Match Using is not All Characters. If there are fewer characters in the Bank Recon Field than the number specified here, then the app will not use the rule. |
Case Sensitive | Specifies if the matching is to be case sensitive or not. |
Don't forget to scroll right when you are on the Automatic Matching Rules list to update all fields.
Create a Copy
When you need to create a new rule based on an existing rule, you can use the Create a Copy action on the Automatic Matching Rule card. This action does the following:
- Tests the current Automatic Matching Rule to ensure that it is correctly configured.
- Copies the current Automatic Matching Rule header and lines and disables the new rule.
- Opens the new Automatic Matching Rule on a different page.
Remember to enable the new rule once you have finished changing it.
Test Matching Rule
You can test a matching rule from the Payment Reconciliation page. This feature allows you to understand why a matching rule may not be working as expected or lets you test a new matching rule.
- This guide includes detailed instructions on how to create different types of matching rules.
Examples
Related-Party Name->Search Name (Customers and Vendors)
The Related-Party Name->Search Name rules get created automatically for customers and vendors. This rule intends to use the Related-Party Name from the statement to find a Customer or Vendor Account with the same text in the search name field.
Note the following:
- Sub-Ledger Auto Apply Method is set to Equal to Account Balance Due - this means the app will apply the incoming line to the customer or vendor entries that are due - provided that the amount is equal to the balance due.
- Matching Method is "contains" to handle where the related party name in the bank statement only contains part of the name setup in Business Central.