Show/Hide All

Click a question below to view the answer.

 What are the differences between filtering the lookup criteria and searching in the lookup?

When filtering, you can define an unlimited number of filters on multiple lookup fields, and you can save these filters so that they always apply to the lookup.

When you perform a search in the lookup window, you can search in one lookup field, unless you select Full Text search. When using the Full Text search, all columns in the view are searched, but you can enter only one value to search for. Also the search criteria cannot be saved. For more information, see Filter the Lookup Criteria.

 

 What are the functions of the filter operands and the value parameters?

The following table describes the operands and values that can be used in the Filter window. For more information, see Filters and Filter the Lookup Criteria.

 

Operand Selection	Operand Function	Value
Begins with	Displays only the data records that begin with the value(s) specified.	Multiple values are separated by a comma or alternate separator and assume an OR condition.
Ends with	Displays only the data records that end with the value(s) specified.	Multiple values are separated by a comma or alternate separator and assume an OR condition.
Contains	Displays only the data records that contain the value(s) specified.	Multiple values are separated by a comma or alternate separator and assume an OR condition.
Less than	Displays the data records less than the value specified.	Can only enter a single value.
Greater than	Displays the data records greater than and equal to the value specified	Can only enter a single value.
Range	Displays the data records included in the starting and ending value.	When Range is selected as the operand, the From and To fields appear. These fields will be validated.
Equal to	Displays only the data records that equal the value(s) specified.	Multiple values are separated by a comma or alternate separator and assume an OR condition.
Not Equal to	Displays the data that is not equal to the value(s) specified.	Multiple values are separated by a comma or alternate separator and assume an AND condition.
Is Empty	Displays the records for which the specified field is empty.	Not used.

 

 

How do I use multiple filters and alternate separators?

Multiple filter example:

The following is an example of a multiple filter that was created using the Filter feature in the Customer List dialog box. The lookup for the filter returns all vendors beginning with 12-ALLENAP and all ZIP Codes beginning with the number 7 or 9.

Column	Operand	Value
Vendor No.	Begins with	12-ALLENAP
Zip Code	Begins with	7,9

 

Alternate separator examples:

Alternate separators (other than the reserved comma) can be used to separate multiple selection values for a lookup. The alternate separators include the following:

~ ! @ # $ % ^ & * ( ) - = [ ] \ ; ‘ , . / _ + { } | : < > ?

Note When alternate separators are used, the value list must end with the alternate separator character.

The following filter uses the forward slash (/) character as a separator in place of a comma (,) and returns all vendors beginning with 01 and 02.

Column	Operand	Value
Vendor No.	Begins with	01/02/

The comma can be used as part of the selection value, if necessary. To create a filter that includes the reserved comma in multiple selection values, use an alternate separator between the values and at the end of the selection value list.

Column	Operand	Value
Name	Equal to	Smith, John#Brown, Mary#Lewis, Donna#

When creating a filter that includes the reserved comma in a single selection value, be sure to use the alternate separator before and after the selection value. The following filter returns the name "Smith, John".

Column	Operand	Value
Name	Equal to	#Smith, John#

 

Range filter example:

Multiple value selections cannot be used with the Range operand; however, commas can be used within range separators for a lookup.

Column	Operand	Value
Name	Range	From: Smith, Anna To: Smith, Zachary