Export Just What You Need in FileMaker

The Lesson of Context

One of the first lessons I learned with FileMaker is that of context! Layouts, reports, calculations, scripts, and so on all act from the perspective of a single Table Occurrence (TO) — the “context” — pulling in data from related tables as needed.

Generally, the record set being acted on is the set of records in that context table occurrence or a found subset thereof. There are a few exceptions to this, however, and today we’re going to explore one of those.

Let’s say I work in airplane maintenance, and we have a fleet of 3 aircraft that are required to have maintenance every month. My boss asks me to send her an output in Excel of all the planes, one row per plane, and the most recent date of maintenance for each.

I said to myself that should be really simple. My solution has two tables: PLANE and MAINTENANCE. Because my goal was to export one row per plane, it seemed that exporting from the context of PLANE would do the trick. Then I went into the relationship graph and changed the relationship between PLANE and MAINTENANCE to sort the latter by date, in descending order:

Then, the export would show the most recent maintenance date for each plane. I could also have created a dedicated TO just for this purpose.

My export script looked like this:

Go To Layout PLANE.
Show all records.
Export MAINTENANCE data.

When I opened the Excel file, it wasn’t what I had expected to see. The first plane had been serviced 3 times. The second plane was a new plane, so it had not been serviced yet. The third plane had been serviced 6 times. Instead of the 3 records I expected based on the script’s context, the export contained 9 records! FileMaker expanded the export out to include all the related records, as shown in the image below. So how can I deliver the export listing each plane only once, with the latest maintenance date for each?

The answer lies in “Group by.”

On my second attempt, the relationship is again sorted on Maintenance Date in descending order. The script is similar to the initial one, too — still operating from the context of the PLANE table. But note, this time, the Sort Records step that sorts by the primary key in the PLANE table…

…which then allows me to use Group By in my export. This ends up delivering the expected result of outputting a list of planes with their most recent Maintenance Date.

If all I had needed was to show this data on the PLANE layout then I would have been done once I had sorted the relationship and then displayed the Maintenance Date field on the layout (no portal needed).

Sometimes we have a lot of confidence in what we think are simple things, but FileMaker can surprise us now and then, which is why we always test everything we write.