Home | Syllabus | Labs

Lab #3 - Modifying a FileMaker Pro Database Easing data entry and linking databases Due January 24, 2005

OBJECTIVE: Using the database created for Lab 2, you will learn how to add a few of the more advanced features that make working with databases a lot easier. Specifically, you need to: change the Category field to a pop-up menu field, add 2 new fields called Date Created and Date Modified that automatically enter the date when a record is created and/or modified, and create a new database called Products and link it to your Mailing list database. The new database will automatically pull in mailing information when a Grower ID code is entered. Print out the first two records of your finished database showing the effects of the new features. Extra Credit: Extend this assignment and make a Plant Locator database that automatically retrieves information saved in your Products and Maillist files. See below. TIPS FOR FILEMAKER PRO 7 USERS: Creating Relationships in FMP 7 is done a little differently than described in Step 11 below. Steps 12 - 14 are OK as written. Step 11 instructions: 1. In Products database, click on File, Define Database . Click on Relationships. Click on the "Add a Table" icon (bottom left corner). Click on the File pop-up menu. Select Add File Reference. Navigate and choose the Maillist database. 2. Click on the "Create a Relationship " icon (next to the Table icon). The Edit Relationship window will open. On the left side, select Maillist for the 1st table and on the right side, select Products for the 2nd table. Click once on Grower ID under Products and once on ID under Maillist. Then click Add. Click OK, OK. 3. Additional information can be found here: Creating Relationships and Working With the Relationship Graph PART I:

STEP 1.	Using a pop-up menu to enter values in a field: Create a new field called Category. Position it over the product field that you created in Lab 2.
STEP 2.	Switch from Browse mode to Layout. Click once on the Category field to highlight it. Choose Field Format from the Format menu.
STEP 3.	Click in the circle next to Pop-up list. Click on the triangle and choose Pop-up menu. Click on the triangle next to Define Value Lists Click on Define Value Lists.
STEP 4.	A dialog box called Define Value Lists will appear. Click on New at the bottom of the window. A new window will appear called, Edit Value List. Type Category in the top box next to Value List Name. Type the following values into the custom values list: Trees and Shrubs, Groundcover and Vines, Perennials, Ferns and Ornamental Grasses, and Aquatics, Bogs & Marginals (press return after each item). Click OK to return to Define Value Lists. Click Done to return to Field Format. Click OK to return to Layout mode.
STEP 5.	Return to Browse mode. The Category field should have a dark outline/shadow around it. Click once in the Category field. You should see a list of the available choices. Create a new record and try using this feature. Isn't that easier than typing. Here's the finished look.
STEP 6.	Let's add a box around the rest of the fields just to make things look a little more standardized. Switch back to Layout, click once on the ID field, and click on your right mouse button once. This is a short cut alternative to the Format menu. Now click on Field Borders.
STEP 7.	Click once in each of the boxes labeled Top, Bottom, Left, and Right. Click OK.
STEP 8.	Switch back to Browse. You should see a box around the ID field. Repeat these steps for the other fields: Firm Name, Owner, Address, City, Zip, and Date Received. Hint: This procedure can be done on more than one field at a time.
Part II. Automatically entering the date when a new record is created This feature is very useful for helping to keep track of the date when a given record was added to the database or when it was last modified. By having the information automatically entered by FileMaker, no one has to remember to do it. Here's what to do.
STEP 9.	While in Browse mode, choose Define Fields from the File menu. Enter a new field name called Date Created. Click Create. Click Options. Click in the option marked Creation Date. This same procedure can be used to automatically enter a modificantion date, an ID number, and many other options. Click OK. Click Done. Repeat these same steps for another field called Date Modified. This time, choose the Modification Date option under Entry options. This way, any updates to the mailing list will be tracked according to the date the entry was created and when it was last modified. Lastly, return to Define Fields, Click on ID, click on Options, choose Serial Number. Set the Next value to 11. Click OK, click Done. Rearrange your fields so that ID, Date Created, and Date Modified, are all on the top left side. Your existing records will not be affected by these changes but new records that you create will automatically have the current date entered, the next ID number, and prompt you for a category of plant. Check this out by clicking on the New button to add a record.
Part III. Linking two files together. This feature is very useful for saving time whenever you will be using the same information over and over again. It also allows you to create one file with producer names and then utilize that information over and over again, without having to retype it. Here's what to do.
STEP 10.	Create a new file called Products with the same field names that you created for Maillist. (Hint: Use the File, Save a Copy As command. This way, you won't have to recreate all the field names.) Open the Products file. Go to Define Fields. Click on ID, change the name to Plant ID. Click on Save. Create a new text field called GrowerID. Click Done. Change the title at the top of the page and the color of the background rectangle. Go back to Browse mode. The layout should look like this:
STEP 11.	Click on File, Define Relationships. Click New. You will then be prompted to pick a file. Choose the Maillist file that was already created. Click Open. You will see two windows. The one on the left is the current file called Products. The one on the right is called Maillist. Click once on the field called GrowerID (in the left box) and once on the field called ID (in the right box). Click OK. Click Done.
STEP 12.	Go to Layout mode. Click once on the Firm Name field to select it. Then click on the right mouse button to get the various field options. Choose Specify Field. Click once on the triangle next to Current File ("products.fp5") and click on Maillist. Then click once on ::Firm Name. Click OK.
STEP 13.	You should now have two colons in the Firm Name field. This implies that this field is linked to another file. Repeat these steps for the Contact, Address, City, State, ZIP, Phone, FAX, Email, and Web fields. Return to Browse mode.
STEP 14.	You have now specified that FileMaker will look for mailing list information in the Maillist file whenever a value in GrowerID matches a value in ID. Erase any data that you have in the Products file for Firm Name, Contact, Address, City, State, ZIP, Phone, FAX, Email, and Web fields. Enter a 1 in GrowerID. Press Tab. The mailing list data should flow into each of the appropriate fields. Try adding a new record and enter a 2 in GrowerID. Hopefully everything is working. Print the first two records of your database. That's all. Extra credit: Try creating a mailing list file, a product list file, and a plant locator file. You are two thirds of the way there. Do another Save a Copy As command. Call it Plantlocator. Define a second relationship between Plantlocator and Products and link the PlantID field with the Plant ID field. Change the Category field back to a normal field (not a pop-up menu) and specify it to link back to the products file. Repeat this step for Product. That's it. Now all you have to do is type a number into PlantID and a number into GrowerID and the appropriate information information will be pulled in from the other two files. This is called a Relational Database. Just image. By creating a single file with firm information and another one with product names, you can easily update your plant locator information each year by just editing the GrowerID and PlantID fields. Good Job!

Home | Syllabus | Labs

Copyright © The Ohio State University All rights reserved.