FileMaker Pro Corner

Go to my Published Articles list
Lookups and Repeating Fields, or Related Fields?

When you convert your FileMaker Pro files from earlier versions to version 3.0, you'll notice that the files are almost identical. Lookup fields are still lookup fields, and repeating fields are still repeating fields. FileMaker Pro 3.0 does this in order to maintain ease of conversion and to allow voluntary adoption of the new relational capabilities of FileMaker Pro 3.0.

Having converted to FileMaker Pro 3.0, you have the option of replacing lookup fields and repeating fields with related fields. A related field is indicated in the new interface by two colons in front of the field name; e.g., "::Contacts". It allows you to display data from another file based on a relationship with that file. Figure 1 shows an example of a layout with 9 of the 12 fields on it being related to other files.

Figure 1: A layout in a file which has 9 of its 12 fields related to other files

 

Related fields can be formatted and used as any other field in the database, as though the related field were part of the same file. The behaviour of a related field is very similar to that of a lookup field with one key difference: a re-lookup is no longer necessary to update the values in the field. With a related field, if the value of the field in the related file is changed, all linked records are updated automatically.


Lookup Fields


In some cases, it may make sense to retain lookup fields rather than replace them with related fields. For example, you may want to retain lookup fields when you wish to transfer data from one file to another (so that it's in two places) during data entry. This may be appropriate if you're creating a file that will not be part of a full database solution, but still borrows data from other files through a lookup. Another example where lookups may be appropriate is where you may have fields that should not be updated when related data changes. For instance, the price of an item on an invoice, if updated, would cause problems with existing invoice totals (refer to Figure 2).

Figure 2: A later change to a unit cost would change the invoice total - not a good idea!

 

Therefore it's important to consider your database very carefully before changing it to incorporate relational features.

Converting lookup fields into related fields

During the conversion of a database into FileMaker Pro 3.0 format, relationships are automatically created to handle the match-field links necessary for the lookup fields. These existing relationships can be used as the basis for the related fields that will replace the lookup fields. You can check any of your converted FileMaker Pro 2.x databases using the 'Define Relationships' command in the 'File' menu to see the relationships that have been automatically created. When you examine the 'Define Relationships' dialogue box, you will note that three parameters are necessary for the creation of a relationship:

- Choosing the file that is related
- Naming the relationship
- Selecting the field in each file that will be used for matching purposes

Figure 3 shows an example of this statement.

Figure 3: Relationships automatically defined by a FileMaker Pro 2.x --> 3.0 conversion

 

If you have a working knowledge of lookup fields in previous versions of FileMaker Pro, you'll see that the function of the 'Define Relationships' dialogue box is much like the matching portion of the 'Lookup Value' dialogue box, as shown in Figure 4.

Figure 4: The 'Lookup Value' screen in FileMaker Pro 2.1

 

The 'Lookup Value' dialogue box requires that you specify a file to look up, and which fields to check between the two files for the lookup match. In the relational area of FileMaker Pro 3.0, a relationship replaces that portion of the lookup definition. Like lookups, relationships can be defined to link records within the same file.

Having decided that your database system would work better with dynamic data rather than static lookups, you will need to determine which layouts in the database currently use the lookup field. One way of accomplishing this is to use the 'Access Privileges' command in the 'File' menu. After choosing 'Overview', click on the lookup field you want to replace with a related field. This will cause a bullet to appear against any layout on which the field is placed, as shown in Figure 5.

Figure 5: Determining the layouts containing a lookup field

 

Once you've determined the layout on which you're going to use a related field, use the 'Mode' menu to enter 'Layout' mode. Double-click on the lookup field on the layout to display the 'Specify Field' dialogue box. Note that the existing field is automatically selected in the 'Specify Field' list, as shown in Figure 6.

Figure 6: The 'Select Field' dialogue box after double-clicking the lookup field on the layout

 

Note also that with FileMaker Pro 3.0, there is an additional pop-up menu at the top of the dialogue box that allows you to specify a field in the current file, or access fields from other files if a relationship is defined with linkage to those files. Remember that in the case of your lookup conversion, the relationships have already been created for you, so you can simply choose the relationship that is based on the correct file and match-field information. Once the relationship is chosen from the pop-up menu, the list of fields that can be specified is updated to display the fields in the related file. Replace the lookup field with the related field by choosing it from the list, as shown in Figure 7.

Figure 7: The 'Select Field' dialogue box after choosing the appropriate relationship

 

The related field will appear on the layout with two colon symbols preceding the field name. Return to 'Browse' mode and note that the related field is displaying the current information from the matching record in the related file.

Once you've replaced all occurrences of the lookup field with related fields, you can delete the lookup field using the 'Define Fields' dialogue box. With this deletion, you've taken full advantage of the new relational capabilities of FileMaker Pro 3.0, using a relationship to share data between two databases. You've eliminated the need to re-lookup fields to ensure that the data in the field is current, and you've removed the redundancy and overhead of storing the data in two separate files.

Repeating fields

Now what about repeating fields? They are like databases within records. Along with many conveniences, they can also make certain operations more difficult. Think about the advantages and disadvantages of repeating fields before you use them in your database. These might be summarised as follows:

Advantages

- The concept is easier to understand.
- Related data can be listed in a single record.
- All entries in the field can be searched at once.
- The field can be located on a layout and formatted as a single block.
- Calculations can quickly determine the minimum, maximum, etc. of the values in the repeating field.

Disadvantages

- Repeating fields must be split by importing into the same or a new database before they can be summarised
- Only the first item in a repeating field is used for sorting.
- It's not possible to easily sort or insert values within repeating fields.

Converting repeating fields using related fields

In earlier versions of FileMaker Pro, repeating fields were the only way to store multiple values of data within each record. One of the major limitations in the use of repeating fields was that it was somewhat difficult to sort or report on the information contained in them. Typically, the management of repeating fields involved scripts that would clone the file, and import all of the records so as to split the multiple values in repeating fields into separate records in the clone. The importing process could be slow and tedious if large numbers of values were to be split.

Fortunately, the relational capabilities of FileMaker Pro 3.0 allow you to choose between repeating fields and related fields. Consider replacing any repeating fields with related fields and a portal. A portal is a new layout object in FileMaker Pro 3.0 that allows the display of multiple related records from a related file. Figure 1 shows two examples of portals, one displaying 'Line Items' fields and the other 'Invoices Self' fields.

There are two major components in the conversion of repeating fields:

- Creating the file that will contain the information currently in the repeating fields
- Relating the new file back to the original file and replacing the repeating fields with related fields in a portal

Export the repeating fields to a new file

In order to eliminate the repeating fields, the data currently in them must be transferred to a new file. Fortunately, a new feature in FileMaker Pro 3.0 that allows you to export data while simultaneously creating a FileMaker database makes this task fairly easy. In order to do this most efficiently, perform a 'Find' in the database of the repeating fields so that at least one record is in the found set. Use the 'Export Records...' command under 'Import/Export' in the 'File' menu, name the file, and choose 'FileMaker Pro' as the export format.

When the 'Export Field Order' dialogue box is displayed, check only the repeating fields and, in addition, the field that will be used for the relationship. For example, let's say you are working with an invoice system in which there are currently repeating fields for 'Part Number', 'Part Description', 'Quantity', and 'Part Price'. You wish to replace those repeating fields using related fields and a portal. The related file - let's call it 'Line Items' - will need to contain the information from the four repeating fields as well as information for a field that will be used as a match field for the relationship. In our invoice scenario, the most likely field for this use would be something like an 'Invoice Number' field. As a result of the export, the 'Line Items' file will consist of five fields: 'Part Number', 'Part Description', 'Part Price', 'Quantity', and 'Invoice Number', as shown in Figure 8.

Figure 8: Exporting repeating fields from the 'Invoices' file to the new 'Line Items' file

 

After the export is finished, do a 'Find All' in the 'Invoices' file, and then open the 'Line Items' file.

The 'Line Items' file is going to be used as the source for the information that we'll display back in the 'Invoices' file in place of the repeating fields. Generating this file is simply a matter of importing from the original 'Invoices' file and choosing to split repeating fields one last time. Before importing, use 'Define Fields' to remove the repeating options from the four parts-related fields in the 'Line Items' file. Now delete any records in the 'Line Items' file. In the 'Import Field' mapping dialogue box, note that the 'Match Fields' button has been replaced by the 'View By' pop-up menu in the upper-right corner of the dialogue box, as shown in Figure 9.

Figure 9: The 'Import Field' mapping dialogue box used to define the fields to be imported

 

Don't forget to split the records during the import by checking the appropriate button, as shown in Figure 10.

Figure 10: Check the radio button to split the records during the import

 

After the import, the 'Line Items' file will contain all of the information that is also being kept in the original repeating fields in the 'Invoices file'. The key here is that there are now multiple records for those invoices that had multiple entries in the repeating fields, and that these records can be matched, based on the 'Invoice Number'. The 'Invoice Number' field will be used as the basis for the relationship between the 'Invoices' and 'Line Items' files.

Create a relationship to the 'Line Items' file

Open the 'Invoices' file and use the 'Define Relationships' command in the 'File' menu to create a new relationship. Specify the 'Line Items' file and choose the 'Invoice Number' fields from the left and right field lists in the 'Edit Relationship' dialogue box, as shown in Figure 11.

Figure 11: Editing the relationship between the 'Invoices' and 'Line Items' files

 

Remember, we are choosing our match fields using this dialogue box.

Let's take a look at two other options in this dialogue box that will come into play as we try to build all the functionality of the repeating fields into the related fields we'll be using instead. The first option allows us to choose to delete any related records if the current record is deleted. In our scenario, we must ask ourselves, "If I delete an invoice, do I also want to delete the line items that are a part of that invoice?" In this case the answer is "yes"; if an order for four widgets has been cancelled and the invoice deleted from the 'Invoices' file, we don't want the line item record for the parts to remain in the 'Line Items' file. Therefore the option to "Allow deletion of related records" should be checked.

One of the advantages of using the repeating fields was the easy entry of multiple pieces of information in an expanded field format. We can retain that ease of entry by using the second option, "Allow creation of related records". This option should also be checked, and then the relationship saved.

Set up a portal for the related fields

Whilst still in the 'Invoices' file, enter 'Layout' mode. Double–click on each of the repeating fields, replacing them with the related fields from the 'Line Items' file using the relationship that we have defined to the 'Line Items' database. The appropriate dialogue box is shown in Figure 12.

Figure 12: The 'Specify Field' dialogue box used when changing repeating fields to related fields

 

If necessary, arrange the four related fields horizontally on the layout. Enter 'Browse' mode, and note that you should be seeing data displayed from the first related record in the 'Line Items' file.

In order to list multiple related records from the 'Line Items' file, a portal must be created on the layout. Return to 'Layout' mode, and use the portal tool to create the portal on the layout. In the 'Portal Setup' dialogue box , choose to show records using the relationship to the 'Line Items' file, as shown in Figure 13.

Figure 13: The 'Portal Setup' dialogue box

 

You will probably want to display the same number of rows in the portal as the number of repeats in the repeating fields that are being replaced. In addition, check the option to allow deletion of portal records. This option allows us to easily delete one of the 'Line Item' records if a mistake is made while entering parts and quantities in an invoice.

Arrange the related fields within the first row of the portal. Return to 'Browse' mode. You should be viewing the same information from the portal as was originally being displayed by the repeating fields.

Test the results

Do you remember the options regarding the creation and deletion of related records that we used as we created the relationship to the 'Line Items' file? Try entering a new invoice in the 'Invoices' file and we'll see how these options make it easier to enter parts and quantities in the invoice. You should be able to enter parts and quantities as necessary using the related fields in the portal. Display the 'Line Items' file using the 'Window' menu, and note that as you enter additional parts and quantities to the invoice, new records are being created in the 'Line Items' file. This ability to automatically create related records is the real power of using a related file rather than the original repeating fields. The entry of parts and quantities into the invoice using the related fields results in the same files as if you had imported and split repeating fields. This happens automatically, without having to import. And, once you've implemented the 'Invoices' and 'Line Items' relationship, it's a fairly straight-forward task to script the generation of summary reports in the 'Line Items' file!


You can download a copy of this article in PDF format from the appropriate link below. To view the file you'll need Acrobat™ or Acrobat™ Reader from Adobe Systems Inc.

Mac OS

MAC FILES

Windows

WIN FILES


Go to my Published Articles list

Copyright © 1997 Hilton Brown - All rights reserved