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 to "allow creation of related records" 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 equijoin conditions.

Multiple match criteria are fine, as long as they're all based on the equals operator. (This can actually be rather useful: A multi-match 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 you're on a Customer Layout, looking at customer number 17, and you have a portal into Invoices, where 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 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.

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 norm ”they 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 low AND 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."