Limiting Checkbox and Radio Button Choices

Did you know that if you shift-click on a field formatted as radio buttons, you can select more than one choice? Even if *you* don’t know this, chances are some day some *user* will figure it out. Yuck. Here’s a short-but-sweet technique to deal with the problem. And you’ll see how to apply the same concept to do some cool stuff.

Just in case you think I’m foolin’, here’s a picture. You’re definitely not supposed to be able to do this:

A FileMaker field formatted with the “Radio Button” style with two buttons turned on at once.

## Where One Is Expected, Two Are Checked

Before I show the solution, it might help if I remind you how radio buttons (and checkboxes) work in FileMaker. If you turn on just “Male” in the database above, the `Gender` field will have this in it:

Male

Makes sense. If you turn on *both* `Male` and `Female`, FileMaker needs to figure out how to put *two* values in one field. It accomplishes the seemingly impossible by putting each value on its own line. So under the hood, the field has this in it:

Male
Female

Every time you check off a new item, it gets added to the end of the list.

>Note: In case it wasn’t obvious up top, you can do this with *any* field that is formatted as a radio button. Just hold down the Shift key and start clicking. Each item you click on turns on — and the other items don’t turn off. Of course if you wanted this kind of behavior, you would have chosen the Checkbox style instead. So chances are you don’t want this to happen.

## Stop the Madness

Of course, there are probably lots of ways to keep your users from doing this. You could, for instance, use a Validation calculation. But that just gets FileMaker to bark at your user when he makes a mistake. I always favor *preventing* a mistake to complaining about one after it has happened. So how can we *stop* the user from choosing the second item in the firs place?

The trick is to use an Auto-Enter calculation. Remember that when a second item is selected, it is added to the *end* of the list. FileMaker doesn’t turn off the other selected items, but you can. Just add this Auto-Enter calculation to the field you want to protect:

GetValue(Self; ValueCount(Self))

Also, make sure you turn *off* the “Do not replace existing value (if any)” checkbox for this Auto-Enter calculation. This formula tells FileMaker to grab the last value in the field and throw out all the rest. And by *value* I mean “line.”

>Note: If you’re not using FileMaker Pro 9 or later, you won’t be able to use the fantastic `Self` function shown here. Instead, just change `Self` to the name of the field you’re trying to protect against double-entry. Likewise, if you’re not using FileMaker Pro 8 or later, you won’t be able to use the `GetValue` function. In that case you’ll have to use `RightValues` instead, and make sure to remove the trailing carriage return from its result.

When your user surreptitiously Shift-clicks the radio button, FileMaker adds her choice to the end of the list. Then the Auto-Enter calc kicks in. It keeps that last line (the one just clicked) and tosses out all the rest. The result is a radio button that works just like she didn’t have the Shift key down.

## More Tricks with Values

You can extend this technique in at least one interesting way. Suppose you have a field formatted as a *checkbox set* on your layout. Normally, of course, this means the user can pick as many items as he wants. But your instructions are to “choose *three*” from the list. Or *ten*. Or *two*. Or whatever makes your socks go up and down.

There are actually two different calculations that will help with this. Here’s the first:

Let ( vals = RightValues(Self, 3), Left(vals, Length(vals) – 1 ))

This calculation works a lot like the radio button version above. It keeps the last three and tosses out the oldest. In this case, though, since we want several values, we can’t use the `GetValue` function. Instead, we use `RightValues` to grab the last three. `RightValues` always tacks a carriage return on the end of the line, so we have to get rid of it to keep things clean. That’s what the whole `Left(vals, Length(vals) – 1))` thing is all about.

Here’s how it looks in action:

Because the calculation keeps the *last three* values, you can keep clicking as much as you want. FileMaker leaves the last three items checked, and clears the X on the oldest as you go.

If you use this calculation instead:

Let ( vals = LeftValues(Self, 3), Left(vals, Length(vals) – 1 ))

Then FileMaker keeps the *first* three values. When you try to click a fourth, it simply refuses to accept it. (Truthfully, it accepts it, updates the field, then promptly removes it because of the Auto-Enter calculation, but from the user’s perspective, it simply won’t allow a fourth choice.) You can see this version in the flesh here:

These techniques can help prevent bad data entry, and make your database more usable. Let us know if you discover any other interesting applications.

Leave a Comment

