CursorAdapter Filters You Need and When You Need Them

When you create a new CursorAdapter, you must set up filters with specific variable names for the CursorAdapter if you plan to use the CursorAdapter in any of the situations described below.

PKValue Filter

The CursorAdapter needs to have a filter set on its primary key field or expression (if the primary key is compound) using a variable named "PKValue" if any of the following three situations apply.

·       Related Forms Parent CursorAdapter: If the CursorAdapter will be the Initial Selected Alias of a Related Forms parent form (see Creating Forms: Related Forms).

·       Validation CursorAdapter: If the CursorAdapter will be used as the Validation CursorAdapter for a foreign key field that uses Referential Validation (see Referential Validation).

·       Foreign Key Descriptive Expression CursorAdapter: If the CursorAdapter is a Picklist CursorAdapter that will be used to display a descriptive expression for a foreign key in a textbox on a form. See Displaying a Descriptive Expression.

ParentPKValue Filter

The CursorAdapter needs to have a filter set on a foreign key field or expression (if the foreign key is compound) using a variable named "ParentPKValue" if either of the following two situations apply.

·       Child CursorAdapter: If the CursorAdapter (a) will be used as a child CursorAdapter on a One-To-Many form or Related Pages form or (b) will be the InitialSelected Alias of a Related Forms child form, and if the foreign key is the link to the parent form.

·       Referential Integrity for Foreign Key in CursorAdapter: If the CursorAdapter has a foreign key for which you want to enforce a Referential Integrity relationship with a parent table, view or CursorAdapter. The CursorAdapter's foreign key field or expression (if the foreign key is compound) will be set up for Referential Validation on the Data Builder Field Integrity page, and it will have its parent table, view or CursorAdapter entered as the Parent RI Cursor. See Referential Integrity for Foreign Keys in Views.

RI<fieldname> Filter - Multiple Foreign Keys

This filter type is used in addition to the ParentPKValue filter when there is more than one foreign key or expression (if the foreign key is compound) in the CursorAdapter.

If the CursorAdapter contains more than one foreign key:

·       Use the ParentPKValue filter (see above) for the foreign key that links the CursorAdapter to its parent for a One-To-Many form, a Related Pages form or Related Forms situation.

·       For each additional foreign key, create a filter using a variable named “RI<fieldname>”, where <fieldname> is the name of the foreign key field or any field in the foreign key expression.

If there is just a single foreign key, the ParentPKValue filter should be used.

This filter type allows the VPME Referential Integrity routine to use the CursorAdapter to update its source table from multiple parents.

FIND<fieldname> Filter – SQL Find Form

If the CursorAdapter will be used as the Initial Selected Alias of a form that uses the VPME SQL Find Form (see Find – SQL), the CursorAdapter needs to have filters set on the fields that will appear on the SQL Find Form using variables named "FIND<fieldname>", where "fieldname" is the name of the CursorAdapter field being filtered.

Tip: FIND<fieldname> Filters Define SQL Find Form: The fields that appear on a SQL Find Form are the fields in a form's InitialSelectedAlias CursorAdapter that are filtered using the FIND<field name> variable. A filtered character field can appear on the SQL Find Form as a single entry (one text box) or as a range (two text boxes). Filtered date, datetime, numeric, float, double and integer fields appear on the SQL Find Form as a range (two text boxes).

GETstring Filter – Developer Definable

If you need to set a filter on a CursorAdapter, use the variable Getstring, where “string” identifies the variable. In other words, use a variable name that begins with “GET” for the filter.

The Getstring filter is not a “required” filter in the sense that it is needed to implement VPME functionality. The Getstring filter variable naming convention allows VPME to identify which CursorAdapter filters are yours and to handle your filters appropriately when performing VPME’s own functionality.

If you use this filter variable naming convention, VPM Enterprise can turn off your filters as needed to perform functionality that uses one of the other four types of filters.

How to Keep a GETstring Filter Active When VPME Wants to Turn It Off

There may be times when you need to define a GETstring filter to be used in conjunction with the other types of filter variables. For example, on a child page of a Related Pages form you may need to use a GETstring filter to limit the records shown to a subset of the child records.

In this situation, the trick is to define the GETstring filter variable before the CursorAdapter is opened or requeried/refreshed using the data handler methods of the form. The GETstring variable values are not turned off if they are defined before the CursorAdapter is opened or requeried/refreshed.

For example, on a child page of a Related Pages form where you want to limit the records shown to a subset of the child records, you can create a GETstring filter that has its variable value set in the Activate event of the child page. That way, the GETstring variable will have its value set before the default code populates the child cursor. The Activate code would be:

 

RELEASE GET<string>

PUBLIC GET<string>

GET<string> = <value>

ThisForm.LockScreen = .T.    && Existing code

This.Parent.Click()                  && Existing code that populates cursor

ThisForm.LockScreen = .F.    && Existing code

RELEASE GET<string>

 

Tip – 5 Filter Types: Notice that only five different variable names are needed to handle the situations listed above. As you will discover in the subsections below, each variable name has a slight variation that depends on the data type involved in the filter definition.

More:

CursorAdapter Select Command Where Clause