Candidate Keys – Preventing Duplicate Values

Tip: This section applies to tables, views and CursorAdapters. This section’s topic is not one on which you need to spend much time as you are learning VPM Enterprise, but you should be aware that the functionality covered exists so that you can use it when you need it.

You can use the VPM Enterprise Data Builder to set up duplicate value checking for candidate keys in a table, view or CursorAdapter when a value for the candidate key has been changed and the record is saved.

A “candidate key” is a field or combination of fields that can be used to uniquely identify a record in a table, view or CursorAdapter. A table, view or CursorAdapter can have one or more candidate keys. One of the candidate keys is selected as the primary key.

When you designate a primary key for a table, view or CursorAdapter in the Data Builder, VPME automatically prevents duplicate primary keys when records are saved or when a primary key value is changed.

If you want to prevent duplicate candidate key values in a table, view or CursorAdapter, you can use the Candidate Keys page on a table, view or CursorAdapter Edit page to provide the information VPME needs to prevent duplicate values in a candidate key.

Ø To prevent duplicate Candidate Key values

1.   Create Search View: The first step is to create a view in the Data Builder that will be used to search for a value that duplicates the current value of the candidate key. The view must be based on the table or view that contains the candidate key and must contain the candidate key field(s).  This will not be a view that will be used to update a table in your application. Therefore, the view can be a simple view with only the candidate key field(s) in it. The view should be as simple as possible to speed up the search for duplicate values.

·       Search View Filter: The view must have a filter set on the candidate key field(s) in the following format:

CandidateKeyExpression = ?GETvariablename

where CandidateKeyExpression is the candidate key field(s) and variablename is any name chosen by you. The string “?GET” must prefix the variable name.

If the candidate key is made up of more than one field, the view filters on an expression (field1+field2, for example).

2.   Create Candidate Keys Record: The next step is to create a Candidate Keys record in the Data Builder that provides the information VPME needs to prevent duplicate values in the candidate key. Go to the Candidate Keys page on the Edit page for the table, view or CursorAdapter that contains the candidate key.

·       New: Click the New button to start creating a new Candidate Key record.

·       Description: Enter a description that identifies the candidate key. The description you enter will be used to identify the candidate key in the message that appears if the duplicate check fails when saving a record.

·       View: In the View combobox, select the name of the search view that you created in step 1.

·       Variable: In the Variable text box, enter the name of the filter variablename that you used in the search view.

·       Expression: In the Expression box, enter an expression based on the candidate key field(s) that will resolve to the candidate key value to be used in the duplicate check.

Candidate Key Data

The Candidate Key records that you create are stored in the SDATADDCK.DBF table. The duplicate candidate key check is performed when saving a new record or when a candidate key value has been changed.

The search view is checked for duplicate values, and if duplicates are found, a message is displayed that identifies the candidate key that has failed the duplicate check.




Building Data: Views