Defining unique fields

[Beno]

Hello.

I'm defining a new file and I would like to have two fiels as identifiers (unique fields) for that file. How is that possible. I don't want them to be unique separetely, but that their association is unique. I need your help. I'm thinking about some calculated verification, bit it really looks like a "trick". Is there some other ways ?

Thanks.

[Jim McKee]

Hi Benoit ...

Without knowing in which order data is entered, or whether a field is allowed to remain empty, I think this should cover all of the possibilities:

1. Create a calculated key field:

"c_key" = fieldA & "_" fieldB

2. Create a self relationship of c_key to c_key. Call it "self c_key"

3. Create a constant calculation, i.e., a calculation field with the number 1 entered into the calc definition window. Call it "constant".

4. In the Validation windows for fieldA and fieldB, select the "Validated by calculation" option, and enter the following calculation:

Case(not IsEmpty(fieldA) and not IsEmpty(fieldB) and not IsEmpty(self c_key::constant), 0, 1)

This will allow two records to contain the same value in, say, fieldA, while fieldB remains empty. The validation will only be enforced when both fields contain data, and will not allow the same data in fieldA and fieldB in more than 1 record.

You might want to select the "Strict: do not allow user to override validation", and also enter a custom message for the user if the test for uniqueness fails.

[jscooper]

Try something like this:

Create a calculation field that concatonates the two values:

FieldsAB = FieldA & "_" & FieldB

Make a self joining relationship, using FieldsAB:

SelfJoin - FieldsAB := FieldsAB

Make sure each record has a unique identifier apart from the double-ID you want to use:

ID - Auto-enter Serial Number

Now, for fieldA and fieldB, make this the validation:

Count(SelfJoin::ID) = 0 or (Count(SelfJoin::ID)=1 and SelfJoin::ID = ID)

This should do the following:

If no record exists with the same fieldAB value, then count(SelfJoin::ID) will equal 0 and the test will pass.

If a record DOES exist with the same fieldAB value, make sure it's the current record (this is in case you want to change an existing value).

I only did a quick test of this, so there may be situtations where it wouldn't work. But I can't think of any offhand.

Jeff

[Beno]

Thanks a lot. This is a calculated verification. So it seems that it is not possible to do it through the definitions of the two fields themselves. I'll go for that. I just wanted to know if there were some ways to avoid the calculated verification. See you soon ;o)