Limitations of View Criteria Filter Expressions

While view criteria filter expressions are extremely convenient, they do not support every possible type of filtering that you might want to do.

This section describes several constructs that are not possible to express directly, and where possible, suggests an alternative way to achieve the filtering.

• Only a case-sensitive field name is allowed before the operator

  On the left hand side of the operator, only a case-sensitive field name is allowed. So, for example, even a simple expression like 1 = 1 is considered illegal because the left-hand side is not a field name.

• Cannot reference a calculated expression directly as an operand value

  You might be interested in querying all rows where one field is equal to a calculated quantity. For example, when querying trouble tickets you might want to find all open tickets whose Resolution Promised Date is less than three days away. Unfortunately, an expression like ResolutionPromisedDate <= today() + 3 is not allowed because it uses a calculated expression on the right hand side of the operator. As an alternative, you can compute the value of the desired expression prior to appending the view criteria and use the already-computed value as a literal operand value string substitution variable in the string or as the value of a bind variable.

• Cannot reference a field name as an operand value

  You might be interested in querying all rows where one field is equal to another field value. For example, when querying contacts you might want to find all contacts whose Home Phone Number is equal to their Work Phone Number. Unfortunately, an expression like HomePhoneNumber = WorkPhoneNumber is not allowed because it uses a field name on the right hand side of the operator. A clause such as this will be ignored at runtime, resulting in no effective filtering.

• Cannot reference fields of related objects in the filter expression

  It is not possible to reference fields of related objects directly in the filter query expression. As an alternative, you can reference the value of a related expression prior to appending the view criteria and use the already-computed value as a literal operand value string substitution variable in the string or as the value of a bind variable.

• Cannot use bind variable values of types other than Text, Number, Date, or Datetime

  It is not possible to use bind variable values of types other than the four supported types: Text, Number, Date, and Datetime. An attempt to use other data types as the value of a bind variable may result in errors or in the criteria's being ignored.