ProjectWise Explorer Help

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:'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:

  1. Simple value

    Simple value is normally transformed to $FIELD$='value' or $FIELD$=value depending if it is a text or numeric field.

  2. Value with a wild card (for example, *)

    Such value is normally transformed to $FIELD$ LIKE 'wild*card'

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

  4. Operator only

    Some operators do not use values. In this case, you only provide an operator. NULL is transformed to $FIELD$ IS NULL

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

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




    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

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

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

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

  4. Date format

    Through the interface, date format must be entered by the format of the current locale.

  5. > and < with the text fields

    String comparison is done on the database server. Character order depends on the database character set and collation.

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

  7. Date range macros and "now" time value

    As in other ProjectWise use cases, date is based on the database server's clock.

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