Using Search Expressions

ProjectWise lets you use a simplified form and a full form of the expressions used as criteria. The latter is usually referred as custom expression and is rarely used. Only full, custom expressions must fully comply with the SQL database syntax.

Custom Expressions (Fully Qualified Expressions)

Custom expressions are the kind of expressions you can use in a SQL Where clause, such as:

document.name='my drawing'

However, because there is no way for the user to know the object names, the actual database table field is referred to using a special macro:

$FIELD$='my drawing'

You can use the macro as many times as you need in a single expression, for example:

$FIELD$='my drawing' OR $FIELD$='my painting'

The only limit is the criterion value length, which currently is 255 characters.

Note: It is imperative to use this macro in all cases when the user specifically wants to supply a full expression. Even if you supply a valid complete expression, but do not use this macro in it, ProjectWise may assume a simplified expression form and will interpret it by the rules listed below.

Simplified Expressions

Simplified expressions are always interpreted by the set of rules and are transformed to a fully qualified expression at the search execution time. Here are the basic value patterns and interpretation rules:

Simple value
Simple value is normally transformed to $FIELD$='value' or $FIELD$=value depending if it is a text or numeric field.
Value with a wild card (for example, *)
Such value is normally transformed to $FIELD$ LIKE 'wild*card'
Value following the operator
In this case the specified operator will be used in the expression. For example, if you specify >value, then it transforms to $FIELD$>value. You can also use = and LIKE operators explicitly, when you do not want the operator being selected by ProjectWise, depending on the presence of the * in the value.
Operator only
Some operators do not use values. In this case, you only provide an operator. NULL is transformed to $FIELD$ IS NULL
Quoted value
When you supply a quoted value, it whole will be interpret as a value, without trying to separate you the operator. This is useful when the value coincides with the operator name.

For example, 'NULL' is transformed to $FIELD$='NULL'
Full expression macros
ProjectWise supports a few expression macros that are interpreted as fully qualified expressions. Currently all of them are designed for DATE fields. They should either be used in a criterion alone, or be combined using AND, OR operators with other fully qualified expressions. For example:

$RANGE_THIS_MONTH$

or

$RANGE_TODAY$ OR $FIELD$ IS NULL

Here is the list of the supported full expression macros:

$RANGE_TODAY$ - expands to a fully qualified expression testing if $FIELD$ is today’s date

$RANGE_THIS_MONTH$ - expands to a fully qualified expression testing if $FIELD$ is date this month

$RANGE_THIS_YEAR$ - expands to a fully qualified expression testing if $FIELD$ is date this year

$RANGE_HOURS(x)$ - expands to a fully qualified expression testing if $FIELD$ falls between now and x hours before.

$RANGE_DAYS(x)$ - expands to a fully qualified expression testing if $FIELD$ falls between now and x days before.

$RANGE_MONTHS(x)$ - expands to a fully qualified expression testing if $FIELD$ falls between now and x months before.

Operators and Their Meaning and Syntax

Operator	Meaning	Simplified syntax	Full syntax
`=`	is equal to	`= 10`	`$FIELD$ = 10`
`<>`	is not equal to	`<> 'John'`	`$FIELD$ <> 'John'`
`<`	is less than	`< 5.2`	`$FIELD$ < 5.2`
`>`	is greater than	`> 2/12/2008`	`$FIELD$ > 2/12/2008`
`<=`	is less than or equal to	`<= 15`	`$FIELD$ <= 15`
`>=`	is greater than or equal to	`>= 20`	`$FIELD$ >= 20`
`BETWEEN`	is between ranges	`BETWEEN 1 AND 5`	`$FIELD$ BETWEEN 1 AND 5`
`NULL`	is a null value	`NULL`	`$FIELD$ IS NULL`
`NOT NULL`	is not a null value	`NOT NULL`	`$FIELD$ IS NOT NULL`
`LIKE`	matches wildcard	`LIKE 'word*'`	`$FIELD$ LIKE 'word*'`
`IN`	is in the list or set	`IN (1,2,25)`	`$FIELD$ IN (1,2,25)`
`NOT IN`	is not in the list or set	`NOT IN (89,90,91)`	`$FIELD$ NOT IN (89,90,91)`

Notes About Expressions

NULL and NOT NULL for document string properties
These will be interpreted as empty values, since ProjectWise does not actually store NULL in the database.
NULL and NOT NULL with environment attributes
These must be used with the Find in this environment only option, otherwise it will return all documents.
Date values in criteria
These may be specified with a different precision: to days, to hours, to minutes and to seconds; ProjectWise will interpret and apply them correctly. This does not apply to the fully qualified expressions (custom expressions). For the latter, if you specify the date to hours, minutes and seconds will be assumed 00:00. You need to use BETWEEN or specify the date ranges with > and < operators, or use database-specific date formatting functions.
Date format
Through the interface, date format must be entered by the format of the current locale.
> and < with the text fields
String comparison is done on the database server. Character order depends on the database character set and collation.
String comparison and string case
Whether the string comparison is case sensitive or not depends on the database character set and collation. ProjectWise has a setting that would force a case-insensitive comparisons. That only works on a simplified expression form, may result in a very inefficient queries and thus should be avoided. It is better to use a database collation that best suits your needs instead.
Date range macros and "now" time value
As in other ProjectWise use cases, date is based on the database server's clock.
Wildcards
ProjectWise supports * and ? for any string and any character, as well as database-specific % and _

Note that these wildcards are only valid in non-full text searches. Full text searches do not support the use of wildcards. If you do use the * or % character in the middle of word in a full text search, they will be treated as search word separators only, just like a space, comma, or semicolon.