Disclaimer: This one falls into the category of just because you can do something doesn’t mean you should. I do not recommend implementing this technique in a real-world solution, in case it should break in some future version of FileMaker.
One thing that bothers me about cartesian join relationships is that they’re implemented at the field level, when from a logical perspective they are actually table-to-table joins (their purpose is to give each table access to all rows in the other table). It seems like we ought to be able to link a table to another table directly, without bothering with fields at all, and it turns out we can… sort of.
To create a “table-to-table” cartesian join like the one at the left, start by defining a new field in each table… the field type doesn’t matter, because it’s going to have a very short lifespan.
- In each table, define a new field, temp_key
- In the Relationships Graph, create a cartesian join relationship between the two tables, linking the two temp_key fields.
- Delete temp_key from each of the tables.
The result is that your two tables will be joined as per the above image. From what I can tell, the relationship behaves exactly the same as a normal cartesian join relationship; for example, there are 14 records in the impostors table, and from the perspective of customers, here’s what we see:
If you’d like to see this technique in action, and are too busy to conduct the experiment yourself, you can download this demo file: cartesian join experiment.