Jump to content

Defining unique fields


This topic is 6549 days old. Please don't post here. Open a new topic instead.

Recommended Posts


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 ?


Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.


Link to comment
Share on other sites

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)

Link to comment
Share on other sites

This topic is 6549 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.