Labels from FileMaker Pro
I publish The Mushroom Growers’ Newsletter. For years I used Bento as my database system to manage my subscribers. However, FileMaker, Inc., discontinued the program a couple of years ago and no longer supports it. They did supply a conversion program that is intended to modify the whole Bento database to work with FileMaker Pro.
Converting Bento to FileMaker Pro
The conversion process did not go well. First I had to upgrade my version of Bento to the last production version. That never happened. The new version couldn’t find the database no matter what I did. Thus, I couldn’t use the program to perform the conversion. That seems to be a common problem. The key thing with the conversion program is that it converts the layouts so they don’t need to be created manually. The alternative is to recreate the layouts in FileMaker Pro. That process isn’t too difficult, so here are the general steps for converting from Bento to FileMaker Pro “manually”:
- Open your database in Bento (any version of Bento that works for you) and export it as a text – comma separated CSV using All Records and All Fields. That creates a text file. Check the file to see if the first row includes the field names. Mine did.
- Close Bento, open FileMaker Pro and import the .csv file you just created. FileMaker will create a table using the column names and the data from your .csv file. Just be sure to tell it that the first row has the names in it.
- Create a layout to display your data in a friendly way. I just kept my Bento screen open while I duplicated it, more or less, as a FileMaker layout. That works pretty well once you get the hang of it.
- Create a layout for printing your data to mailing labels. I use Avery 5960 labels that use the Avery 5160 template (your label sheets should tell you what template to use). FileMaker already has defaults for a wide variety of labels including Avery 5160, so I just used all the defaults. Some of my labels have six lines (Name, Company, Address 1, Address 2, City/State/Zip, Country), so I used 9 point font size with the default Arial font.
Making a Label Layout Work
All worked well until I queried up a batch of records and looked at the preview. The bottom row of three labels was missing! A Google search revealed that this is also a common problem. The issue is that the printer I use, an HP 5200, has a print area that is slightly smaller than the sum of all the components that make the vertical size of the Avery 5160 template. The tech support people suggest dinking with various dimension settings within the FileMaker layout until that last row appears. I wasn’t able to make that work. It may be that the latest version of FileMaker does things a little differently.
Here’s my simple solution. Go to FileMaker Pro File > Page Set Up and set the printer to print at 99%. That worked perfectly for me.
My final issue is how to save labels by choosing which label to start printing on. Suppose you print eight labels on a new label sheet. You need an entirely new sheet if you want to print more labels unless you can tell FileMaker that you want to start printing on label nine. My Bento system has this option that is selected from a dropdown menu with the numbers 1 through 30 to choose from (for Avery 5160).
FileMaker’s answer is this:
Here is one method. Put as many blank records in the database as needed to match the number of already used labels. Then sort the database, or the found set including blanks. The sort will place the blank records at the beginning of the printable records, so that the first record with data in it prints on the first unused label on the sheet.
By putting in as many blank records as there are total labels on a full sheet, and setting a field numbering the blanks from 1 to the number of labels on a full sheet, you can find and omit the number of unused labels left to get exactly the number of needed blanks for a particular print job.
Riffing of off that, I created a new field called “Blank Record Number” and created 30 records with nothing in them except a number in that field from 1 to 30. Now, say I want to print all labels from Zip Code 19348 (lot of mushroom growers there), but I want to start printing on label 8. Here’s the process:
- Get your data entry screen up then click the find button at the top.
- Type 19348 into the Zip Code field
- Click the “New Request” button at the top (this adds to your first request all the records that match your new request)
- Enter <9 on in the “Blank Record Number” field And press the Return/Enter key on your keyboard
- Click the table view icon (to the left of the “Preview” button.
- Note that the blank records are at the bottom of the list. So click the sort button and sort by zip code. Since the blank records have a blank zip code, they will move to the top of the list
- Go into preview mode and choose your label layout to see that it will print with eight blanks in front of your chosen records.
Well, OK, that works, but it lacks elegance. FileMaker Magazine discusses a more elegant solution that involves some scripting. Unfortunately the online version doesn’t provide the details and I’m not far enough along to want to be writing scripts for FileMaker.
That’s what I did today. If I came up with better solutions, I’ll update this post. If you have a better solution, please share it in the comment section below.