How and When to Use Multiple Files

Troubleshooting

Trouble Creating Related Records with Non-Equijoins

I want to create a relationship that allows creation of related records on one side of the relationship, but the box that enables that capability is grayed out.

You might have noticed (for example, in Figure 7.19) that the option Allow Creation of Records in This Table via This Relationship has mysteriously been disabled. This suggests that you have one or more non-equality conditions in your relationship match criteria. The rule is this: FileMaker can allow creation of related records only if the relationship in question consists only of conditions involving an equality comparison. This limits such relationships to using only the equal (=), less than or equal (), or greater than or equal (images/U2265.jpg border=0>) operators.

Multiple match criteria are fine, as long as they're all based on one of those three operators. (This can actually be rather useful: A multimatch relationship that allows creation of related records automatically fills in all the key fields of the related record.) But as soon as any non-equality condition becomes involved in the match, the capability to create related records goes away.

This makes sense if you think about it. FileMaker can create a record via an equijoin because there's only one condition that satisfies the match criteria for the current record. Suppose that you're on a Customer Layout, looking at customer number 17, and you have a portal into Invoices, in which the relationship to Invoices is an equijoin on CustomerID. FileMaker can create a new record in the portal by creating a new invoice record and setting the CustomerID to 17. But suppose that the relationship instead were based on a "not equal to" relationship? To create a record on the other side, FileMaker would need to create an Invoice record with a customer ID other than 17. Fine, but what customer ID should it use? There's really no way to say. Similar reasoning holds for other non-equijoin types: There's no sensible way for FileMaker to decide what match data should go into the related record.

If the capability to create related records is enabled, the key fields in the related record will always be populated with values equal to the key field in the parent record, regardless of which of the three allowable relational operators is chosen.

No OR Conditions with Multiple Match Criteria

Whenever I add multiple match criteria to a relationship, FileMaker always tells me the match will work if condition 1 AND condition 2 AND condition 3 are true. But I have a match that needs to work if 1 OR 2 OR 3 is true. Where do I set that up?

You don't, unfortunately. Using the native FileMaker relationship features, relational matches are always AND matches whenever multiple match criteria are specified. If you want to mimic the effect of an OR search in another table, you need to find another means of doing that. Say, for example, that you have a database with tables for teachers, classes, enrollments, and students. From the viewpoint of a teacher, you want to be able to view all students who are outside the normthey have either a very low GPA or a very high GPA. You could try to do this with two match criteria, but that would necessarily be an AND match, which would never be fulfilled (no student would have both a low and a high GPA at once).

The solution here would be to create a stored calculation in the student table called something like ExceptionalGPA, defined as

If ( GPA < 2 or GPA > 3.75; 1; 0)

The calculation will have the value 1 when the student's GPA is exceptionally high or low, and a value of 0 otherwise.

You could now create a field in the teacher table called Constant, and define it as a calculation that evaluates to 1. Then specify a relationship between the teacher table and the student table, with multiple match criteria: TeacherID=TeacherID, and Constant=ExceptionalGPA, meaning, "Find me all students with the same teacher ID and an exceptional GPA."

FileMaker Extra Managing the Relationships Graph