AX2466

Filter criteria syntax

Several areas of Axiom Platform 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, you must place the value in single quotation marks as previously shown. 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, the table is assumed based on the current context. For example, if the filter is used in an Axiom query, 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, the lookup table is assumed.

NOTE: This description applies to areas where you can create filters using the legacy Filter Wizard or by manually entering 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 previous example, 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 returns 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.

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

Reference 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 automatically uses 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

Reference values with apostrophes in filters

If a string column contains a value with an apostrophe (such as O'Connor), that apostrophe must be coupled 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 Platform reads it as Dept.VP='O' and does not know what to do with the rest of the text.

Dept.VP='O''Connor'

Valid. The extra apostrophe tells Axiom Platform 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 Platform will construct the appropriate syntax for you.

Reference 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 do 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 automatically converts the date or date-time value to the appropriate syntax.

Validate filters

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

Filter validation is available in the various pages 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 page.

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