EXAMPLE: Cartesian Join, Minus One

From Dwayne Wright - Certified FileMaker 9 Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright

CHAPTER 06: Relationships In Action

There are times you may want to have a table related to itself and this is commonly called a self join relationship. Now FileMaker will not allow you to join two table occurrences together twice and that is essentially what you are doing with a self relationship. FileMaker will require you to create a new table occurrence. So a self relationship would probably look something like ... clients ( table occurrence ) linked to clients1 ( table occurrence ). Both table occurrences would reference the same table but you would see them differently any time you are viewing table occurrences for fields, layouts, scripts, value lists and the like. I did an example file of this back in December of 2007 and you can download a copy of it at http://www.dwaynewright.com/blogfiles/uni1.zip. This example is built upon the framework of that older file.



STEP 1: SETUP THE CARTESIAN JOIN
The Cartesian Product describes a relationship where any record in one table occurrence will match all the records in another table occurrence. The relationship operator that you would use for this is the X operator.

Here you can see the Cartesian join in use.

STEP 2: PREPARE FOR MULTIPLE COMPARISONS
Now you will likely want to omit the current record that you are on from the Cartesian portal you are looking at. This can be done by using a multiple predicate relationship. FileMaker 7 and higher allows you to create more than one relationship comparison layer, this is somewhat akin to the multiple available layers in the sort dialog box. The setup for a multiple layer comparison relationship starts off just like a regular FileMaker relationship. You navigate to the relationship tab in the define database dialog box. You draw a relationship between two table occurrences and then open up the relationship setup box.

Along the right side of your relationship box and below the right hand table occurrence field pick list, there is a button with the label of "Add" and sits along side a button labeled "Change." These new buttons are linked to a box below the buttons that lists the levels of valid relationship comparisons in order of first listed is compared first and then on down the line. Normally, you will only see the one comparison line but that may change as you grow to appreciate what a multiple layer comparison can do for you.

Here you can see we have it setup to add our second comparison layer.



STEP 3: ADD THE DOES NOT EQUAL TO JOIN ≠ ( not equal )
This could be used to flip the results on an equal to relationship. Before FileMaker 7, if you wanted to see a not equal relationship, you would have to create additional key fields that were the calculated opposite of the equal to relationship ( so that “yes” data strings would read as “no”). No need for those redundant fields these days, now you can use the exact same relationship setup but use the not equal operator to flip the results.

So we want to say if the name does not match the current record (or better yet the primary and foreign key fields do not match). I’ll use the name field here just for illustration purposes.

Here is what our relationship looks like in the final version.

An example file can be downloaded at ...
http://www.dwaynewright.com/blogfiles09/CartesianMinus1.zip

Here are some links to other posts that might be of interest in regards to this topic...
http://fmprelationships.blogspot.com/2007/11/filemaker-universal-or-constant.html
FileMaker And Relationship Comparison Operators
Relationships That Range
Unequal Comparison Relationship
Multiple Predicate Relationship
Relationships That Can Range Based Upon User Input


=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2009 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.

ADVERTISEMENT ==============================
Looking for a robust FileMaker CRM template to manage your business? Check out the InBizness product line at http://www.dwaynewright.com/solutions.html.