25 thoughts on “Limiting Checkbox and Radio Button Choices”

  2. @sam: I’m with you there man, but I have to think it is a “feature.” It has been this way at least since FileMaker 3.0. Anybody older than me care to comment on radio buttons in 2? 🙂

  3. This is great! Thanks.

    I have a related issue and I was wondering if you could help.

    Our value list comes form a related database based on active clients. Let’s say we have 5 items checked on the list of items. Then you deactivate one client in the related db, which hides them from the checkbox set, but if you look at the field as editable field, all the previous clients show up. So, I cannot calculate a number of accounts my account executives are working on.

    Thanks in advance!

    Agnes Riley

  4. @agnes: It might make sense to add a little more relational magic to this system. A multi-value checkbox field is great for things like color choices or preferred contact methods (in other words, simple attributes of something in your database). But in your case, the checkbox represents an association between two entities: account execs and clients. This has a lot of drawbacks, one of which you mention here.

    I don’t know enough about your database to say exactly what should be done, but I think you might be well served by a new table between Account Execs and Clients that maps this association. A so-called “join table” with the Account Exec ID and the Client ID. This makes it easy, for instance, to go to the related records from a client and delete them to remove that client from all associated execs.

    I know this is vague…it is hard to give a concrete solution here. Feel free to use the Request Our Help button on the home page to send more details.

  5. Cool trick! This technique can also be used to deal with the problem of users selecting ‘Paste’ from the ‘Edit’ menu and pasting bad data into a Pop-up Menu in FileMaker Pro. The formula below will clear out any bad data (data not in the valuelist) where ‘GENDER’ is the field name and ‘GenderChoice’ is the name of the valuelist. Thanks Sixfriedrice, you guys rock!

    If(
PatternCount ( ValueListItems ( Get ( FileName ) ; "GenderChoice" ) ; GENDER );GENDER;""
)

  6. Using the RightValues function will add an extra carriage return to the field… undesirable in some situations.

    The alternative for radio buttons is to use GetValue ( Self ; ValueCount ( Self ) ), instead of RightValues, assuming you have FileMaker 9.

    Checkboxes are a little more complicated, but the same could be accomplished with a recursive function specifying either a range or a total to provide flexibility, or simple text manipulation to remove the last carriage return.

    Great tips!

  7. Geoff:

    A user pointed out this “shift-click” feature on radio buttons to me in FileMaker Pro 2.1! (I guess this makes me older than you!) I’m reasonably certain FMI does not consider it a bug, but am not sure the rationale for allowing the behavior.

    Thanks for the fresh approach to dealing with this issue.

  8. @Jonathan – Thanks for the tip. RightValues does indeed add a carriage return to the end of the value. Changing the functions to read:

    Substitute( RightValues( Self ; 1 ) ; ¶ ; "")

    would handle that errant carriage return in any of the above functions. You can also change to:

    GetValue( Self ; ValueCount( Self ) )

    To alleviate the hassle of dealing with the carriage return if you would like. Thanks again Jonathan.

  9. @Rob – That’s a gem. Validating the field against the value list during data entry is a get way to tackle that problem. I do believe I remember the phone rep who showed you that little problem 🙂

  10. @Jonathan, @Jesse: I chose RightValues for maximum compatibility (GetValue was not available in 7.) As for the errant carriage return — at first I had code to remove it in place. But then I was testing things and noticed that FileMaker put the extra carriage return in the field for a normal radio button as well, so for consistency I left it in.

    Now I’m testing again and I see I was completely nuts. It does not put a carriage return in with a radio button. I must have had some errant data in my field or something. That’s what I get for writing at 2:00 in the morning. 🙂

    Thanks for the correction. I have revised the code accordingly.

  11. Thanks for the tip. I wondered if it was possible to inhibit the changed behaviour in find mode. It is. However, I found that it was pretty useless to be able to check multiple items in radio buttons as this does an AND search.

    I then wondered if your technique could be used to cure another FM problem. I’ll give an example. I have a field ‘Location’ which has radio buttons (among others) of ‘West’ and ‘South West’. Trouble is that when trying to find ‘West’ I also get the ‘South West’ entries. So, I tried to adapt your technique to put “==” in front of ‘West’ (or any other selection) to do an exact search. I can’t get it to work. Can you?

    BTW, the first field had a zippScript Auto-enter calculation. Adding your functionality did not stop zippScript functioning.

  12. @norman: To do an OR search on radio buttons, just create multiple find requests, and select a different radio button in each. See this article for more info.

    As for your other question, I don’t think you can get the auto-enter calculations to do their thing in find mode. I completely see what you’re after but it won’t work with an auto-enter calc. I think your best bet is to script the find. Something like this:

    
