Filters

To select records in a table, you must use Filters. For information about selecting records, and for examples of Filters being used, see Retrieve a Record. This section describes how to create Filters.

Basic Comparisons

Normally, you can use operators like '==', '!=' and '<' to perform a single comparison, and determine whether two objects are equal, not equal and so on. Filters allow you to perform the same types of comparison, using the same operators, for every record in a table.

For example, if you have a Table object called table, the following expression creates a Filter that matches all records with "MyValue" in column A:

	table.Column("A") == "MyValue"
A B
MyValue XYZ
MyValue MyValue
XYZ XYZ
XYZ MyValue

The '==' operator can also be used between columns. The following example matches all records where the value in column A is equal to the value in column B:

	table.Column("A") == table.Column("B")
A B
MyValue XYZ
MyValue MyValue
XYZ

XYZ

XYZ MyValue

You can use the ‘!=’, ‘<’, ‘<=’, ‘>’ and ‘>=’ operators in the same way. Using Table.Column at one side of these operators creates a Filter.

NOTE: Table columns can be of integer type or of string type. If the ‘<’, ‘<=’, ‘>’, or ‘>=’ operators are used between integer columns the comparison is numeric. If used between string columns, the strings are compared using the numeric value of each character. If string columns have been used to store numbers, a string comparison is still used.

Combine Filters

Filters can be combined using the ‘&’ and ‘|’ operators, or negated using the ‘!’ operator.

The ‘&’ operator specifies rows that match both filters. The following example selects all records where column A is equal to “MyValue” and column B is equal to “XYZ”:

	table.Column("A") == "MyValue" & table.Column("B") == "XYZ"
A B
MyValue XYZ
MyValue MyValue
XYZ

XYZ

XYZ MyValue

In a similar way, the ‘|’ operator specifies rows that match either filter:

	table.Column("A") == "MyValue" | table.Column("B") == "XYZ"
A B
MyValue XYZ
MyValue MyValue
XYZ

XYZ

XYZ MyValue

The Like Operator

The ‘%’ operator tests whether the value in a column matches a string using wildcards. It tests that a column contains a matching string value, using the ‘%’ character as a wildcard that can match zero or more of any character, and using the '_' character to match any single character. For example, the following filter matches records where column A contains a string beginning “DEF-”, followed by any string, a “-” and then any single character:

	table.Column("A") % @"DEF-%-_"

This would match the following rows:

A B
DEFABC XY\Z
DEF-ABC-! XY\Z
DEF-123-% XY\Z

If you want to match a literal ‘%’ character you can escape it using ‘\’, for example:

	table.Column("A") % @"DEF-%-\%"

With that expression the second “-” must be followed by a literal ‘%’ character. This matches the following rows:

A B
DEFABC XY\Z
DEF-ABC-! XY\Z
DEF-123-% XY\Z

To match the “\” character, escape it with another “\”. To match the value in column “B” you would use this filter:

	table.Column("B") % @"XY\\Z"

Match All Records

To match all records in a table you can use the empty filter:

	Filter.Empty

Filter Examples

The following examples assume you have a Table object called “table”, and that it has three columns called “A”, “B” and “C”:

Filter Meaning
table.Column("A") == "MyValue" Column “A” is equal to “MyValue”
table.Column("A") == "MyValue" & table.Column("B") != new DateTime(1980, 1, 1) Column “A” is equal to “MyValue” and column “B” is not equal to 01/01/1980.
table.Column("A") < table.Column("B") | (table.Column("C") % "Tr%" & !(table.Column("D") >= 37)) Column “A” is less than column “B”, or both column “C” begins with the string “Tr” and column “D” is less than 37. (The '!' operator before "(table.Column("D")..." means that the condition reads "not" greater than or equal to 37).