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:
Now we set up a relationship:
On the Subscription side, we’ll click the “Allow creation of records…” checkbox for that relationship.
Now that’s done, let’s ramble on into Layout Mode.
Start out by duplicating our layout, and then deleting the subscriptions field from it. Let’s use the portal tool by clicking this button:
Then click a spot on the layout where you want the top left corner to be, and drag to where you want the bottom right to be. Add the magazineName field to the portal.
On the Data tab of the Inspector, set the following options:
You want a pop-up menu in the portal, using the same value list that you used for the checkboxes. Your layout should look like this:
Let’s add a few records to that portal.
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:
Assign an OnObjectExit trigger that uses our script.
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!
- I often use the variable $i as a counter in a loop. That’s a practice I learned years ago when I was learning JavaScript. It’s quicker than typing “$counter”, and something that is generally accepted.
- 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.
That is a very cool technique Paul, thanks for sharing. I particularly like the notes part, found some interesting stuff in there, and we share the same views on most of the pointers in there.
What are your thoughts on using checkboxes as the data entry method for items that are coming from records in a table? Lets say for example that magazines were stored in a table – which they really should be if this were a real solution. Checkboxes are fine up to a certain point, but when the # of magazines grows, you can’t really have a scrolling checkbox set, and so you are kinda screwed, and would have to keep expanding the size of the field area to accommodate. Personally I would switch to using a portal of magazines as a data entry method, which would suit this example quite well too I think. Curious to hear your thoughts on this usage of checkboxes.
Cheers!
Thanks, Daniel.
Yep, I really do hate checkboxes — for all the reasons you just said.
I rarely use checkboxes for a value list that is populated from a table. If one of those values changes, then you suddenly can’t see all your data in the checkbox field anymore. The value list needs to be stable; it’s sometimes hard for users to not go in and change these things!
… and of course, as you said, it’s easy for the number of possible values to outgrow the physical size of the checkbox field.
I also don’t like storing multiple values in a single field, which is why I first thought of this technique. Most of the time when I use checkboxes, I use a value list of “1″, as described here:
http://fmlayoutmode.com/wp/?p=398
One of my clients has a huge number of magazines they are selling ads for, and they asked for checkboxes. Luckily, I was able to convince them that portals with drop-downs were the way to go. That way, they can manage their own value list within the interface I gave them, and never have to go into layout mode to resize the field.
Very smart, Paul! This solution came across my way just at the right moment, as I have to make selctions for email-sending based among such a field, and without your way I might have gone a much more complicated way.
As I already had entries in my DB, I had to write another looped script for the existing ones, so now I have recorded everything and can work with the results.
So thank you very much for this post, if you agree; will probably post a German translation in the FM Technet forum. Please give me a note, if tis is ok for you.
Brilliant – exacly what I was looking for! I’ve added a “Commit Record” script to the “OnObjectModify” trigger to immediatley action the change. Well done – you da man!
Cheers
Mike
I have learned new things out of your blog post. One other thing to I have discovered is that generally, FSBO sellers will certainly reject anyone. Remember, they can prefer never to use your expert services. But if you actually maintain a steady, professional partnership, offering assistance and being in contact for four to five weeks, you will usually be capable to win a conversation. From there, a listing follows. Thanks
I just want to mention I’m newbie to blogging and site-building and absolutely savored your website. Likely I’m going to bookmark your site . You definitely have remarkable stories. Cheers for sharing with us your website page.
I have been exploring for a bit for any high-quality articles or weblog posts in this kind of space . Exploring in Yahoo I ultimately stumbled upon this site. Reading this info So i’m glad to convey that I’ve an incredibly good uncanny feeling I came upon exactly what I needed. I such a lot surely will make certain to do not overlook this web site and provides it a glance on a continuing basis.
Good ?€“ I should definitely pronounce, impressed with your website. I had no trouble navigating through all tabs as well as related information ended up being truly easy to do to access. I recently found what I hoped for before you know it at all. Reasonably unusual. Is likely to appreciate it for those who add forums or anything, web site theme . a tones way for your customer to communicate. Excellent task.
Great write-up, I am regular visitor of one’s site, maintain up the excellent operate, and It’s going to be a regular visitor for a lengthy time. “Our opinions do not really blossom into fruition until we have expressed them to someone else.” by Mark Twain.
Hey, you used to write wonderful, but the last few posts have been kinda boring… I miss your great writings. Past few posts are just a bit out of track! come on!”The smaller the understanding of the situation, the more pretentious the form of expression.” by John Romano.
I was reading through some of your articles on this site and I conceive this site is rattling instructive! Retain posting .
Sweet website , super style and design , real clean and use genial .
Excellent goods from you, man. I have understand your stuff previous to and you are just extremely fantastic. I really like what you have acquired here, certainly like what you are saying and the way in which you say it. You make it enjoyable and you still take care of to keep it smart. I cant wait to read far more from you. This is really a great website.
I have to convey my affection for your generosity giving support to those people who actually need guidance on your concern. Your real commitment to passing the solution up and down became unbelievably effective and has constantly made many people much like me to get to their desired goals. Your personal valuable guide signifies so much to me and additionally to my mates. Many thanks; from everyone of us.
Appreciating the time and effort you put into your website and in depth information you provide. It’s great to come across a blog every once in a while that isn’t the same outdated rehashed information. Fantastic read! I’ve saved your site and I’m adding your RSS feeds to my Google account.
There are plenty of individuals who are usually unsure of whether should look into a sale as a result model for just a variety of aspects.
You made some decent points there. I appeared on the internet for the issue and found most individuals will go together with together with your website.
Outstanding post, I think people should learn a lot from this site its rattling user genial . “You don’t have to deserve your mother’s love. You have to deserve your father’s.” by Robert Frost.
The terms sound a lot like the phrase “money-grubbing.” And that’s exactly what they mean, except in these scams, no one actually clutches a stack of bills in his fist.
Your website doesn’t show up correctly on my droid ??you may want to try and fix that
I was looking through some of your posts on this internet site and I think this website is really informative ! Continue putting up.
I would like to thnkx for the efforts you’ve put in writing this site. I’m hoping the same high-grade blog post from you in the upcoming also. In fact your creative writing skills has encouraged me to get my own website now. Really the blogging is spreading its wings rapidly. Your write up is a good example of it.
Thanks for sharing superb informations. Your web site is very cool. I am impressed by the details that you’ve on this web site. It reveals how nicely you perceive this subject. Bookmarked this website page, will come back for more articles. You, my pal, ROCK! I found simply the info I already searched everywhere and simply couldn’t come across. What an ideal web site.
Thanks for all your efforts that you have put in this. very interesting information. “Originality is the fine art of remembering what you hear but forgetting where you heard it.” by Laurence J. Peter.
Yay google is my queen helped me to find this outstanding web site ! . “Don’t rule out working with your hands. It does not preclude using your head.” by Andy Rooney.
Outstanding post, I think people should learn a lot from this web blog its real user friendly . “My father always told me, ‘Find a job you love and you’ll never have to work a day in your life.’” by Jim Fox.
Wonderful beat ! I would like to apprentice while you amend your site, how can i subscribe for a blog website? The account helped me a acceptable deal. I had been a little bit acquainted of this your broadcast provided bright clear idea