Are you a list fiend? If so, your hard drive is probably littered with tables and spreadsheets—from Little League rosters and wedding guest lists, to information about your personal assets. Sure, lists lend themselves to the linear and cellular format of a spreadsheet. But if they grow to more than a few dozen records, or if you’re constantly entering the same information again and again, spreadsheets can quickly become unwieldy.
Databases are ideal for managing large lists, but many people find them intimidating. Thanks to FileMaker’s $49 Bento (), they no longer have reason to fear. Bento is about as easy to use as a database program can get. To show you how easy it can be, I’ll use one scenario to show how it can manage information a spreadsheet can’t.
Read more…Make the Switch
Let’s say you’re a musician who has spent years collecting information about the people who attend your concerts, buy your CDs, and purchase other band paraphernalia. You use this information to send out letters and e-mail messages, figure out which products are selling best, and evaluate where you should be performing.
Initially a spreadsheet seemed like the perfect solution for collecting this information. But now that it contains more than 1,000 records, that spreadsheet has become unwieldy.
Your fan list contains contact information (name, address, phone number, and e-mail address), and details on items that each person has purchased, such as CDs, hoodies, and T-shirts. Here I’ll divide the data in your spreadsheet into two new Bento libraries (that’s Bento-speak for databases): one for the fan list, the other for merchandise. I’ll then link the two together to make the data easier to access and manage.
Import Information
You can import data into Bento from either Microsoft Excel 2008 () or Apple’s Numbers (). In either case, you have to do some prep work before bringing the data into Bento.
In our example, you’d start by making sure that the first row in the spreadsheet corresponds to the fields you want in your database. In this case, that would include: First Name, Last Name, Age, Address, City, State, Zip, Phone, and Email. The spreadsheet also includes columns for each item you sell; let’s assume you’ve placed an X under any product that a fan has purchased.
Save the spreadsheet as a comma-delimited text file. If you’re using Excel, go to File: Save As, and select Comma Separated Values (.csv) from the Format pop-up menu; in Numbers, go to File: Export, click on the CSV button, set Text Encoding to Western (Mac OS Roman), and click on Next. Assign the file a name, choose a location where you want to save the file, and click on Export.
Launch Bento. Unless you have changed Bento’s default preferences, you should see the Home pane. Click on Start Using Bento.
The program’s main window consists of three sections. At the top on the left is the Library list which contains all of your Bento libraries. On the bottom left is the Fields list, which shows all of the fields in the currently selected library, including information on any linked libraries. The main window, called the Records Area, displays records from your selected library. If you don’t see all three sections, go to the View menu and select Show Libraries & Fields Pane. You may also need to click the small Show Or Hide Fields Pane disclosure triangle at the bottom left-hand side of the Bento window in order to see both the Library List and the Fields List.
Now you’re ready to import the spreadsheet (File: Import). In section 1 of Bento’s Import dialog box, click on the Choose button and locate your CSV file. Section 2 gives you the option to import the data into a new library or an existing one. Make sure New Library is selected in the pop-up menu, and name it something like FanList. Section 3 lets you set data formatting by selecting a field type (text, number, date, phone number, and so on). For example, instructing Bento to import a field as an e-mail address (change the EMAIL Field Type to Email Address) later allows you to send a message to that person from within the database.
Customize Forms
After importing your data, you probably want to do some tweaking. For one thing, Bento defaults to its standard form when you import data. In addition, you may end up with duplicate fields; for instance, Bento creates both a Phone Number List and a separate Phone field (the same is true for e-mail). The List fields are useful if you have more than one e-mail address or phone number, such as work and personal, but in this case having both is unnecessary. Bento also adds a Date Created and Date Modified field to your form, which for our needs are also unnecessary.
To modify the form, click once on a field’s name. You should then see handles—small white squares—appear on the right-hand side of the selected field. Pressing the delete key will remove the field from the form, dragging the field allows you to move it where you want on the form, double-clicking the name lets you edit the field title, and dragging one of the handles will make the field larger. If you want to change shading, field alignment, text size, add columns, or change other form attributes you’ll need to click the Form Tools button at the bottom right-hand corner of the Bento window. (The button with a form and a small pencil on it.) Clicking this button reveals a small button bar at the bottom of the main Bento window with many more form formatting tools, including options for making your theme something a little more rockin’.
If a field is missing from the form, drag it from the Fields list to the location where you want it to appear on your form. To delete the Phone Number List or the Email Address List that Bento created, click on that field’s name and press delete. You’ll also want to create a second column for the merchandise information you’ll be adding later. To do this click the Objects button in the form tools bar and from the menu that appears select the option that says Column Divider.
Create a Library
Although importing the fan list is simple, you can’t then import the merchandise data and automatically link it to the contact information. Instead you’ll have to create a merchandise library from scratch, then go back and link each fan’s purchases to the items in that database.
To create the merchandise database, click on the Add a Library button at the bottom-left of the Library list (the other button creates a collection, which is a subset of records from a library). In the New Library window that appears, select the Blank template, change the name to Merchandise, and click on the Choose button. Your new Merchandise library appears in the Library list, the Records window displays a blank table, and two new fields appear in the Fields list—Date Created and Date Modified.
Now it’s time to start adding fields. Click on the large plus-sign button at the bottom of the Fields list to bring up the Create A Field window. The first field should be your product number—select Number as the field type from the box in section 1 and name the field Product ID in section 2. (Section 3 lets you set certain parameters for that field type, such as the number of decimal places.) Click on Create And Continue to add two additional fields: Product Name (field type Text) and Price (field type Currency). When you’re done, the Fields list should contain these new fields.
You could use the default form view to enter merchandise information, but since there are only a few items, it’s easier to work in table view. If you’re not in table view, click on the Table button at the top of the Records window; check boxes will appear next to all of the fields in the Fields list that are available in the table. Your table should contain one new blank record. Type the product ID, name, and price for your first piece of merchandise, and then click on the plus-sign button (+) at the bottom of the Records window to generate a new blank record. Repeat the process for the remaining items.
Link Your Libraries
The final step is to reconnect the fans’ records to the items they’ve purchased. You’ll have to do this manually—it’s straightforward but time-consuming.
First you must link the two databases together. Select the FanList library, making sure that you’re in Form View then drag the Merchandise library from the Library list to the top of the new column that you created on the FanList form (or wherever you would like it to appear)—this adds a blank merchandise table.
Find the first record in the FanList Library. At the bottom of the merchandise table, click on the teeny Add Related Records From A List button (labeled with a small plus sign next to several lines). You will see a list of items from your Merchandise library—select all of the products that person purchased (press the command key to highlight multiple items), and then click on the Add To List button. You can drag items from the list to your merchandise table; when you’re done, close the list window and repeat this process for each fan in the database.
Put It to Use
So now that you’ve set up your database, what next? In our example case, you can now more easily track CD and merchandise sales, or send out concert mailers and e-mail messages to fans based on where they live.
Say that you’re planning a concert in Chicago. With your database in place, you can easily create a collection that only includes people who live in and around that city. Go to File: New Smart Collection. At the top of the Collection window, enter the search criteria. To look for Chicago-based fans, choose City Contains from the first two drop-down menus, and then enter Chicago in the blank field. Click on the plus-sign button to tack on another city, such as Evanston, Illinois (you can also search by state or any other field in your database). Click on Find. Every time you add a record that fits these criteria, Bento will automatically update that collection.
Bento allows you to create a list that’s much more convenient to manage and customize than a spreadsheet—and that makes it much easier to find and use the information you’ve collected.
Editor’s Note: This Working Mac article has been updated to reflect changes made to the recently released Bento 2.
Jeffery Battersby is an IT manager and freelance writer based in New York. Check out his blog, Building the Perfect Beast.