Jonathan's latest books. > > >
Data Design Pros, LLC
~ Custom Software for Growing Businesses ~

Are you using FileMaker Pro to help run your business? You should be. All other databases are second-class citizens and money pits. (Yikes! That sounds a little harsh.) Read why here.

We make your databases work the way you want them to. That's as easy as it gets. Contact us here.

We are dedicated to serving businesses by designing, constructing, training and servicing tools built with the cross-platform database program FileMaker Pro.

* Save Time
* Increase Profits
* Get Exactly the Software You Need

Click here for information about what can be done with FileMaker

About Us
Learn about our company and how we started.
Products & Services
Find out about our product line.
In the News
Read press releases, newsletters, articles, and reviews of our products. Example below.
Ask the Experts
Get answers to commonly asked questions or send email to our experts.
Sign our guest book to receive product notices and press releases.
FileMaker Advisor Magazine
Jonathan's column
FileMaker Pro
Find out more about the program we use in our application development.
Job Opportunities
Data Design Pros, LLC

What our clients say about our work.
FileMaker Links
Go here for a list of links to other web sites that talk about or use FileMaker Pro etc.
FileMaker 8.5 Book Updates
Additions and corrections.
FileMaker Training

Using Lookups to Streamline Data Entry

Enter data in one field to give you automatic data in one or more other fields.

By Jonathan Stars

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. (The 7-11 file is easily converted to 12 and greater.) 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'll probably 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 to verify your city. If they don't, you know they're not running with a very efficient database. Multiply all the times their employees have to type in the City and State (not to mention all the spelling mistakes they might make) and you can imagine money being flushed down the toilet. 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 **


© 2009 Jonathan Stars


Data Design Pros, LLC is an independent entity and this web site has not been
authorized, sponsored, or otherwise approved by FileMaker, Inc.

Email us here.

web counter

web counter