VISUAL QUICKPRO GUIDE FileMaker Pro 7 Advanced FOR WINDOWS AND MACINTOSH [Electronic resources]

Creating a Lookup with a Self-Relationship

By using a self-relationship to do a lookup, you can save yourself lots of repetitive input and decrease typing errors. Many types of information, like a Zip code and the city and state combination it represents, must always be the same. If you use the most specific information (in this case, the Zip code) to define the relationship, you can automatically insert the other two fields' contents.

To create a lookup with a self-relationship

1 .Choose File > Define > Database (Control+Shift+D/Command+Shift+D). When the Define Database dialog box appears, click the Relationships tab.

2 .Click the Edit Relationships button. In the Edit Relationship dialog box, choose the Contacts table in both Table drop-down lists.

3 .Click the Zip field in both field lists and then click Add (Figure 6.56).

4 .Leave all the check boxes unchecked, and click OK.

5 .When the Add Relationship dialog box appears, type Contacts by Zip in the Name of Occurrence box. Click OK.

6 .In the Define Database dialog box, click the Fields tab. Double-click the first field you want to look up. In our example, it's City.

7 .The Options dialog box appears with the Auto-Enter tab displayed. Click the "Looked-up value" check box (Figure 6.57).

8 .

When the Lookup for Field dialog box appears, choose the self-relationship you just created in the "Lookup from related table:" drop-down list (Figure 6.58).

9 .Click the City field in the list. Make sure that "If no exact match, then" is set to "do not copy" and the " Don't copy contents if empty" check box is checked (Figure 6.59).

In our example, selecting these options ensures that if there is an existing record with the same Zip code, the city and state that match it will be filled in automatically. If there isn't a record with that Zip code, nothing will be copied, so you can fill in the fields manually. Click OK, then OK again.

10 .

Repeat steps 79 if you have a second field to look up. In our example, we use the State field. Click OK when you're finished.

11 .Go to the layout where the data will be entered. Switch to Layout mode.

12 .

Choose Layouts > Set Tab Order.

13 .The Set Tab Order dialog box appears. If your layout doesn't have a tab order already, choose the "Create new tab order" radio button. Otherwise, select "Edit tab order" (Figure 6.60). Click OK.

If you already have a tab order in place but you've added fields since it was first specified, you'll see a dialog box asking you if you want to omit these fields (Figure 6.61). Click Add if you want to include them, Omit if you do not.

14 .Click inside the arrows on the fields to change or create the tab order so that the number in the Zip field is lower than the numbers in the City and State fields (Figure 6.62). Changing the tab order prompts you to enter the Zip code before the city and state, so that the lookup can take place.

15 .In the Set Tab Order dialog box, click OK.