AX2466

Filter criteria syntax

Several areas of Axiom 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.

NOTE: This discussion applies to areas where filters can be created using the legacy Filter Wizard or by manually typing a filter. If you are creating a filter for a web report, see Using the Filter Wizard in the Report Builder.

Operators

The criteria statement operator can be one of the following: =, >,<,<>,<=,>=. For example:

ACCT.Acct>1000

SQL IN, LIKE, and BETWEEN 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 (an empty string), you may want to create a filter that includes or excludes records with these blank values. 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.

If the blank value is actually a null instead of an empty string, the filter should use the syntax IS NULL or IS NOT NULL. String columns should not allow null values, but Date and DateTime columns often allow null values. For example: Project.StartDate IS NULL or Project.StartDate IS NOT NULL

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 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 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 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.

Validating filters

When you are entering a filter criteria statement into an Axiom dialog, you can validate the filter to ensure that it uses correct syntax.

Filter validation is available in the various dialogs throughout the system, such as:

  • Security Management
  • Open Table in Spreadsheet
  • GetData Function Wizard
  • Copy Table Data

The validation feature only validates the filter syntax; it does not validate the logic of the filter or ensure that it will return the desired data. For example, it would detect if you misspelled a column name and allow you to correct it.

If the syntax is correct, the message "Filter is valid" appears in the dialog.

If the syntax has errors, the message "Filter is invalid" appears in the dialog. You can click this link to bring up a Filter Error message box that contains more information about the error. In the Filter Error message box, click Show Details to see the specific error message.