Validation based on 3 fields

[shaunsund]

I am creating a db that tracks the ethernet ports and their config used on campus. I need to validate that the port # I entered is not a duplicate. Based on the building and room fields. the condidtions that I need to test for are:

can't exist in the same building and/or same room,

but can exist in the same room but different building.

the If (test, result 1, result 2) seems to be the one to use, but I need a way for it to check the room and bldg fields. Would I need a script?

I used FMPro about 2 years ago and re-imersing myself in it, but am stumped at this point.

Thanks in advance.

[Kurt Knippel]

I would start by defining a field that gives you your UNIQUE address: Building & Room & Port.

Then define a self relationship based on this field. This is a relationship to the same file with this field on both sides of the relationship.

Finally you validation can be as simple as:

code:

---

Count ( Self by Address::RecordID ) = 1

---

This means that validation will pass if there is only 1 record (the one you just created) with the same address. Otherwise validation will fail.

I would also suggest that you NOT use field validation, as it will be way to difficult to control. Do this via scripting so that you can validate the entire record.

[Kurt Knippel]

quote:

---

Originally posted by Don Aehl:

Your next suggestion was to use scripting. That probably would work better, but I'd like to make the solution as automatic and foolproof as possible.. But is there a simple way to trigger the validation script automatically without requiring the user to select a "validate" button after each record is entered or edited?

---

There are many, many reasons that I do not like Field Level validation and I greatly prefer scripted record level validation.

I give my users the standard "New Record", "Edit Record" with takes them to a layout where they can only "Cancel" or "Save". Saveing the record will perform the validation and if it fails prompt the user to fix the problem so that the validation will pass.

This is really both the easiest, most automatic and most professional way to accomplish this action.

quote:

---

Also, what action do you recommend taking if the script-valiation fails? Do you just display a message, move the cursor to the invalid field, erase the field or the whole record? What works best in your opinion?

---

It really depends upon the context. Most often I will simply inform the user as to the problem and not allow them to leave the screen (unless they decide to press cancel).

[daehl]

Kurt.

I'm using FMP 5 and I have three fields that (when combined) should be unique for each record. I tried implementing a solution based on your initial advice. It works, sort of. I tried it with the validation in another text field in the record. The validation doesn't seem to work exactly as you described. If the validation returns any number (1 or higher) that indicates a failure. The value 1 appears to indicate that there is another field with a matching combined field. It never does return the value 0 (zero) because I think it evaluates the validation field before it actually adds the record to the database. Anyway, this approach doesn't seem very dependable either, I got a lot of false validations (especially when editing exisiting records).

Your next suggestion was to use scripting. That probably would work better, but I'd like to make the solution as automatic and foolproof as possible.. But is there a simple way to trigger the validation script automatically without requiring the user to select a "validate" button after each record is entered or edited?

Also, what action do you recommend taking if the script-valiation fails? Do you just display a message, move the cursor to the invalid field, erase the field or the whole record? What works best in your opinion?

I greatly appreciate you taking the time to respond. Thanks,

Don Aehl