PointForce iTopia Search Tips

The PointForce iTopia search functionality makes use of search operators and query-by-example (QBE) expressions (or pseudo expressions) to retrieve data from the database.

The following subtopics are available: (you can click on the links or scroll down to view the text)

Criteria Helper

The Criteria Helper functionality enables a user to view the most common search operators available for a field.

In order to automate a part of the search process, the operators are placed in the correct position with regards to any expressions used and do not permit the addition of conflicting operators.

If hot keys are used when the search operator is applicable to a specific expression, the cursor is automatically positioned on that particular expression. If you select the Criteria Helper icon () the cursor is positioned on the last expression.

In order to view the criteria list, press the appropriate hot keys (Alt+G or Ctrl+Spacebar) or select the Criteria Helper icon.

Search Operators

When specifying search criteria, use the following search symbols, or symbol combinations, to help produce more specific search results. Note: The following symbols MUST occur at the beginning of your search expression. If any search symbols are encountered in your search expression AFTER one of these initial symbols, they will be treated as regular characters.

Symbol Description
~ Case Insensitive
= Equal To
<> Not Equal To
> Greater Than
>= Greater Than or Equal To
< Less Than
<= Less Than or Equal To
! Negate Expression

The following symbols can occur anywhere in your search expression. However, the symbols are listed in order of priority, and this priority will be used in determining which type of search operation to perform.

Symbol Description
; Multiple Values
| Multiple Values
* Multiple Multiple Character Wildcard
? Single Character Wildcard
.. Range

Note: The multiple value delimiters cannot be mixed within the same search expression.

The following symbol can occur anywhere in your search expression:

Symbol Description
\ Escape Character

The following symbol can only be used as a suffix, which means that it can only be added to the end of your search expression:

Symbol Description
+null Include Nulls

Leading spaces will be preserved, and trailing spaces will be removed from all search expressions. This also applies to individual values in a range or multi-value search expression.

If no symbol is specified, then an exact match is assumed.

In alphanumeric comparisons, a number is smaller than a capitalized letter, which is smaller than a lowercase letter: 0 < 1 < ... < 9 < A < B < ... < Z < a < b < ... < z

The following table provides examples of each of the Search Criteria listed above:

Search Operator Description
Case-Insensitive
~
Finds all occurrences of the specified value without regard for uppercase/lowercase. Applies only to alphanumeric/text fields.

Examples:
~FRENCH
~french
~French

Case insensitivity can also be combined with the wildcard symbols (*) and (?).

Examples:
~1A4?3B
~customer*
~Fr*21?3

Equal To
=
Finds all exact matches of the specified value.

Examples:
=quote (when entered in the 'Header Status' field in Product Inquiries-Orders/Quotes (IC45/I1), as an example, will search for ALL Quotes)
=1423
=tomato
=Tomato
=TOMATO
=ToMaTo
=2006-11 (during specific month, where QBE equivalent is 2006-11-01 00:00:00.0..2006-11-30 23:59.59.9)

= (to search for NULL)

Note: If a value you want to search for contains one of the reserved search symbols, then prefix the value with the equal operator. For example, if you want to search for 101A-208B<D,C,then enter =101A-208B<D,C to get an exact match. With the (=) in front, the (<), (-) and (,) will be treated as regular characters.

Escape Character
\
Escapes the QBE operators and affects the way iTopia interpets the QBE.

Examples:
AB\* (exact match on AB*)
AB\\* (like AB\*)
H5T*\;G1X* (like H5T*;G1X*)

Greater Than > Finds all values, except for NULL values, which are GREATER THAN the specified value.

Examples:
>0 (when entered in the Quantity Back-ordered field in Product Inquiries-Orders/Quotes (IC45/I1), as an example, will search for ALL Back Orders)
>1423
>federal
>2006-11-23 14:25:37 (after specific second, where QBE equivalent is >2006-11-23 14:25:37.9)

