• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 6. Creating Relationships > Creating a Lookup with a Self-Relationship

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:

Choose File > Define Relationships.

When the Define Relationships dialog box appears, click New.

In the Open File dialog box, double-click on the name of the current file. In this case, the file is called Contacts.fp5.

In the Edit Relationship dialog box, type in a relationship name.

Since we're looking up records based on the Zip code, that's what we've named our relationship.

Click the same field in both field lists. In this example, we've chosen the Zip field (Figure 6.61).

Figure 6.61. This relationship will use Zip as the key field.

Leave the Sort related records check box unchecked because you don't need to specify a sort. Click OK and then Done.

Choose File > Define Fields.

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

The Options dialog box appears with the Auto-Enter tab in front. Click the Looked-up value checkbox (Figure 6.62).

Figure 6.62. To create a lookup based on a field, choose Looked-up value from the Options dialog box.

When the Lookup dialog box appears, click the drop-down list at the top to choose the self-relationship you just created (Figure 6.63).

Figure 6.63. Use the Zip Code relationship to do the lookup.

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” checkbox is checked (Figure 6.64).

Figure 6.64. Don't copy the values for City and State if the zip code isn't exactly the same.

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.

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

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

Choose Layouts > Set Tab Order.

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.65).

Figure 6.65. You can choose to create a new tab order, or edit the existing one.

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.66).

Figure 6.66. The tab order is set to bring you to the Zip Code field before City and State.

Changing the tab order prompts you to enter the Zip code before the city and state, so that the lookup can take place.

In the Set Tab Order dialog box, click OK.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint