Ditch Those Flush Cashes, Use Cartesian Join Instead!

All too often, the Refresh Window script step is used with the 'Flush Cached Join Results' option checked. Sometimes this is through lack of understanding, but other times it is required to refresh those pesky relationships which have trouble re-evaluating. This article explains why Flush Cache can be detrimental on performance, and offers an alternative and more efficient method for refreshing relationships.

What is Flushing Cached Join Results?

The FileMaker documentation describes this as the following:

Select Flush cached join results to delete the results of queries for related records and cause related records to be refreshed. Do not select this option if you know your script does not affect related data, and if you want to minimize the performance impact of re-accessing related data (particularly when sharing a database over a network).

Whenever a relationship is evaluated (the query) the related records found are cached (downloaded) to your computer. If you are accessing the database over LAN or WAN, then the speed at which this happens depends upon your connection speed. Once cached, the relationship should perform much faster because the records now reside on your machine.

However, by flushing the cached join results, all results of relationships that have been done (i.e. all the records downloaded through relationships) are cleared from your cache, and you basically have to start all over again re-downloading related records when viewing them.

The reason why FileMaker states this should be avoided over network, is because of this re-download factor.

When is a Flush Cached Join Results needed?

Most of the time, a flush of the cached join results is not necessary at all, and typically it is used in error. People may think that because a calculation is not refreshing on their layout, it will require a refresh of the window to display correctly. While this may be true, all too often I have seen the "Flush Cached Join Results" option checked at the same time, merely for added insurance that the desired result will be achieved. Sometimes the performance hit is negligible so it goes unnoticed, but once the solution goes up on the network, that is when the real fun begins!

Flushing of the Cached Join Results should really only be used when dealing with a particularly complex relationship whose results are not re-evaluating as desired.

What causes problematic relationships to not refresh?

I have found over the years that the most problematic relationships are those which make use of globals, and unstored calculations whose calculations reference related records. Sometimes it may be necessary to build these types of relationships to achieve a desired result (notably when using an interface/data model).

The particular situations in which relationships have refresh issues always tends to elude me, but when it happens, the reasons are usually clear, and include one or more of the reasons above.

So, why not just flush the cached join results when it happens?

Well for starters, as mentioned earlier, every single downloaded record that you have downloaded and cached since you first open a file, will be cleared. This basically means that you are starting from scratch since the file was opened, and any record you viewed will be re-downloaded.

Over WAN this is where it is most noticed. When using a WAN hosted database, you may notice upon first use that portals and relationships may load slowly as the record is downloaded - scrolling portals may be slow, that sort of thing. But after more use, things speed up because the records are now being accessed from your cache. Flushing the cache puts you back to square 1.

In 99% of the cases your intention for wanting to flush the cached join results is merely to refresh one particular relationship, not the entire database. If only there was a way to do this...... well there is !

The solution: Using a Cartesian Join in your Relationship

The refresh problem arises because the relationship is unaware of its own need to refresh the results. The trick here is to tell the relationship that it should refresh itself.

Relationships will re-evaluate themselves if one or more fields on the source side of the relationship change explicitly. However, we know that any field put on the source side of a relationship will actually affect the results of that relationship.

The exception here is a cartesian join.

Cartesian join predicates, by definition, find ALL records through a relationship:

The above relationship uses a cartesian join, so will find all contact records through this relationship.

In this relationship, a standard "=" predicate is used. The result of this relationship will be all contacts whose status is "Active".

Here lies the key in this technique. The above relationship uses a standard "=" predicate, AND a cartesian join predicate. The result of this relationship? All active contacts. The cartesian has no effect on the relationship, EXCEPT as a means to refresh it.

We have managed to add a field into the relationship, which has no detrimental effect on the relationship, does not affect the relationships intended results, but is a field nonetheless which we can use to refresh the relationship.

So, how does it work?

Simple, just set the field you have used in your cartesian join predicate to any arbitrary value (non-empty). Ideally, you should create a field whose sole purpose is for the refresh of relationships. You could create one field per relationship if you wish to control specific relationships, otherwise you can use one field for all relationships that require refreshing. I have created one in this example called "PortalRefresh".

The field can be set either via a script, or more ideally using a script trigger. The script trigger would be perfect if you wish the relationship to re-evaluate upon changing the contents of another field (which itself indirectly affects the result of the relationship).

The refresh field you choose can be global or non-global depending upon your needs.

An Example for you to play with

I have included at the bottom of this article an example file which you can use to see the method in action.

Because these refresh issues are sometimes contrived, I have come up with a similarly contrived relationship to illustrate the purpose.

The example file comprises 2 tables - Home, and Contacts. There are 3 relationships on the graph as shown in the above image:

1. The first is a standard cartesian join to find all contacts through a relationship
2. he second makes use of the cartesian join technique for refresh
3. The third leaves off the cartesian technique and just finds a particular record.

On the Home table there is a calculation which obtains the Contact ID of the Nth record through the first relationship. The user enters a number into a global field which corresponds to the contact record they wish to view.

To cause the refresh issue, the global field the user enters the record # into resides on the Contacts table, and is pulled through to the Home table for use in the unstored calculation.

What it all boils down to is a relationship to a particular contact, which will not refresh without a little nudge :)

Using the Example File, a Demonstration

In the example file you will see a portal of all contacts, and their record number displayed beside them:

You will also see the following entry fields:

Enter a number into the Record # to show box. If you click out of this box, you will notice that the Found Record value does not change to the record you entered. This is the refresh issue. Due to the nature of the relationship & the fact it is based on an unstored calc that references globals in the contacts table, the relationship is not refreshing.

The button beside the field will run a script that either uses the flush cache method, or the cartesian refresh method, depending upon which tab you are using.

Beneath each record entry field is a script-trigger version of the field, which runs the associated script using an OnObjectModify trigger.

Both versions in the example file (flush cache & cartesian) exhibit the same issues, but each uses a different method of solution.

Final Thoughts
You should always use the flush cached join results option on the Refresh Window script step with caution. Don't just check it willy nilly to guarantee your refresh issue will be solved. Understand what it really means and what impact it has on your solution, especially when deployed over LAN or WAN.

Finally, consider the alternative method of refreshing a relationships results by using a cartesian join - you will retain your cached results, and only "flush" the specific relationship you need flushed, improving WAN performance in the long run.

Example File:

Please find attached an example file. This file was used for all the screenshots in this article, and is provided to help you fully understand what is going on in this article, and to let you experiment in FileMaker with this solution.

Download Example File