Greater Than Or Equal To
>=
Finds all values, except for NULL values, which are GREATER THAN OR EQUAL TO the specified value.

Examples:
>=1423
>=federal
>=2006-11-23 14 (during or after specific hour, where QBE equivalent is >=2006-11-23 14:00:00.0)

Less Than
<
Finds all values, except for NULL values, which are LESS THAN the specified value.

Examples:
<1423
<case
<2006-11-23 14:25 (before specific minute, where QBE equivalent is >=2006-11-23 14:25:00.0)

Less Than Or Equal To
<=
Finds all values, except for NULL values, which are LESS THAN OR EQUAL TO the specified value.

Examples:
<=1423
<=case
<=2006-11-23 14 (before specific hour, where QBE equivalent is >=2006-11-23 14:59:59.9)

Multiple Character Wildcard
*
Finds all exact matches of the specified value, and where * is zero or more arbitrary characters.

Examples:
14a*3B
fr*d
L2*;NO*4 (returns all codes that begin with L2 and codes that start with NO and end in 4)

Note: For a search expression like ABC..01*DEF-99, a wildcard operation would be performed because the (*) has higher precedence than the (..) and the (-).

Note:When using an Oracle database, the multiple wildcard character is automatically replaced with the Not Equal To (<>) search operator. If you want to also retrieve records for which the field value is null, do one of these:

  • Leave the criteria blank;
  • Use *+null; or,
  • Use <>+null.

Multiple Values
;
A delimiter that finds all occurrences of any of the specified values.

Examples:
423;1426;1533;1890
Costco;Rona
H5T*;G1G* (like H5T* OR like G1X*)

Multiple Values
|
This is the preferred method of separating multiple values in a search where the criteria contains a numeric value. This delimiter allows numbers to be formatted using using the comma as a decimal place or a thousand separator.

Examples:
Costco|Rona
33.50|4,342.23|5,599.12

Negate Expression
!
Finds all values which are not equal to the specified value (same functionality as the not equal to operator).

In addition, returns the opposite value of the search symbol used. For example, !10:20 returns any values not between 10 and 20.

Examples:
!order;01;dirbl;notes (when entered in the 'Shipping Document Number' field in SU45/I1, as an example, will search for ALL Back Orders)
!case,tare,pallet (not in list)
 !TAX* (not like)
!a..b (not in range)

Theoretically, all existing operators can be negated; however, the following already have an equivalent:

  • If the operator is <=a (less than or equal to), then to negate it, we would enter !<=a (not less than or equal to) or its equivalent, >a (greater than).
  • If the operator is <> (is not null), then to negate it, we would enter !<> (not is not null) or its equivalent , = (is null).

Note: ! is not the same as <>. ! means not, whereas <> means different than or not equal to. != is the same as <>.

Note: !a means not a, which is the same as <>a; this only works when you have a single value.

Not Equal To
<>
Finds all values, except for NULL values, which are not equal to the specified value.

Examples:
<>0 (when entered in the 'Quantity Back-ordered' field in Product Inquiries-Orders/Quotes (IC45/I1), as an example, will search for ALL Back Orders)
<>9876
<>ARM
<>arm
<>ArM
<> (to search for NOT NULL)
<>b* means the first character is NOT b and the second character is NOT *

Null
+null
Finds all values which are null

Examples:
A|B|C+null
CAN;US;AUS+null
!CAN;US;AUS+null (!negates the entire expression, except for the +null)

Range
..
Finds all values between, and including, the two specified values.

Examples:
1423..1429
A..Z
IM-1..IM-9
2006-02-18 00.00.00..2006-02-19 00.00.00

Single Character Wildcard
?
Finds all exact matches of the specified value, and where ? is one arbitrary character.

Examples:
1A4?3b
customer?

Query-by-Example Expressions

Query-by-example expressions exist to enhance searches using system variables. Such macros expressions are discussed in more detail below.

Macro Expression Description
{today +|- 0-9
hour|day|week|month|year}
{today} is internally replaced with the current system date, and then the additional instructions act upon the date returned. Its use is limited to date-type fields.

