3305 Main Street Ste 019 Vancouver, WA 98663

How do I use pattern matching with GL Accounts?

Background
Some Office Connector reports allow you specify which GL Accounts to include by using pattern matching (using the SQL LIKE operator).  This allows you to chose which accounts to include or exclude by entering an account number pattern that consists of a combination of literal characters (that must match) and wildcard characters (that represent any alpha or numeric character).  This article describes how to enter patterns that can be used with the GL Account field.

Account Formats
The format of your account numbers is specific to how your company chose to implement Sage 300 Construction and Real Estate.  Specifically, at the beginning of your implementation, General Ledger allowed you to segment your account number to represent your various companies, departments or other entities.  These are represented in the prefix of the account which can consist of one to three prefix segments (each named according to its purpose).  Your account format includes a base account segment representing the account number and that can optionally be followed by a suffix segment.  The possible segments are shown below.

 Prefix A    Prefix B    Prefix C    Base   Suffix 
   -      -    .  

In order to supply a good pattern for selecting accounts on a report, it is important to know what your company's account format is.  Your format will use the Base segment but all other segments may or may not be in use.  Your format also consists of the separator characters that were chosen to separate each segment.

Wildcard Characters
When specifying a pattern that accounts need to match (to be included in your report); you can use two types of wildcard characters in addition to any literal characters.
 Character  Purpose
 % The percent symbol represents any number of characters (zero or more) that can be any alpha or numeric character.
 _  The underscore character represents a single character that can be any alpha or numeric character.

General Rule
For complex patterns, it is best to identify the pattern for each segment of your account number.  Each segment's pattern should be separated by whatever the separator character is in your account format.

Examples

Account Format

 Pattern

 Results

 Any  %  All accounts
 9999
(no prefixes)
 12%  All accounts where the first two digits are "12"
 AA-9999
(prefix A and base)
 10-%  All accounts where the Prefix A segment is "10"
 AA-9999
(prefix A and base)
 %-1_50  All accounts where the base account segment starts with "1", the second character of the base account is any number, and the last two digits are "50"
 AA-BB-9999.000
(prefix A, B, base and suffix)
 20-1_-5%.000  All accounts where prefix A is "20", prefix B starts with "1", the base account starts with "5" and the suffix is "000".