14 for 14: Ditch those Cartesian Joins, Use Refresh Portal Instead (maybe!)
By Daniel Wood, 29 June 2015
A bit of background
Back when we first started writing this blog one of our first articles was around portal refreshing and how sometimes complex relationships simply couldn't be refreshed without a forced flush cache as part of the refresh window script step. Normally these complex relationships were based on unstored calculations, related data, or the relationship in question was more than one relationship away from the base table occurrence.
We wrote an article on a technique you could employ to bypass the need to flush the cache and merely "flush the relationship" instead. The end result was refreshed relationships without the need to flush away all record data that had been sent from server to client during the session.
You can read the original article here
The technique is still solid and we have used it all these years for certain relationships that just won't refresh nicely.
How FileMaker 14 changes the game
FileMaker 14 has changed the game by introducing the "Refresh Portal" script step to address this issue. The script step is very simple and looks as follows:
Refresh Portal [ Object Name: "ObjectName" ]
It's very simple and just requires the object name of the portal to be refreshed. This forces the update of displayed records in the named portal, effectively refreshing the relationship
*Disclaimer!
We put a big fat "maybe" at the end of the article title because we don't necessarily recommend you switch (that was just trying to keep the same article name format as our earlier article on the subject!) Whether you switch to this technique or not depends on a number of factors which we'll outline below.
Benefits of switching from Cartesian to Refresh Portal
So the question is why would you want to switch to this if your cartesian join relationships have been working fine? Well, the answer is subjective. For some, there is no benefit, and for others there will.
First benefit would be removing "hacks" from your solution. We still consider the cartesian join technique a hack and it may no longer be supported in future versions whereas Refresh Portal will.
Secondly is that relationships that use a cartesian join cannot have records created via them. If you have a portal where you want to achieve this, then you may be better off using the Refresh Portal step if records don't correctly refresh.
Lastly, you remove the need for a field in your database to act as the portal refresh (however you gain a script step!)
Benefits of staying with Cartesian Join
It's the old saying - if it ain't broke, don't fix it. If there is no visible benefit in removing cartesian joins to refresh a relationship then why bother. Both methods appear to work exactly the same - the Refresh Portal script step doesn't do anything significantly different to a cartesian join refresh that we can see.
Another big benefit of a cartesian join refresh is that it can be triggered without a script. Because the refresh occurs when the value of the field used in the cartesian join is changed, this can be triggered using an auto-enter calculation on the cartesian join field that refreshes when any referenced field changes - no need for a script trigger.
In a lot of cases this is quite elegant and better than relying on script triggers
There is also another big benefit of keeping with Cartesian Joins. The Refresh Portal script step requires an actual portal on your layout to refresh! Cartesian Join refresh operates at the relationship level, whereas the Refresh Portal step acts at the portal level (but still technically is refreshing the relationship). If you are using cartesian join purely to update a relationship and not a visual portal, then you would need to add this portal to your layout. Fortunately it can be added on the right edge of your layout so is not visible to the user, but you still need it there for this to work.
In Conclusion
If you are using cartesian joins to refresh your relationships and portals and it works fine, we don't think there is any reason to change. If you are concerned about it working in future, it may be best to switch now to the Refresh Portal step. Either way you now have 2 great options!
Example File
We've built a new example file that shows both the Cartesian Join refresh technique from our earlier demo, and now the Refresh Portal script step technique also. You can compare both for yourself and see what will work best for you.