Checkbox fields: Generally speaking, I hate them. I mean, they’re handy of course, but a little too handy. They can end up recording multiple pieces of information in a single field: That makes it hard to do reports. Let’s look at an example.
Here’s a quick and dirty layout from a subscription database:
See how we have a single field with multiple checkboxes for the magazines that person is subscribed to? That’s a nice easy way to do that kind of data entry, but there’s a problem.
Your boss asks you for a report of each magazine and the names of the people who are subscribed to it. You would probably create a subsummary report: one that sorts by magazine, and has the client names listed for each magazine.
Do you see the problem with this database structure? What if this subscriber is subscribed to more than one magazine?
Since there is only one subscriber, and multiple magazines, we can’t split that subscriber up to make it appear with each magazine.
Since there is only one subscriptions field, we can’t split them up either.
So this report can’t happen. Let’s make this database relational, young Jedi.
The first thing we need to do is create a new table called “Subscription”, with the following fields:
Start out by duplicating our layout, and then deleting the subscriptions field from it. Let’s use the portal tool by clicking this button:
On the Data tab of the Inspector, set the following options:
So now you have related records created for your magazine subscriptions. That means you’ll be able to create the report your boss asked for. Any FileMaker book or training course will show you how to create a subsummary report, so we won’t get into that, but let’s talk about data entry.
This portal entry into related records is almost always an easier way to get your data into a reportable format — and that’s good. However, there is a problem with entering that data. If you are used to entering the data using our original checkbox field, then you’re used to doing this procedure for data entry:
- Look for the checkbox you want to click.
- Click it.
On the other hand, the portal method needs you to do this:
- Go to the last portal row.
- Click on the pop-up menu field.
- Look for the value you want.
- Click it.
Four steps instead of two. That may suck. If your users have always entered this data into a checkbox field, they’ll really think it sucks.
So what’s a poor boy to do? A little bit of FileMaker trickery to incorporate the best of both worlds! We’re going to let users do data entry in that checkbox field that they know and love, and then have a script create the related records to match the checkboxes.
Here’s a domain we don’t often venture into on FMLayoutMode. We’re going to create a script. Here it is:
Let’s walk through what this big, magical lump o’ script will do. I’ve divided up my explanation by section; each section in the script starts with a comment step.
- The setup area commits the record, so that FileMaker recognizes any changes you have made to the subscriptions checkbox field, and then it grabs two variables: the values in the subscriptions field, and the client record ID.
- The next section (“delete related if there are any”), uses not IsEmpty to see if there are any related records already created. If there are, it does a Go to Related Record for that relationship, in a new window. Then it deletes all those related records, and leaves you on a Subscription layout, ready to create the needed records.
- The next section is what happens if there are no related subscription records already created. It just creates a new window and goes to a Subscription layout. So no matter what happens, our script is now looking at a Subscription layout.
- In the “make it happen” section, we make sure there is something in the subscription field, then set some variables and start a loop that creates records for each value in the field; it sets the _kf_clientID field so the new records are related to this client. Once you’ve created the same number of related records as there are checkboxes checked, the Exit Loop If script step removes you from that loop.
- Now that you’re out of the loop, the Cleanup section closes the window and exits the script.
One last thing to do, friend. Let’s go back into Layout Mode on our original layout.
Right-click on the subscriptions checkbox field and select Set Script Triggers. In the dialog that pops up, do the following:
So what does this do? We can click and unclick as many checkboxes as we want in that field. As soon as we go to another field, or commit the record, it runs our script.
The end result?
You get the relational data that allows for the reporting you need, and the users get the easy data entry that they love.
You can download the file I created here: relationalCheckboxes.zip
Notes (just a few, sorry):
- You have to start out the script by committing the record, so that you can make changes to it in another window. Otherwise, your script may fail.
- The fastest way I know of to check for related records is to check if the primary key (which I always name __kp_ID) for that relationship is empty. Since there is always a value in the primary key on an existing record, if that is empty, no related records exist.
- For this script, you’ll need to make sure your users have the access to allow them to delete records. One way to do this is to check the box for “Run script with full access privileges”.
- Whenever I have a script performing a fast operation like this, I like to have it create a new window to do it in. The main reason? If you are on a layout that has tabs, and your script has to switch layouts, when it returns you to this layout, it will forget what tab you were on. You will suddenly be staring at the default tab. That can cause your users to have an out-of-body experience.
- When doing stuff in another window, some developers like to have the window appear offscreen, so that the user doesn’t see it. That’s a good idea, but I prefer to have it appear onscreen, with a message that tells the user what’s going on. You’ll notice I gave this window a title of “Synchronize subscriptions…”. Communicate with your users whenever possible so they always know what’s going on!
- GetValue ( $subscriptionArray ; $i ) grabs a value from the subscription checkbox field (which we set to the $subscriptionArray variable). So if $i is equal to 2, it will grab the second value.
- If you’ll have a look at my post from my April 24, 2010 post, you’ll see that I always like to end my scripts with an Exit Script script step. The script will happily end without it — so you don’t have to — but I listed my main reasons for doing this. Check it out!
- Instead of deleting and recreating the related records every time, we could have our script just create and delete records based on the changes we’ve made. That would be a little harder to script. Also, it may not improve database speed at all. The heavier thinking that you’re asking your database to do to make those decisions could take longer than our simpler delete and recreate process.