Define a self join relationship linking your faculty table to itself by the area field. Then a validation calculation can pop up an error if you select a second area head for the same area.
Find "Faculty" in Manage|Database|Relationships, click to select it and then click the button with two green plus signs to create a second table occurrence of the Faculty table. Double click this new occurrence and rename it "Faculty Same Area". Now you can set up this relationship between the two:
Faculty::Area = Faculty Same Area::Area
It would be better if your field were not a radio button field, but instead a single value check box format where you click the box once to select the record as an area head and click it a second time to clear the selection. I'll work from that set up here as othewise, you'd need to add an extra calculation field to use with the validation expression I'm about to suggest:
Now you can define this validation calculation in Field options for your check box formatted field:
Self = "no" or ( Count (Facutly Same Area::checkboxField ) < 2 )
You can also use a script triggered by the OnObjectValidate trigger that uses with this same expression inside an IF step to check for an error and then your script can handle the error in possibly a more user friendly fashion.
Thank you so much. It's amazing how simple something can be when you really know what your doing. This worked great and has broadened my understanding of uses for self joins. Now if I can just get it to give me a option to automatically uncheck the old area head it will be perfect. I think I can accomplish this behavior.
If you have a custom dialog box you can set a variable using the function Get(LastMessageChoice) then evaluate the variable as to what you want to do. (The options are 1 2 3 depending on how many buttons you lable)
As a result of selecting "change area head" you could then perform a find and set the radio buttons field value then set the value of the new person.
You may have to pass the area value to a variable in order to find the right area head but that shouldnt be a problem.
That's exactly what I was thinking, but I'm not sure how to get the custom dialog to trigger on a failed validation. I can't seem to find the script trigger OnObjectValidate that PhilModJunk referenced, it's not in my list of script trigger choices. I'm still in V10 is this something that was added in V11?
Phil mentioned the OnObjectValidate Script Trigger.
Didnt completely read your message.
One way to do this is to make the validation a scripted validation attached to the OnObjectSave or OnObjectExit script triggers.
Rather than on the validation tab of the field itself. This does open up some problems enforcing it in all places that the field is editable in your application. (I have a number of these scripted validations in one of my solutions and I use a pallet layout to copy objects from in order to ensure that the triggers are attached)
OnObjectValidate is new to FileMaker 11. You won't have that option in FileMaker 10. In 10, if you want to use a trigger, you'd need to use OnObjectExit and not use the validation setting in field options. This puts the responsibility on you, the developer to apply this consistently if you have more than one layout where this field can be edited.
When I use this option with FileMaker 11, I set up the validation rule as an "insurance policy" to make sure that any lapses in interface design on my part don't allow bad data to be entered, but then use OnObjectValidate and other interface and auto-enter techniques to catch or prevent the error before the less friendly validation rule can kick in.