Design Recon Rules

Which Recon Rule Editor should I use?

The Easy Rule editor should be powerful enough for 95% of the rules you want to write, including rules that require grouping. The advanced Rule Editor is needed for the following types of rules

• • Helper Rules where you use a second source of data to make the match.

  • If you want to perform any logic in the Select Statement (Case Statement, Substring, etc)

  • Often the logic in the Select statement can be done in an 'Other Match' Definition instead using Regex or any C# or VB function

Easy Rules

Adding other matches to an Easy Rule

• • If you need to match 2 fields with a tolerance, or strip a value out of a string using Regex (or any C# or VB function), do it in the other match section

  • Examples of basic expressions can be found by clicking the Help Button on the Other Matches editor.

Using Regex in C#

• • Has the format Regex(Field,@"Expression") e.g. Regex(Right.Details,@"(?<=\s)\d{9}")

  • This matches a 9 digit character string after a space e.g. 12345678 in 456785334545 123456789 which is a value found in the Details Field of the Right Dataset

  • To match this extracted value to a field in the Left dataset would require the following match definition Left.Details==Regex(Right.Details,@"(?<=\s)\d{9}")

Advanced Rules

• • Advanced rules require you to write the rule in XML format. You specify the SQL for the Datasets, decide what the unique identifiers for the datasets are and define both the exact and other matches

  • Converting an Easy Rule to an Advanced Rule will expose the XML structure of the rule

  • Every rule needs a LeftDataset and a RightDataset. One can be Bank, one Cashbook or a match can have left and right from the same side. The name must be Bank or Cashbook

  • Command - the body of the SQL Query to return the fields and data to be matched

  • This is the Dataset Selection and Criteria in the Easy Rule interface

  • OrderFields - The Exact matches that are made. Specified using the Ordinals of the Command Fields

  • This is the Exact Matches from the Easy Rule Interface

  • UniqueFields - the Ordinals that return a unique value per row of data. Can be the ID or a combination of fields where Grouping is used

  • GetKeysCommand - this section is required if the rule uses Grouping to make many-many or many-one matches. Must return the underlying ID's of the transactions making up the Group in order to match them

  • Matches - this section defines matches that have a tolerance or contain VB or C# functions

  • This is the Other Matches section of the Easy Rule interface

  • Helper - this section identifies the helper transactions (usually in the ReconHelper tables) that are used to make the match, so that they can also be updated with the applicable Recon ID

Run Rules across accounts

• • You can configure any rule, easy or advanced, to run across accounts

  • This allows you to automate matches that need to happen between transactions in different accounts

  • Remember to remove any exact amount match on accountid from the rule to allow this to happen

  • See here for more info on what happens when a match across accounts is made

Best Practices

• • Use @CurrentDomainID (case-sensitive) to run the rule on the current domain

  • Filter the datasets to only return columns you need and data that will match. Exclude zero values and blank columns

  • Sort the datasets and Sort LHS and RHS the same way

  • Specify exact matches in the Command SortOrder

  • Try specify at least one exact match, even if it is just accountid (if you're doing a Recon across accounts them AccountId is not an exact match!)

  • Specify the matches in the same order you sort the data

  • Exclude 'groups' of 1 transaction from Grouped rules (1-many, many-many). Set up a separate rule for 1-1 matches

  • Limit the datasets to recent transactions. If it hasn't matched in 90 days, it is unlikely to match now