Click checkboxes to manually select records from a list.
The ability to manually select individual records for filtering is useful and often necessary, but is not as straight forward as you might think.
Your first thought may be to create a field in the table to act as a flag and place it on a list layout as a checkbox allowing users to check the records they want. The problem with this is that when one user selects a checkbox, that record is flagged for all users. So, if you have more than one user wishing to select different records, their selections will interfere with one another.
With a global field, however, the value of the field is session-based: meaning what one user does will not affect other users. But, how do you use a global field, which by definition allows only one value per table, to allow you to select multiple records? With the technique explained below, we will do just that.
To see this technique in action, download the “Free Contact Manager Database” using the form in the sidebar or by visiting the Inspired Business Suite webpage.
Quick Overview
There are four basic steps which are outlined in detail below.
- Create a global field to hold a list of selected IDs.
- Created layered layout objects for the checkbox and use conditional formatting to show or hide the checkbox based on whether the current record’s ID exists in the global field.
- Define a button on the layered layout objects that toggles the checkbox by adding or removing the current record’s ID to or from the list in the global field.
- Create a filter button, “Show Selected,” that either goes to related records based on a relationship built with the global field, or run a find script that loops through the IDs in the global field and finds each record.
See the details of each of these steps below as well as some alternative methods and enhancements.
Step 1 – Create a Global Field
Create a text field in any table. In the field options, set the new field to use global storage as shown in the figure below. The purpose of this field is to hold a list of primary keys of the selected records.
Under the storage tab in the field options, select the “Use global storage (one value for all records)” check box.
Note: This technique could also be performed with a global variable rather than a global field. In this way, no database definition changes would be necessary. However, you would not be able to use the global variable in relationships. So, in Step 4 below, you will need to choose the script option..
Step 2 – Add the Checkbox to the Layout
This is a little bit more complex than it sounds, and there are a number of ways to approach this. I’ll be explaining the method I use in the Inspired Business Suite contact manager which you can download for free using the “Free Contact Manager Database” form in the sidebar or by visiting the Inspired Business Suite webpage.
In short, we will use conditional formatting to show or hide a checkbox based on whether the current record is in the list of selected records. The method I outline here layers a couple of layout objects on top of one another. For a simpler option, see the note at the bottom of this step.
Step 2a – Place a checkbox image on the layout. This could be an image with a checkmark, an X, or you could simply use a text object with an X or √ character. Use the “engraved” effect and give it a border if you wish.
Step 2b – Place a text object or button with nothing but a single space character over the image. Make the object slightly smaller vertically and horizontally than the object from 2a and center it vertically and horizontally such that it covers the whole object except the border and/or engrave effect. Format the object to have a clear background with no border.
Step 2c – Set conditional formatting for the item in 2b with the following formula:
not ValueCount ( FilterValues ( Globals::SelectedItems ; Table::RecordID ) ) > 0
Replace Globals::SelectedItems with the name of the global field you created in Step 1.
Replace Table::RecordID primary key from the table from which you will be selecting records.
For the conditional format, set the background color to be the same as the background color on your layout.
Conditional formatting for the object layered on top of the checkbox.
The result of this step is that when the primary key of the current record exists in the list of keys in the global field, the checkbox image will be hidden, making it appear “unchecked”.
Step 3 – Create the Button
Define a button for the object you created in 2b above. The button simply needs to perform a Set Field step to either add or remove the ID of the current record to or from the global field.
The target field will be the field you created in Step 1.
The calculation will be as follows:
Case ( ValueCount ( FilterValues ( Globals::SelectedItems ; Table::RecordID ) ) > 0; Substitute ( Globals::SelectedItems ; Table::RecordID & ¶ ; "" ) ; Globals::SelectedItems & Table::RecordID & ¶ )
Replace Globals::SelectedItems with the name of the global field you created in Step 1.
Replace Table::RecordID primary key from the table from which you will be selecting records.
Step 4 – Filter Button
Now you will want to have a way to find all of that user’s checked records. We’ll look at two approaches. One uses a find script; the other uses a relationship.
Option 1 – The Script Method – Create a script that enters find mode, loops through all the IDs in the global field and creates a find request for each. The script would look something like this:
Enter Find Mode[] Set Variable [$i;1] Loop Set Field[ Table::RecordID ; GetValue ( Globals::SelectedItems ; $i ) ] Set Variable [ $i ; $i+1 ] Exit Loop If [ $i > ValueCount ( Globals::SelectedItems ) ] New Record/Request[] End Loop Perform Find[]
Option 2 – The Relationship Method – Create a relationship to the table with records being selected based on the global field = record id. Then create a button that runs the script step Go To Related Record.
… And You’re Done
Enhancements
User-based rather than session-based
The method above will cause all selections to be cleared whenever the database is closed. You may want to set this up to be persistent across a user’s sessions. In other words, if a user logs out, then logs back in later, you may want their selections to remain. There are three possible options.
Option 1 - Instead of using a global field, use a non-global text field in the table you are filtering. Instead of storing a list of IDs in the field, store a list of account names. When the checkbox is clicked, add or remove the current account name to or from the list. When searching for selected records, do a simple find on the current account name in that field. One drawback to this method is that it will trigger any modification date/time/user fields to be updated which may not be desirable for auditing purposes.
Option 2 requires that your database be set up with a user table and a way to access the current user’s records from the current context. Then, instead of using a global field as created in Step 1 above, you will use a field in the user table in a similar manner.
Option 3 - Run a script on exit that saves the contents of the global field along with the current account name to a record in a table you create to store such things. Then upon opening or logging in, a script will run to search for a record in that table with the current account name and repopulate the global field with the contents of the record you populated on exit.
Select All and Select None buttons
For Select All, create a script that loops through each record in the current found set and adds the ID and a carriage return (¶) to the global field if it didn’t already exist.
For Select None, simply clear the global field:
Set Field [ Globals::SelectedItems ; "" ]
That’s all!
To see this and techniques from most other articles in action, download the “Free Contact Manager Database” using the form in the sidebar or by visiting the Inspired Business Suite webpage.
As always, if you have any questions or comments feel free to post a comment below or contact me directly.
19 Comments
Grammar Correction. “… And Your Done” should be “…And You’re Done”.
Thanks David. It’s fixed. Glad you got my back!
Thank you for great tech.
After click twice, How can I hide the check mark?
Hello,
Thank you for this method, it works nicely. But as Boris asked, how can I do to uncheck a record after clicking on it again?
@Boris & @Damien – I’m not sure I understand your questions. The calculation in step 3 should cause the check box to toggle. Click once, and it checks the record. Click a second time and the record is unchecked. Is this not working for you? If not, can you share your button calculation from step 3?
In Step3, the function “ValueExists” is not recognized by Filemaker (I’m using version 12). Instead of it, I tried using the “ValueListItems” function, and it seems to be working. But I can only check the record but can’t uncheck it. Here below my calculation for step3:
Case (
ValueListItems ( Inventory::SelectedItems ; Inventory::Ref. nr ) ;
Substitute ( Inventory::SelectedItems ; Inventory::Ref. nr & ¶ ; “” ) ;
Inventory::SelectedItems & Inventory::Ref. nr & ¶
)
Holy %@#$! I can’t believe that glaring error has gone this long unnoticed. Thanks for pointing that out Damien! It is fixed now in the article. Your calculation should look something like this:
BTW: ValueExists is one of my custom functions that I use in nearly every one of my solutions. It basically just does ValueCount ( FilterValues ( a ; b ) ) > 0. It’s handy to have around, but not so good for general code samples.
hmmm… :):) It’s working like a charm now. Thank you for the quick response by the way.
OK.. This is great information.
But All I need is a Check box to
check off and then hit delete the
checked items.
Seems like a bunch of steps to do that.
Is it. ?
@Michael J – Yes, it might be a lot of steps, but this is probably the safest, most robust way to approach your problem. A simpler approach would be to just simply have a non-global delete flag field in the table. Simply toggle that field 1 or 0 (or empty) with a checkbox, then have a delete script that searches on that field, then deletes all records, possibly in a new window so you don’t lose your current found set. The drawback to this is that, in a multiple user environment, you could run into record locking conflicts.
Today I revisited Filemaker after a 15 year hiatus. I’ve now spent the better part of a day trying to find the most efficient way to add a checkbox field to my file, something that comes standard in every other DB development tool I’ve ever used. After finally finding this article, I was able to kludge together a checkbox, only to discover that Filemaker doesn’t have a Boolean field type. I guess I should have known that from the lack of a checkbox field type. Filemaker’s data architecture, both in terms of database and application design are incomprehensible. I’ve looked long and hard for a competing database management product on the Mac, to no avail. Without competition, Filemaker seems to be wallowing in complacency.
I would like to thank the author of this article for his diligence in finding a solution for this problem. However, I am curious why you haven’t given up on this tool. Anyone looking to do serious development needs to look elsewhere. Let me know if you find anything. I’m going back to coding in Objective C. It’s less work.
@DWR – I feel your pain and agree with much of your assessment. However, I think that you would be hard pressed to find a rapid application development platform that is more approachable than FileMaker. In my experience both as a FileMaker developer and a developer on several other platforms, there is no other platform that allows you to build complex, custom database solutions for small and medium sized business more quickly and inexpensively than FileMaker: Mac, PC or *NIX. If FileMaker is indeed complacent (and I do agree that they are), that is the reason. Objective C is great, especially for robust, scalable applications. But, I would be willing to bet that if we were both faced with the same task of building a custom database solution from the ground up, you with objective C and me with FileMaker, I could build the system for 1/4th of the price in 1/4th of the time. Yes, I would have to face some minor annoyances along the way (like having to use a number field as a Boolean), but I’d still get the job done fast and cheap.
checkboxes are simple in FMP.
1. make a value list with one entry that is blank
2. add a field to the layout
3. make this field a checkbox set based on the value list.
4. if it is checked it has a boolean value of 1
if it is not checked it has a boolean value of 0
5. you can then do calculations or “if/then” statements in scripts based on whether the field is checked (=1) or not (=0).
@Jim – Yes. For a simple checkbox in a single user environment, that would work. However, when you are in a multi-user environment and you want each user to be able to check records, you will need to use a global field as I describe in this article. Also, your value list will need to have a value of 1, not a blank value. A blank value will not resolve to 1 in a find.
Hello, Thank you for your post.
I have faced a problem with using same technique as you suggested.
The problem is that “RecordID” field is defined as number and the number of digits are different by record.
I avoided this issue using prefix text such as “@@@@@@@@” with “Right” function to have same number of digits.
The formula is like this ;
Right ( “@@@@@@@@” & RecordID” ; numberOfDigits )
“numberOfDigits” is defined by length of prefix text.
Is there any other idea?
I am Japanese and not good at English, so pardon me if there are any wrong English usage.
Regards.
Hi Danny,
Hope all is well, How can I accomplish step No. 4 (Step 4 – Filter Button) using option 2 relationship (The Relationship Method ) Im trying but Im having a hard time.
Please guide me.
Thank you so much.
Regards,
Karen
In case someone stumbles upon this post, I’d like to add that there’s a better approach to a “select all”-button these days: A script with a loop to build the list of IDs is rather slow and shouldn’t be used any longer. Instead you should create a summary field and set it to “list of” in order to populate the field with all record IDs of the current found set. Now when you press the “select all” button just set your global field to the content of that summary field. No loop required.
@Michi – Thanks for the tip. It’s a reminder to me that this article could use an update. In addition to the “list of” summary field, we can also hide objects now without having to layer objects with conditional formatting.
Hi Danny,
Excellent solution.
With later versions of FileMaker the Step #2 and #3 can be combined into a single object.
1. Create a button with an X icon that is black. This can also have an outline around the box.
2. Under conditional formatting set the icon color to White.