Newsletter #4
Combining Files With Similar Data

My mother keeps track of a mailing list for a group of bluegrass musicians. One day she asked me to help her start a new file for her Christmas list. I asked her what fields she would need and what she wanted to do with the file. She said it would be almost exactly like the Bluegrass file - names, addresses, phone numbers, notes, and she would probably just print labels. I suggested that she could use the same file and use a checkbox to show which group each record belongs to. By the time we were done, we had the file keeping track of 4 different lists.

Making such a decision sounds easy enough. But it does take a little consideration. Here's the thought process; When the purposes, fields, and reports of multiple groups of data are similar, it may be logical to combine them into one file.

It might not be so logical if you're responsible to multiple organizations where any of the groups might want control of their database at a later time. If you had a single-file solution, it probably wouldn't be much of a problem to make a copy of the file and delete the records that don't belong to that group. But it could be more complicated when you're dealing with multi-file solutions. Of course, if most of the fields in each group have very little to do with each other, don't try to combine them. It's OK to have a few fields that are just for specific groups and not others.

In my mother's case, it was simple to get started. She had a typewritten Christmas list, so we weren't even going to import any data. (If you have questions about importing data, get hold of me.)

We created a new Text field and called it "Group." (I'm assuming you know how to create a field here.)

Then we created a Value List. Here's what you do;

1) In FileMaker Pro 7, choose File > Define > Value Lists. In previous versions choose File > Define Value Lists.
2) When the dialog appears, click the New button.
3) In the Value List Name box, type "Group."
4) Type the Tab key and enter the names of your groups. Be sure to put a return between each entry.
5) Click the OK button.
6) Now get to the layout where you enter data and go to Layout mode.
7) Add the field to the layout.
8) While the field is still selected, choose Format > Field Format.
9) In the Field Format dialog, (FMP7) next to “Format field as:” click on “Edit Box” and scroll down to “Checkbox set.” (If you're working pre-7, click the radio button next to Pop-up list and change it to Check boxes.)
10) Next to “Display values from:” or "using value list," click on the pop-up that says and scroll down to the Group value list you just created.
11) Click the OK button.
12) Resize the field so it can accommodate all the checkboxes and text in the Group list. You can make the field taller, wider or whatever you want. A field formatted with checkboxes will allow you to have multiple columns and rows.
13) Switch back to Browse mode to see if it looks the way you want.

Now when you add new records to your file, just check the box next to the group it belongs to. Some records can belong to multiple groups. Just click the checkbox next to each group it belongs to. The nice thing about having people belong to multiple groups is that when any of their contact information changes, you only have to make that change in one place instead of in different files. As I said, my mother uses 4 checkboxes. I have a file that has 40.

J **


Of course, the main purpose of this newsletter is to remind you that I'm here to serve your FileMaker needs - development, support, and training.

If you can think of anyone you might refer me to, I offer a 5% "Thank You!" for the first 2 years of billing on referrals.

< Back to In the News


Email us here.