Beno Posted February 16, 2004 Posted February 16, 2004 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 Posted February 16, 2004 Posted February 16, 2004 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 Posted February 16, 2004 Posted February 16, 2004 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 Posted February 18, 2004 Author Posted February 18, 2004 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)
Recommended Posts
This topic is 7654 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 accountSign in
Already have an account? Sign in here.
Sign In Now