PointForce iTopia (v. 8.1 and higher) Search Tips

The PointForce search functionality makes use of search operators and query-by-example (QBE) expressions (or pseudo expressions) to retrieve data from the database. This document is designed for use with PointForce iTopia 8.1 and higher.

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

 

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.
 

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
 <>    (to search for NOT NULL)

 
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)
 

Negate Expression
!
Finds all values which are not equal to the specified value (same functionality as the "not equal to" operator). Note: you can only use the exclamation mark (!) as the first character of the expression.

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:
a) 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).
b) 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.

Note: ! does not negate the +null suffix.

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 (-).
 

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

  Examples:
 14a?3B
 customer?
 

Multiple Values
;
Finds all occurrences of any of the specified values.

  Examples:
 423;1426;1533;1890
 Costco;Rona
 

Multiple Values
|
This is the preferred method of separating multiple values in a search where the criteria contains a numeric value. This 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
 

Range
..
Finds all values between, and including, the two specified values. If multiple range symbols occur in your search expression, the first highest priority symbol encountered (according to the order in the list above) is treated as the range symbol for your search, and all other occurrences are treated as regular characters.

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

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*)
 

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)
 

Query-by-Example Expressions

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

Processing Instruction
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.

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. Defaul 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} Internaly 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 processing instruction 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