VISUAL QUICKPRO GUIDE FileMaker Pro 7 Advanced FOR WINDOWS AND MACINTOSH [Electronic resources] نسخه متنی

Lookup vs. Related Fields

Once you've established a relationship between tables, you can choose between two methods for using the source data. With the first method, you use the Lookup function to copy the actual data from a field in the source table to a field in the current table. Alter natively, you can display the source data directly by placing the table's fields on a layout (or using them in a calculation) in the receiver table.

If you choose Lookup, when you enter a value in the source table's match field, the data in the specified fields is copied into fields in the receiver table. Once you've copied the field contents, however, if the data in the source changes, the data in the receiver table won't update. This might be useful if, for example, you want to maintain the customer's shipping address at the time of the sale.

If you choose to display the source data in the current table layouts without copying it, any changes made to the source will be reflected in the receiver table. The fields displayed in the layout are the actual fields in the source table, not a copy. If a customer moves and you update your records, all the entries in both databases will show the new address, no matter when the entries were originally made.

To create a Lookup field

1 .

Open your current file and choose File > Define > Database (Control+Shift+D/Command+Shift+D). When the Define Database dialog box appears, click the Tables tab. Select the current table, then click the Fields tab.

2 .

Type a name for the first field, make it a text or number field(depending on what you need), and click Create.

3 .Repeat step 2 for every field from the source table that you need to duplicate. These fields will hold the data that will be copied from the source table. Use the same field names for these destination fields that you used in the original source table.

4 .

When you've finished creating the new fields, double-click the first field name.

5 .The Options dialog box appears with the Auto-Enter tab displayed. Check the "Looked-up value" check box (Figure 6.17). The Lookup dialog box opens.

6 .In the "Lookup from related table" drop-down menu, choose the relationship you created for the two tables.

In our example, it's Customer, which we created in "To create a match field in a current table" on page 117 (Figure 6.18).

7 .The "Copy value from field" list will become active. Choose the field to be copied from the source table (Figure 6.19). Click OK, then OK again to close the Options box.

8 .Repeat steps 47 for each field that will contain looked-up data, then click OK.

Tip

You can update data in the current table that you entered with a Lookup. To do this, click in the match field in a layout and choose Records > Relookup Field Contents. The database will copy the related data from the source into all Lookup fields using that match field for the relationship. If you only want to update certain records, do a Find for those records and then choose Records > Relookup Field Contents. Only records in the current found set will be updated.

To use related fields in a layout

1 .Go to the layout in which you want to display the related fields. Choose View > Layout Mode (Control+L/Command+L).

2 .

Drag the Field tool onto the layout to bring up the Specify Field dialog box.

3 .From the drop-down menu above the field lists, choose the relationship you want to use (Figure 6.20).

4 .The field list will display the fields in the source table (Figure 6.21). Choose the field you want and click OK.

5 .Repeat steps 24 for each field you want to use.

The related fields on the layout will have field names preceded by two colons (::). The double colonsindicate that the fields are from a related table, not from the current table (Figure 6.22).

Tips

A database can have many relationships. You can use a field from any relationship in a layout. Just choose the table occurrence in the Specify Field dialog box before choosing the field name.

Don't be surprised if you create a new relationship and a set of new tables appears on your list of related tables. FileMaker 7 allows multiple tables to be connected through a chain of relationships. Those unexpected tables are connected to the most recent table occurrence you related to your current table.

The source match field can have multiple entries, separated by returns. As long as the match field in the destination field is the same as any of the entries in the source match field, the relationship will work.

FileMaker 7 is less forgiving than earlier versions about leading or trailing spaces in key fields. So a field with "1027" will not match another with " 1027" (a leading space).