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
