View Filter Example

Let’s build a view filter for a hypothetical “EMPLOYEES” view with the following fields:

·       EMPLOYEEID – the view’s primary key; integer data type.

·       POSTALCODE – a foreign key to the primary key of  a table of valid postal codes; character data type.

·       COUNTRY – a foreign key to the primary key of a table of valid country names; the COUNTRY field is also to be used to filter reports by country; character data type.

·       FIRSTNAME, LASTNAME – fields to appear on a SQL Find Form; character data type.

Here are the view filters we need to enter on the Filter page of the view’s View Designer:

·       PKValue Filter: The EMPLOYEEID field is the primary key for the EMPLOYEES view. The EMPLOYEES view will be used as the Initial Selected Alias of a Related Forms parent form and as the Validation view for foreign key fields in other views that contain the EMPLOYEEID field. Therefore, we need a PKValue filter on the integer field EMPLOYEEID. The PKValue filter in the View Designer will consist of the following two lines:

·       EMPLOYEEID >= ?PKValue_nMin AND

·       EMPLOYEEID <= ?PKValue_nMax

·       ParentPKValue Filter: The POSTALCODE field is a foreign key with a Referential Integrity relationship to a table of valid postal codes. Further, the EMPLOYEES view will be the InitialSelectedAlias for a Related Forms Child form that will display employees who live in a particular postal code for the postal code displayed on the parent Related Form. Therefore, we need a ParentPKValue filter on the character field POSTALCODE. The ParentPKValue filter in the View Designer will consist of the following:

·       POSTALCODE Like ?ParentPKValueD

·       RI<fieldname> Filter: The COUNTRY field is a foreign key with a Referential Integrity relationship to a table of valid country names. Therefore, we have a multiple foreign key situation, and we need a RI<fieldname> filter on the character field COUNTRY.  The RI<fieldname> filter in the View Designer will consist of the following:

·       COUNTRY Like ?RICOUNTRY

·       FIND<fieldname> Filter: When the EMPLOYEES view is used as the InitialSelectedAlias for a form, we want the form to use a SQL Find Form that allows users to use the FIRSTNAME and LASTNAME fields to select the records to fill the cursor. Therefore, we need FIND<fieldname> filters on those two fields.  The FIND<fieldname> filters in the View Designer will consist of the following two lines:

·       UPPER(FIRSTNAME) Like UPPER(?FINDFIRSTNAME) AND

·       UPPER(LASTNAME) Like UPPER(?FINDLASTNAME)

·       GETstring Filter: The COUNTRY field will be used to filter reports by an employee’s country. We need a variable to store the country currently selected to filter the report. We must use a GETstring variable name for our filter so that VPME will know that the filter is a developer filter and not part of VPME’s own functionality. We’ll use the variable name “GETCountry”. The GETstring filter in the View Designer will consist of the following:

·       COUNTRY Like?GETCountry

More:

Technical Description