How to Filter Validation and Picklist Cursors

You can set up filters to restrict the primary key values in the Validation Cursor when a field is validated and in the Picklist Cursor when Picklist Help is displayed for a field.

VPM Enterprise provides two ways that you can set up such filters, depending on the cursor data source (table, view or CursorAdapter).. In both methods you enter filter information into properties of the control that is bound to the field. Your filter information is then used to filter the Validation Cursor when the field is validated and to filter the Picklist Cursor when Picklist Help is displayed.

Method 1 – SET FILTER TO – Primarily for Tables Only

Method 1 uses the Visual FoxPro SET FILTER TO command with a filter expression that you enter into one or both of the following two properties of the control that is bound to the field:

·       cValidFilterExpression Property: The expression entered into  the cValidFilterExpression property filters the Validation Cursor.

·       cPicklistFilterExpression Property: The expression entered into the cPicklistFilterExpression property filters the Picklist Cursor.

Tip – Primarily For Tables Only: The expressions that you enter into these properties are applied after the Validation and Picklist Cursors are created. That’s fine for tables. It’s acceptable for views and CursorAdapters based on tables with few records. However, for views and CursorAdapters based on large tables, Method 1 is simply too slow, and you wouldn’t want to use Method 1 in those cases.

Method 1 Example

Let's say that you are creating a customer tracking system for a multinational company. You have a table of postal codes but only want to show in Picklist Help (or use for validation purposes) those postal codes that are valid for the country of the customer whose postal code (foreign key) is being entered. In this case, you would need a country code in the postal codes table that you could filter on, using the country code from the current customer record for which a postal code is being entered.

In this example, you can enter the following code into the GotFocus event of the field’s control to filter the Validation and Picklist Cursors:

This.cValidFilterExpression = “cCountry = customers.cCountry”

This.cPicklistFilterExpression = “cCountry = customers.cCountry”

Where “cCountry” is the name of the country field in the Validation and Picklist Curors and in the Customer table, and “customers” is the alias of the Customers table.

Tips: (1) Because we are using fields from the cursor that contains the field, we must create the filter expressions programmatically. (2) It is not necessary to use an alias in your code for fields from the Validation and Picklist Cursors. (3) You can always put your code to create filter expressions in a control’s GotFocus event.

Method 2 – Array - Views and CursorAdapters Only

Method 2 cannot be used for tables. It is for views and CursorAdapters only. Method 2 is designed to maximize the speed of filtering Validation and Picklist Cursors based on views and CursorAdapters.

View and CursorAdapter Filters

Method 2 requires you to set filters on the fields that you will use to filter the Validation Cursor and Picklist Cursor.

·       View Filters: In the case of a Validation Cursor or Picklist Cursor based on a view:

·       You set up a filter in the View Designer on a field that you will use to filter the Validation or Picklist Cursor using the GETstring variable name just as described in View Filters.

·       However, if the field already has a filter set on it, you do not need to create another filter using the GETstring variable name.

·       CursorAdapter Filters: In the case of a Validation Cursor or Picklist Cursor based on a CursorAdapter:

·       You set up a filter in the WHERE clause on a field that you will use to filter the Validation or Picklist Cursor using the GETstring variable name just as described in CursorAdapter Filters.

·       However, if the field already has a filter set on it, you do not need to create another filter using the GETstring variable name.

Control Array Properties

After you create the view and/or CursorAdapter filters, you enter information about the filters into the following two array properties of the control that is bound to the field. The filter variable information is used to create the Validation and Picklist Cursors to select only filtered records.

·       aValidFilterVariables Property: An array of the filter variable names and variable values. Each array row defines a filter.

·       aPicklistFilterVariables Property: An array of the filter variable names and variable values. Each array row defines a filter.

The arrays are two-dimensional with the first column holding the filter variable name and the second column holding the filter variable value.

The information that you programmatically enter into these arrays is applied in filtering records when the Validation and Picklist cursors are created, not after they are created.

Method 2 Example

To illustrate Method 2, we’ll use the same facts as the Method 1 example above, except:

·       The Validation and Picklist Cursors will be based on a view.

·       The Validation and Picklist Cursors contain an additional “cRegion” field that we want to add to the filter.

·       The Validation and Picklist Cursors already have a Find<field> filter set on the cCountry field as part of the set up for the SQL Find Form.

The first thing that you need to do is set up a filter on the cRegion field in the View Designer for the cRegion field using the GETstring variable name. We’ll use the variable name “GETcRegion”.

In this example, you can enter the following code into the GotFocus event of the field’s control to filter the Validation and Picklist Cursors:

DIMENSION This.aValidFilterVariables[2,2]
This.aValidFilterVariables[1,1] = "FINDcCountry"
This.aValidFilterVariables[1,2] = customers.cCountry
This.aValidFilterVariables[2,1] = "GETcRegion"
This.aValidFilterVariables[2,2] = customers.cRegion

DIMENSION This.aPicklistFilterVariables[2,2]
This.aPicklistFilterVariables[1,1] = "FINDcCountry"
This.aPicklistFilterVariables[1,2] = customers.cCountry
This.aPicklistFilterVariables[2,1] = "GETcRegion"
This.aPicklistFilterVariables[2,2] = customers.cRegion

More:

Century Handling