Math Operator: (+|-). Add or subtract the quantifier.

Quantifier: (0-9). Numeric that will be used in conjunction with the math operator and the qualifier to add or subtract a certain amount of time.

Qualifier: (hour|day|week|month|year). Hour or day or week or month or year. Day is default.

Note: If no qualifier is specified, the default is taken.

{current.postInstruction +|-
0-9 hour|day|week|month|
year}
{current} is internally replaced with the current system date and time and then the additional instructions act upon the date and time returned. Its use is limited to timestamp-type fields. Should always be used in combination with one of the following search operators: >, <, >=, <=, or =.

Post Instruction: (hour, day, week, month, and year).

Math Operator: (+|-). Add or subtract the quantifier.

Quantifier: (0-9). Number of hours, days, weeks, months or years to add or subtract. When a post instruction is specified, it uses the post instruction as the qualifier.

Qualifier: (hour|day|week|month|year). Hour or day or week or month or year. Default is day. Not valid when a post instruction is specified. The system will take the post instruction and use that as the qualifier.

{session.user} Internally replaced with the session user name. Its use is limited to character-type fields.

Examples:

{session.user}; tecsys: current user or user tecsys
<>{session.user}: anyone but the current user

{column.tablename.columname} A column reference macro that supports non-wildcard operators that act on a single operand (i.e. equal, not equal, greater than, less than, greater than or equal, less than or equal). The referenced column must be from the view's main table, that table's identifying ancestors, or from a parent-maintained table.

Example:

A new view is created from the INIDP table (Invoice History Detail Product) that contains the search criteria fields 'Domestic Line Extension' and 'SA Cost of Sales Dollars'.

To find all of the customer invoice records for which the Domestic Line Extension is less than the SA Cost of Sales Dollars, enter the following criteria in the Domestic Line Extension field of the view:

<{column.inidp.sacos}

The expression is always preceded by a search operator, in this case the less than sign '<'. The information inside the brackets indicates the column value that the search is being performed against. The first portion of the value is always 'column'. This is followed by the table and column name. Each value is separated by a period and must be enclosed in the curly brackets.

Table and column names can be found in the meta_md_column view.

Date Examples

  • Date used in examples: 03/07/2005

Expression Meaning/Interpretation QBE Equivalent
{today} Today 03/07/2005
{today + 1} Tomorrow 03/08/2005
{today - 1 week} 7 days ago 02/28/2005
{today - 1 month} 1 month ago 02/07/2005
{today - 1 year} 1 year ago 03/07/2004
{today - 4 week} 4 weeks ago (28 days ago) 02/07/2005
<={today} Less than or equal to today

Timestamp Examples

  • Date used in examples: 2005-03-07 11:03:45

Expression Meaning/Interpretation QBE Equivalent
{current} Current Timestamp 2005-03-07 11:03:45
{current - 1} 1 day before current timestamp 2005-03-06 11:03:45
{current - 1 hour} 1 hour before current timestamp 2005-03-07 10:03:45
{current - 1 day} 1 day before current timestamp 2005-03-06 11:03:45
{current - 1 week} 1 week (7 days) before current timestamp 2005-02-28 11:03:45
{current - 1 month} 1 month before current timestamp 2005-02-07 11:03:45
{current - 1 year} 1 year before current timestamp 2004-03-07 11:03:45
{current.hour} Current hour 2005-03-07 11
{current.hour -1} Current hour less 1 hour 2005-03-07 10
{current.day} Current day 2005-03-07
{current.day - 1} Current day less 1 day 2005-03-06
{current.week} Current week as a range
(start of week until end of week)
2005-03-06..2005-03-12
{current.week - 1} Start of last week until end of last week 2005-02-27..2005-03-05
{current.month} Current month 2005-03
{current.month - 1} Last month 2005-02
{current.year} Current year 2005
{current.year - 1} Last year 2004


Back