Sounds fancy, I know. But a Cartesian join in FileMaker is a useful tool. When needing to report information about the same table used in a layout, this is a powerful, quick, and easy way to do it. Typically, one would create a relationship between two tables based on some common, unique piece of data, like a key or id field. Here, a row of a table would only be related to rows of another table that share that unique piece of data. Cartesian joins (or cross joins), however, are an inner join of two tables where the condition always evaluates to "true". This means that there is a join for each row of one table to every row of another table.

Example 1: Showing Recently-Edited records

Let’s say there are multiple users of a table in a database dedicated to contact information. It might be helpful for others to see which records were updated recently. One could do a find on the modification timestamp field, but a quicker, more visual way would be to create a portal (perhaps in a popover) showing all the most recently edited records.

If the main table is called People, in the Relationships section of the "Manage Database" section, one would highlight the People table and press the + key at the bottom to make another table occurrence, perhaps named "People Recently Edited". Then link the PrimaryKey field of both tables. However, instead of choosing = for the relationship (which would only ever show the same, current record one was already on), choose X instead (that Cartesian join mentioned earlier). This creates a general relationship between the tables where every record (row) is up for grabs. I like to do the sorting of records right here in the relationship, too, sorting by modification timestamp, but in reverse order so that the most recent record is always first.

Then, back in the layout, base the portal on the "People Recently Edited" table occurrence. Bonus points for grouping the fields in the portal row into a button that runs the script step, "Go to Related Record" using the "People Recently Edited" table and the same layout. This creates a shortcut to visit those recently-edited records.

To prevent the current record being viewed from showing up in the portal, use this portal filter: People::PrimaryKey ≠ People Recently Edited::PrimaryKey

Example 2: Show Records with missing information

In a database of composers and their basic info, I wanted to—at a glance—see which composers I was lacking birth and deaths dates for and be able to click on their name in a portal to quickly add their information. This kind of portal can be created a few ways. One way to do it is to list all the composers but use conditional formatting to show those who still need dates in another color: IsEmpty ( Composer::BirthYear ) or IsEmpty ( Composer::DeathYear ) ) Another way would be to use a portal filter so that only those lacking a birth or death year show up in the portal: IsEmpty ( Composer::BirthYear ) and IsEmpty ( Composer::DeathYear ) ) Then, as more and progress is made, the portal will continue to shrink. ◼︎