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

Chapter 6. Creating Relationships > Field Formatting with a Calculated Match Fi...

Field Formatting with a Calculated Match Field

Using the calculated field technique combined with a drop-down list format allows the user to choose the customer from a list of names rather than numbers. You will need to make one change to the relationship as it was shown in “To create a calculated match field” on page 98. It's more efficient to list the customers in alphabetical order by last name, so you'll want to re-sort before you create the drop-down value list.

To add a drop-down list to a match field:

1.
Go to the receiver file and switch to the layout that holds your calculated match field. Choose File > Define Relationships.

2.
When the Define Relationships dialog box appears, double-click the relationship that you established by creating a calculated field.

In this example, we use Name Lookup (Figure 6.19).

Figure 6.19. Double-click to choose the relationship that uses your calculated match field from the Define Relationships listing.


3.
When the Edit Relationship dialog box appears, click the “Sort related records” check box (Figure 6.20).

Figure 6.20. Choose Sort related records to organize the files in the match field.


4.
In the Specify Sort dialog box, double-click a field to define the sort criteria.

In this example, we use Last Name (Figure 6.21). Click OK, and then OK again, then click Done.

Figure 6.21. To choose the way a match field will be sorted, select the field you want to sort on from the Specify Sort list.


5.
Choose File > Define Value Lists. When the Define Value Lists dialog box appears, click New (Figure 6.22).

Figure 6.22. The Define Value Lists dialog box will be empty if you have never used it in the file before.


6.
When the Edit Value List dialog box appears, give the value list the same name as your calculated match field and click the “Use values from field” radio button (Figure 6.23).

Figure 6.23. Give the value list the same name as the calculated field to make it easy to remember their connection.


7.
The Specify Fields for Value List dialog box will appear with the name of your master file in quotes at the top. Click Specify File (Figure 6.24).

Figure 6.24. Click Specify File to choose the source database.


8.
From the Open File listing, navigate to the source file and double-click to select it.

9.
The Specify Fields for Value List dialog box will appear again. From the list on the left, click to select the calculated match field. In our example, the file is Name Lookup (Figure 6.25). Click OK, then OK again and then Done.

Figure 6.25. The field list of the related database will display in this scrolling list.


10.
Go to the data entry layout and switch to Layout Mode (Ctrl+L/Command-L).

11.
From the tools on the left, choose the Field tool and drag it onto the layout to add the calculated match field to the layout (Figure 6.26).

Figure 6.26. Use the calculated match field to choose the related record.


12.
Click this field to select it and choose Format > Field Format (Ctrl+Shift+M/Command-Shift-M).

13.
When the Field Format dialog box appears, click the Pop-up list radio button and choose the calculated match field from the “using value list” pop-up on the right (Figure 6.27). Click OK.

Figure 6.27. The list of values from the match field in the related source database will display in the pop-up.


14.
If you are using lookup fields, verify that they are defined to copy data using the Name Lookup relationship. (See “To create lookup fields,” page 94.) If you are displaying related data, make sure that the fields are from the correct relationship.

15.
Return to Browse mode and create a new record (Ctrl+N/Command-N).

16.
Click in the match field. It will display a list of names from the source field of the same name in the Customer database. Click on the name you want (Figure 6.28).

Figure 6.28. The list of customer names will appear in alphabetical order by Last Name.


All of the related (or lookup) fields will fill with the values from the related record of the name you chose (Figure 6.29).

Figure 6.29. When you choose a name in the Name Lookup list, all of the related fields display the values from the source database.



PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


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