About Expression Syntax for Selection Criteria

Sage 300 ERP

previous page next page

Home > Financial Reporter > Designing Financial Statements > Retrieving G/L Account Information > Expression Syntax for Selection Criteria

About Expression Syntax for Selection Criteria

The easiest way to create a selection criteria expression is to use FR Paste. For more information, see About FR Paste.

You can also enter expressions directly into spreadsheet cells.

A simple criteria expression looks like this:

ACCTTYPE = "I"

A more complex one looks like this:

ACCTTYPE = "I" AND QTYSW = "Yes" OR ACCTDESC LIKE "%sale%"

ACCTTYPE and QTYSW (quantities switch) are fields in the account master file. All other fields in this file are listed in the next section.

Selection criteria use account master fields only. For more information, see Account Master Fields.

Left-to-Right Evaluation

Selection criteria expressions are evaluated strictly from left to right unless brackets are added. In other words, the above example will be evaluated as:

(ACCTTYPE = "I" AND QTYSW = "Yes") OR ACCTDESC LIKE "%sale%

The expression will select all accounts that contain the word "sale" in their description, and it will select all income statement accounts that also store quantities.

Note: Criteria are case-sensitive, so a description with “Sale” will be excluded.

Brackets

You may use brackets to change the order of evaluation. For example:

ACCTTYPE = "I" AND (QTYSW = "Yes" OR ACCTDESC LIKE "%sale%")

will select income statement accounts that store quantities, or that contain the word "sale" in their description.

All the relational operators work with all the field types except for switches (like QTYSW), where only the = and != (not equal) operators apply.

Spaces

You use spaces to separate field names, operators and constants in expressions. For this reason, if you are comparing a string that includes spaces, you must enclose the entire string in quotation marks.

Quotation Marks

Remember that criteria are text parameters. If you place criteria in formulas, you must enclose criteria text in quotation marks.

If you paste criteria in column B, you will notice that FR Paste inserts them as formulas containing text. For example:

="(ACCTTYPE = ""I"" AND QTYSW = ""Yes"" )"

FR Paste adds an extra pair of quotation marks around text values to accommodate spaces within the text strings.

If you paste a formula with criteria, it looks like this:

=FRACCT("ACCTDESC","","(ACCTTYPE = ""I"")")

If this is a default line column specification, the middle parameter in the formula will take on the account reference specified in column A.

LIKE

The LIKE operator is similar to the = operator, except the second operand can contain the wild cards % and _.

% matches any group of characters.
_ matches any single character.

So ACCTDESC LIKE "%sale%" is true for any accounts with descriptions that include the word "sale." This would include "Product B sales," as well as "Cost of sales."

Optional Fields

Selection criteria can also include optional fields. For example, the following expression restricts the results to accounts with account optional field ACCTCLASS = Sales:

A.ACCTCLASS = "Sales"

And, the following expression restricts the results to transactions with transaction optional field QUANTITY<= 0:

T.QUANTITY <= 0

For more information, see About Optional Fields in Financial Reports.