Relating to an Unstored Value… Sort Of

For this blog, I’ve created a super-simplified demonstration file. You can download it here. I created this as a .fp7 file for those readers who are still using version 7-11. If you’re in version 12, you can easily convert it.

When creating a relationship in FileMaker, the value on the “far side” of the relationship has to be stored. Most of the time, this is straightforward enough and an easy rule to work with. Sometimes, though, you really need to relate to a table occurrence using a value that isn’t stored.

For example, you might have a calculated field to indicate whether an invoice is unpaid, but that flag is based on related payments, or more accurately, payment line items. (If you can’t figure out why Invoices, Payments and Payment Line Items should be separate tables, then think about receiving one check for more than one invoice, or two checks for one invoice.)

You could make the “Unpaid” flag in Invoices a number field. But then you’d need to update it with a server-side script, or with script triggers. If you use a server-side script, then the Unpaid flag will be incorrect for at least a little while between script runs. If you use script triggers, you’ll need to be sure that field, and any dependent fields, are never updated without running the script trigger, which can be challenging, especially if the fields are often set by scripts and such.

If you’re dealing with huge volumes of records, you’ll probably want to use one of those methods. However, here’s an alternative to consider.

Imagine that for a customer, you want a list of unpaid invoices, based on the calculated “unpaid” flag we’ve described. You probably already have the following relationship:

Customers-<Invoices

WHERE Customers::ID =Invoices::Customer_ID

Unfortunately, you can’t simply add Customers::Constant_1 =Invoices::Unpaid_Flag, because the Unpaid_Flag field is unstored. However, you could do this:

Customers-<Invoices-<Invoices_Unpaid

WHERE Customers::Customer_ID =Invoices::Customer_ID
AND Invoices::Invoice_ID=Invoices_Unpaid::Invoice_ID
AND Invoices::Unpaid_Flag=Invoices_Unpaid::Constant_1

Then, from Customers, you can show a portal of Invoices_Unpaid. While you’re using the unstored “Unpaid_Flag” field, it’s never on the “far” side of the relationship.

There is an important caveat here. Make sure that your first relationship (in this case, Customers-<Invoices) isn’t relating to a large volume of records. Definitely don’t make this a Cartesian (“x”) relationship. In that case, FileMaker would have to evaluate the unstored calculation for a large volume of (or worse, all) records, and your performance will be lousy. More often, though, you can get the count of related records down pretty low with your other criteria, so FileMaker doesn’t have to resolve the unstored calculation for more than a handful of records, in which case the performance hit can be reduced, often to the point where it’s negligible.

This method isn’t a “silver bullet” and you should use it judiciously, especially if performance is an issue in your database. However, it can be a tool in your toolbox that can sometimes allow you to do an “impossible” relationship in FileMaker and solve some real-world development challenges.

Tags: Chris Cain, FileMaker Development