Exporting Related Fields

All exporting in FileMaker takes place from the context of a single table. In general, then, it's not possible to export data from several tables independently in one stroke. It is possible, though, to export data from tables related to the current one, whether immediately or more distantly.

Doing so is a simple matter of choosing fields from related tables when specifying fields for export. Consider a typical ordering system, in which a table holding order records is related to a table holding customer records via some sort of customer ID. The system probably doesn't store the customer name in the order table because this violates some principles of good data modeling. In FileMaker, the customer name probably is stored in a Customer table, and displayed on an order record via related fields.

Because the customer name is stored in a related field or fields, if you want to export the customer name along with the order, it will be necessary to export the related fields as well. Figure 20.5 shows what this might look like.

Figure 20.5. You can export fields from the current table, or from any related table as well.

Tip

Notice a useful aspect of FileMaker's new field list filtering feature: Because the related fields for the customer's first and last name are displayed on the current layout, they are automatically available for export in the default (layout-based) field listno need to go hunt for them in the field list for the related table.

The case of orders and their parent customer records is fairly clear-cut. Because there is only one customer record per order, the export produces one row per order, with the associated unique customer data as part of the row. But what if you try to export related fields from a child table, one that exists in a many-to-one relationship with the current table? Suppose that you shift to a detail layout showing individual orders, and export fields from that layout, including fields from related order lines? The result might not be what you'd expect, and may or may not be useful. Figure 20.6 shows a sample of such data, displayed in Excel.

Figure 20.6. You can export fields from child tables, in which case you may get records with partial data.

Notice that although only five orders were exported, there are 10 rows in the output. FileMaker has output one row for each related record. The first row contains all data from the parent record (the order) along with data from the first related line item. Subsequent rows include only data from the line item, and leave the columns for data from the order record blank.

The behavior of exports that include related fields, then, depends on the number of records related to the current record being exported. When only one related record exists, one row will be output. If multiple records exist, a row will be output for each related record.

Exporting Grouped Data