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.

Click here to download the example file.

Something to say? Post a comment...

Comments

  • Katy 06/09/2021 10:12am (3 years ago)

    Please never stop posting these gems Daniel Wood! I cannot tell you how many times you have come to my rescue over the last decade with a problem I have needed a work around or fix for. I wanted to also really thank you for the example files you are always kind enough to provide. I was using a "refresh window" script to update the results after setting a global that was part of a relationship in a second hosted file (and the relationship was between two tables in that other file) but then the users were seeing a screen I did not want them to see flashing as it ran a loop. I had thought about creating something in an offscreen window when I found your post. Problem solved and definately the tidiest way to achieve the result I needed. Thank you once again.

  • Graefe 25/03/2018 2:38pm (6 years ago)

    I am working with FileMaker Server. Refresh portal does a refresh on the local portal, but not on the client systems. The only way to refresh the portal on the clients is a Cartesian joint, still.

  • Daniel Wood 30/06/2015 3:29pm (9 years ago)

    Hi David,
    We haven't noticed any real speed difference between the two but we haven't compared them extensively or across larger data sets / WAN so we can't really comment. I know from the previous article that some people commented using a global field on the RHS of the cartesian join greatly improves the speed of using Cartesian Join, so I would recommend that at the very least, but I'd be interested to hear the outcome of large data set testing comparing the two methods.

  • David Zakary 29/06/2015 10:57pm (9 years ago)

    I haven't downloaded the file yet, did you notice any speed differences between the techniques?

RSS feed for comments on this page | RSS feed for all comments

Categories(show all)

Subscribe

Tags