6 Replies Latest reply on Jul 8, 2011 8:17 AM by philmodjunk

    Unique radio button values spanning multiple records



      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.

        • 1. Re: Unique radio button values spanning multiple records

          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.

          • 2. Re: Unique radio button values spanning multiple records

            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.

            Thanks Again!Smile

            • 3. Re: Unique radio button values spanning multiple records

              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.



              • 4. Re: Unique radio button values spanning multiple records

                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?

                • 5. Re: Unique radio button values spanning multiple records

                  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)

                  • 6. Re: Unique radio button values spanning multiple records

                    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.