Enter Find Mode [ Pause ]
Set Field [ My Radio Button Field ; "==" & My Radio Button Field ]
Perform Find []

    This script will go do find mode, pause so the user can enter request info, then put quotes around each item in the radio button field and perform the find.

    Hope this helps!

  13. The shift key can also be used to deselect any value in a radio button field. Just hold the shift key and click on an already selected radio button and the selection gets deselected. There seems to be many people ignoring that…

  14. Cool advice.

    I was wondering if you could do something similar to auto-enter a radio button value when creating a new record. 90% of the records in my db will have one particular value, and I’d like to have it auto entered. that way you only have to click a radio button for the small 10% minority of records.

  15. @tom: You absolutely can. Under the hood, a radio button field is just a normal field. When you click one of the radio buttons, FileMaker “types” the associated value into the field. So to get what you want, just use the Data option in the Auto Enter section when setting up your field.

    For instance, if your value list items are “Windows” and “Mac,” put “Windows” in the Data box. Now whenever a new record is created, the field will get “Windows” plugged in, and that radio button will show as selected.

    Geoff

  18. @nkhil: I’m not exactly sure what you mean. I’m guessing you have a checkbox field, and a button on your layout. You want a click on the button to uncheck two of the checkboxes. Is that right?

    If you want the button to clear all of the checkboxes, that’s a breeze. Just clear the field. For instance, if your checkbox field is called Colors then you can use this simple step:

    Set Field [ Colors; "" ]

    If the field has several checkboxes, and you want to clear just two specific values, then it gets a little more complex. As mentioned in the article, FileMaker stores the data in the field with each checkbox value on one line. So you need to find a way to remove specific lines of text from the field. You can do this with the Substitute function. For instance, if you want to uncheck Red and Blue from your Colors field, you could essentially do this:

    Set Field [ Colors; Substitute(Colors, "Red", "") ]
Set Field [ Colors; Substitute(Colors, "Blue", "") ]

    That two-line script says to replace the word “Red” in the field with nothing, and then do the same with “Blue”.

    First, you can streamline this a little if you want by doing both substitutions in one step, like this:

    Set Field [ Colors; Substitute(Colors; ["Red", ""]; ["Blue", ""]) ]

    The Substitute function lest you pass in multiple pairs like this, and it will replace them all.

    But you also have another problem. Suppose your Colors field has these values:

    Red
Green
Blue
Blueish

    When your script removes the word “Blue” from this value, it will find it in two places (“Blue” and the first part of “Blueish”). If this is a problem for your data set, you’ll have to protect against it. You only want to replace “Blue” when it is on a line all by itself. In other words, when there is an ¶ right before it and another ¶ right after it. So you can almost get away with this:

    Set Field [ Colors; Substitute(Colors, "¶Blue¶", "¶") ]

    This version says to replace “Blue” when it is preceded and followed by new lines with a single return, effectively wiping it out. But this version is still not quite right. What if “Blue” is the first item in the list? Then it won’t have an ¶ before it. Likewise, if it is the last item, it won’t have one after it. So you have to get creative: force your data into the form you want, do the substitution, and then clean it up when your done. This script does just that:

    Set Field [ Colors, ¶ & Colors & ¶ ]
Set Field [ Colors, Substitute(Colors; "¶Blue¶" "¶") ]
Set Field [ Colors, Substitute(Colors; "¶Red¶", "¶") ]
Set Field [ Colors, Middle(Colors, 2, Length(Colors) - 1) ]

    As before, this script could be written in fewer steps if you want (in fact, it can be written in one step) but I wanted the steps to be explicit here.

    Hope this helps.

    Geoff

  20. Disregard my last comment. The issue I was having seems to be when the field is in a portal. Suggestions?

  21. I would like to know if the following is possible? Im new to fm

    I created a link between two layouts and I would like the link to change colors when it is selected. How do I achieve this affect in FP9?

    I also have three radiobuttons which contain names. I would like to know if its possible to automatically select the correct radio button if the user name that logins matches one of the names in the radio button selection?

  22. ZSmith:

    1: If you mean you want the link to change color as you click on it, I don’t think its possible in any straight forward way. If you mean its color should be different after you have clicked it, then you can do this with Conditional Formatting. You just need some way to keep track of whether or not the link has been clicked. This could be done with a global variable, a value stored in a field somewhere, etc…

    For example, when they click the button, you could run a script that sets a variable called $$link_clicked to true, and then go to the layout. Then apply conditional formatting to the button with a condition formula of:

    $$link_clicked

    And apply an alternate text color. Should work just fine.

    2: For your radio buttons, you just need to set the value in the field to the current user’s name, which you can get from the Get(AccountName) function. You can put this in an auto-enter calculation, or set the field with a script.

    Hope this helps 🙂

    Geoff

  23. Wow… just the simple revelation that multiple items are understood by FM to be “return” delimited … (I had tried all kinds of things, like assigning lists to the cell, etc… to no avail.)

    solved my prob. Thanks!
    Troy

  24. I have a small db and I have a value list with radio buttons. Is there a script that can be written to reset the radio buttons back to a value on the value list?

  25. Is there a way to get this technique to work with a drop down list?

    Drop down lists with auto-complete enabled make it easy to pick a value without your hands leaving the keyboard. When using this technique on a list, the calculation is not applied until you click out of the field and the value that is left in the field is the bottom most one in the list not necessarily the last one selected. Doesn’t matter if they are shift-click selected or command-click selected.

    How can you capture the values as they are selected?

    Thanks! Lindee