trouble with duplicate records

[jimsisco]

Once again I am humbled by what should be easy.

I created a new table called debits


name type width decimal

Transno Numeric 6 0
Recno Numeric 6 0
Tblno Numeric 2 0
Clientno Character 6 0
Amount Numeric 16 2


the following index was setup

Recno TRANSNO+RECNO Ascending Unique only


when I open the default browse and begin to enter records,
it allows entries that are not unique.
For instance, I am able to enter two records with
transno=1 and recno=1
I was expecting the index to prevent that.
what did I fail to do?

Jim

[Stan Mathews]

Re: trouble with duplicate records

An index, unique only, has nothing to do with enforcing uniqueness. It shows only single instances of records with the same key value.

Uniqueness Test for a Field

If you want to prevent duplicate values from being entered into a field, use one of the two Uniqueness test rules: Value of field must be unique, or Value of expression must be unique.

In the Control Panel select the table and click Table > Edit Field Rules.

Display the Validations tab.

Select an entry from the Uniqueness test list. The options are:

"None" - the default

"Value of field must be unique" - Select this setting to specify that any value you enter must not already exist in the same field in another record. If you try to save a record that has a duplicate value in a unique field, an error message appears listing the duplicate value.

"Value of expression must be unique" - Use this setting when you want to build an expression to determine if a value is unique. The field for which this rule is defined is usually included in the Unique expression. The value that results from this expression must be unique for each record in the table; if not, the record cannot be saved.

If you selected "Value of expression must be unique" in step 3, enter an expression in the Unique expression field. The expression must evaluate to a unique value of the same type as the table field. Click to use the Expression Builder to help you define the expression.

Uniqueness Test for Multiple Fields in a Record

There are two techniques to prevent duplicate values from being entered into multiple fields in a record. This technique also applies to calculated fields.

Testing each field:

In the Control Panel select the table and click Table > Edit Field Rules.

Display the Validations tab.

For each of the multiple fields:

Select "Value of expression must be unique" in the Uniqueness test list.

In the Unique expression field enter an expression that evaluates to a character value and that includes all of the multiple fields that you want to test.

For example, if you wanted to make sure that the firstname and lastname combination in any record was unique, the Unique expression would be firstname + lastname.

Suppose you defined a calculated field named fullname that was defined as alltrim(firstname) + " " + alltrim(lastname), then this would be the Unique expression that you define for the firstname and lastname fields.

Testing the combined value:

In the Control Panel select the table and click Table > Define Indexes.

Create a new index that uses the expression you want to test. For example, the index might be named fullname and use the expression alltrim(firstname) + " " + alltrim(lastname).

Click Build Indexes and Field Rules.

Display the Events tab of the Field Rule Editor.

Select the Record Events radio button.

Select the "CanSaveRecord" event from the list box.

Enter a script that tests whether the index value exists. Note that the KEY_EXIST() function:

references the name of the index created in step 2 in its first argument

uses the same expression as the index as its second argument, with the exception that each field name is prefaced by " tbl. ".

tbl = table.current()

if key_exist("fullname", alltrim(tbl.firstname) + " " + alltrim(tbl.lastname) ) then

cancel()

ui_msg_box("Error", "The combined values of First Name and Last Name must be unique")

end if

[Tom Cone Jr]

Re: trouble with duplicate records

Jim, the "unique" setting for an index does NOT assure uniqueness or operate as a constraint preventing the entry of duplicates. Check the help file discussion for details but basically what this setting does is exclude records with duplicate keys from being DISPLAYED.

It's also problematic to define the index as the sum of two numeric fields. 2 + 3 happens to equal 3 + 2 and 1 + 4 and 4 + 1 and... and ...
if you see what I mean.

[jimsisco]

Re: trouble with duplicate records

thanks guys!

I'm in the process of switching from Paradox to alpha.
I'm really excited about alpha. I've studied the a5 made easy and xbasic for everyone, but i'm still a long way from being skillful.
I keep expecting it to act like paradox.

I have the references in the alphaFiveHelp that you mentioned.

thanks!!!

jim