Unique radio button values spanning multiple records
I've tried finding an answer to this but no joy. I'm new here so sorry if I've missed it some how.
I would like to find a way to validate an entry, or limit a radio button selection for a group of related records in a table. What I have is a table of about 50 faculty members, each member belongs to an "area" in our department ( brass, woodwinds, piano, voice, percussion) five choices defined in a field using a value list. Only one faculty member can be the head of their area at a time. I have a yes / no radio button for "Area Head" in the table.
Is there a method that will only allow one "Yes" for the related records of each "area"? In other words there can only be 5 area heads at one time ( five areas, one head each) Not sure if this can be done through a validation calculation or not. Maybe another way? In my ideal world a custom dialog would pop up alerting the user that they've attempted to add a second "Area Head" and then be given a choice to cancel or proceed. Proceeding would make the current record the area head and change the existing area heads field to "No".
Any suggestions or directions to pursue would be greatly appreciated.