AX2466

Filter criteria syntax

Several areas of Axiom Software use criteria statements to define a set of data. The syntax for these criteria statement is as follows:

Table.Column='Value'

  • Table is the name of the database table.
  • Column is the name of the column in the database table.
  • Value is the value in the column.

If the column is String, Date, or DateTime, the value must be placed in single quotation marks as shown above. If the column is Numeric, Integer (all types), Identity, or Boolean, then the quotation marks are omitted.

For example:

  • To filter data by regions, the filter criteria statement might be: DEPT.Region='North'. This would limit data to only those departments that are assigned to region North in the Region column.
  • To filter data by a single department, the filter criteria statement might be: DEPT.Dept=100. This would limit data to only department 100.

If the table portion of the syntax is omitted, then the table is assumed based on the current context. For example, if the filter is used in an Axiom query, then the primary table for the Axiom query is assumed. If the current context supports column-only syntax, and the specified column is a validated key column, then the lookup table is assumed.

Operators

The criteria statement operator can be one of the following: =, >,<,<>,<=,>=. Greater than or less than statements can only be used with numeric values. For example:

ACCT.Acct>1000

SQL IN and LIKE syntax can also be used. For example:

DEPT.Region IN ('North','South')

Compound criteria statements

You can use AND and OR to combine multiple criteria statements. If you are creating long compound criteria statements with multiple ANDs or ORs, you can use parentheses to group statements and eliminate ambiguity. For example:

(DEPT.Region='North' OR DEPT.Region='South') AND (ACCT.Acct=100 OR ACCT.Acct=200)

NOTES:  

  • When filtering on multiple values in the same column, you must use OR to join the statements, not AND. In the example above, if the statement was instead DEPT.Region='North' AND DEPT.Region='South', that statement would return no data because no single department belongs to both the North and South regions. When you use OR, the statement will return departments that belong to either the North or the South regions.

  • Alternatively, you can use the SQL IN syntax to create a compound statement for values in the same column. For example, the statement DEPT.Region='North' OR DEPT.Region='South' can also be written as DEPT.Region IN ('North','South'). The Filter Wizard uses IN syntax by default.

Using criteria statements in functions

If you are using a criteria statement in a function, such as GetData, you must place the entire criteria statement in double quotation marks. For example:

=GetData("Bud1","DEPT.Region='North'","GL1")

You can also place the criteria statement in a cell and then use a cell reference in the function. In this case, you do not need to use double quotation marks in the function, unless you are concatenating text and cell reference contents within the function. For more details, see Using cell references in Axiom functions.

Referencing blank values in filters

If a string column contains a blank value, you may want to create a filter that includes or excludes records with these blank values. For SQL Server, the blank value is stored as an empty string. This empty string is indicated with empty quotation marks in the filter. For example: ACCT.CMAssign='' or ACCT.CMAssign<>''

If you use the Filter Wizard to construct the filter, it will automatically use the appropriate syntax.

Referencing values with apostrophes in filters

If a string column contains a value with an apostrophe (such as O'Connor), then that apostrophe must be escaped with another apostrophe so that it is not read as the closing apostrophe for the filter criteria statement. For example:

Dept.VP='O'Connor'

Invalid. This construction does not work because Axiom Software reads it as Dept.VP='O' and then does not know what to do with the rest of the text.

Dept.VP='O''Connor'

Valid. The extra apostrophe tells Axiom Software that the apostrophe is part of the string value and is not the closing apostrophe.

NOTE: This syntax must use two apostrophe characters in sequence and not a double quotation mark. If you create the filter using the Filter Wizard, Axiom Software will construct the appropriate syntax for you.

Referencing Date or DateTime values in filters

If your locale uses a date format where the first value is the day, filters using that date or date-time value will not process correctly. Instead, the date or date-time value must be in standard format. Standard format is YYYY-MM-DDTHH:MM:SS for DateTime and YYYY-MM-DD for Date.

If you use the Filter Wizard to construct the filter, it will automatically convert the date or date-time value to the appropriate syntax.