How to Determine the Criteria Operator and Example Entries for a View Filter

Your Field Name entry for a filter on the Filter page depends on which of the five types of filters you are setting. The Field Name entry is either a primary key, foreign key, SQL Find Form field or a field you need to filter.

Your Criteria operator and your Example entries for a filter depend on the data type of the field or expression you enter in the Field name column. The following table shows the Criteria operator and Example entries you must use for different field / expression data types.

 

Field / Expression
Data Type

View Filter Criteria Operator
and Example Entries

Character

Like ?VariableName

Character, filtered on a range of values

Enter the following two Filter lines joined with Logical "AND":

>=  ?RangeVariableName_cMin

<=  ? RangeVariableName _cMax

Date or Datetime

Enter the following two Filter lines joined with Logical "AND":

>=  ? VariableName _tMin

<=  ? VariableName _tMax

Numeric, Float, Double, Integer

Enter the following two Filter lines joined with Logical "AND":

>=  ? VariableName _nMin

<= ? VariableName _nMax

 

(1) Where VariableName is one of the following filter variable names depending on which of the five types of filters you are setting:

·       PKValue – primary key filter.

·       ParentPKValue – foreign key filter.

·       RI<fieldname> - multiple foreign key filter.

·       Where <fieldname> is the name of the foreign key field or any field in the foreign key expression.

·       Example: For a character foreign key field named “TITLE” in the view, the entry would be “Like ?RITITLE”.

·       FIND<fieldname> - field to be used for a SQL Find Form filter.

·       Where <fieldname> is the name of the filtered field.

·       Example: For a character filtered field named “EMPLOYEEID” in the view, the entry would be “Like ?FINDEMPLOYEEID”.

·       Getstring -  developer definable filter.

·       Where “string” identifies the variable.

·       Example: For a charcter filtered field named “COUNTRY” in the view, the entry could be “GETCOUNTRY”. (Actually, “GETCOUNTRY” could be any variable name that begins with “GET” and makes the variable name unique.

(2) And where RangeVariableName is either the FIND<fieldname> or the Getstring variable name. A character field type filtered on a range of values only makes sense in the context of these two types of filters.

Tip - ?: Notice that the variable name for a filter is preceded by a "?". The "?" tells Visual FoxPro that the entry is a variable, not a constant or literal.

Tip – Logical: When you create more than one filter using the special variable names, join the filters with the "AND" operator in the Logical column.

Tip – Case-Insensitive: To make a filter case-insensitive the UPPER() function must be used. For example, a FIND filter can be made case-insensitive as follows: UPPER(<field>) LIKE UPPER(?FIND<field>).

Character Criteria Operators

When creating filtered views, you use the Filter criteria operator "Like" when you want to allow partial matches or "==" when you want an exact match for a character data type.

VPME will append the "WHERE Wildcard" character entered on the Data Sources form in the Data Builder to the partial character value to make the partial match succeed. For example, for SQL Server, the WHERE Wildcard character is “%”. See Data Sources.

Forms - NoDataOnLoad Property

When you use a parameterized view with a form, you must set the parameterized view's Data Environment cursor NoDataOnLoad property to .T. to prevent Visual FoxPro from displaying parameter prompts when a form loads.

FIND<field name> - Handling Table Fields with Same Name

Note that in the parameter variable "FIND<field name>", "field name" is the name of the view field that is being filtered, not the name of the table field. If two fields with the same name from different tables are included in a view, Visual FoxPro will name one field "<same name>_a" and the other "<same name>_b". Before you can include one of those fields in a FIND<field name> filter, you must know the view field name to include.

Tip – View Field Names: After you create a view through the Data Builder, you can find the names of the fields in the view on the Data Builder Fields page. You can then reenter the View Designer and set up the FIND<field name> parameters that you need with the correct view field names.

FIND<field name> - Character Case

The SQL Find Form provides for the entry of parameter values and performs a REQUERY on the InitialSelectedAlias view cursor. The REQUERY creates a cursor with records that match the parameter values entered on the SQL Find Form. If you want a character field's parameter value to be handled without regard to case (that is, case insensitive), use the UPPER() function in the field's view filter definition in the View Designer. Use the UPPER() function for the Field Name and Example entries. See the following View Filter Example.

More:

View Filter Example