Newsletter #8
Using Lookups to Streamline Data Entry

One of the best things I ever did in my contacts database was to learn how to use Lookups. A good example is using a zip code table to lookup the City and State. As I tab through the data entry process for a new contact, I go from First Name, to Last Name, Company, Address, and then jump right to zip code, bypassing the City and State. Why should I type the City and State when they can be entered automatically once I type in the zip code? And why should you?

In order to make this work, you'll need a zip code file (or table). Click here to go to my download page to get a FileMaker file with about 42,000 records of zip codes, cities and states. There is a file for FileMaker 5 & 6 or 7-11. Be sure to choose the file that's right for the version of FileMaker you're using. (If you're using FMP7 or greater, you might even want to add the zip code table right inside your contact file.)

In your contact database, we'll assume you already have a zip code field. Some people use a separate field for the plus four zip codes. If you don't do that and you do collect the plus four data in your regular zip code field, you'll need to add a calculation field to your file. Let's call it ZipFiveCalc. Make it a calculation field with a number result. The calculation should be Left(Zip; 5).

You need to create an equal relationship between the ZipFiveCalc field (or the Zip field if you don't put the plus four info in the Zip field) in your contact file and the Zips_JS file or table. Since the process is different for versions of FileMaker greater and less than FMP7, I'll assume that you know how to create the relationship. (If not, I'm for hire. :-) Call the relationship something like Contact_ZipCalc_Zip.

Now go to the City field, click the Options button and go to the Auto-Enter area. Check the box next to "Looked-up value." That should open the Lookup dialog. Choose Contact_ZipCalc_Zip from the drop-down. In the "Copy value from field" area, choose City. Leave everything else set to the default settings and click OK and OK again to bring you back to the field list.

Repeat the steps for the State field, except choose State from the field list.

Now go to Layout mode and remove the City and State fields from the Tab order.

To test it out, go to Browse mode and create a new record. You don't really need to add a new person, just tab until you get to the Zip field. Enter a zip code that you know exists and tab out of the field. You should see the City and State fields fill in automatically.

Have you ever ordered something over the phone? Next time you do, notice whether they ask you for your zip code before they ask you for your city. If they don't, you know they're not running with a very efficient database. Multiply all the times all their employees have to type in the City and State (not to mention all the spelling mistakes they might make) and you can just picture money being flushed down the toilet. Now that won't happen to you any more!

Among all the many uses you might make of Lookups, here are a few ideas:

* Automatically filling out most of the information on a standard contract.
* Adding the entire customer address information to an Invoice.
* Filling in the description and price for items on an invoice.
* Entering the contact and account information on a check.

I've had clients ask why you wouldn't simply display customer addresses or product information using a relationship. They don't think it's a good idea to have fields for the same data in more than one place. But in an ordering system where the price list might change, you want your current orders to reflect the prices at the time you made a quote to a customer. That way, no matter how the data changes in the Prices table, you’ll always have a snapshot of what the price was at the time the invoice was created in the Invoices table. Also, with invoices it’s important to have a copy of the address information just the way it was when the invoice was created. If the IRS ever did an audit, you wouldn’t want to try to explain why the addresses from an old invoice don’t match their old location!

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.