New Loop options in v20.3

I had an interesting discussion in FMSlug about this with Daniel Harlow who basically was the one who got this new loop feature put into the product.

Consider a loop that loops over 500 records. Within that loop it is doing a Set field on a particular field (let's say a foreign key).

Within that table occurrence group sits 10 relationships, all of which reference this field that is being changed in the loop.

Each iteration of the loop and each timei that field is changed per record, all 10 relationships are re-evaluated by FileMaker because that's just the way it works, it is having to refresh the relationship to determine new records through it.

BUT in reality in this particular scenario you just don't care, you just want to set the field and are not going to be using those relationships at all during the loop, yet you still are forced to incur the overhead of FileMaker re-evaluating the relationship and downloading any changed data set it finds.

Introduce the new loop options. The loop options are concerned with when FileMaker will refresh relationships and the data sets of them.

"Always" is default and existing behaviour as it will always re-evaluate relationships any time it is required.

"Minimal" is a bit murky to be honest. The only info is that it flushes minimal data, so I presume that if any field data is used in the loop dependent on the field(s) changed then the value must be refreshed otherwise you'll be dealing with wrong data.

"Defer" will only every refresh relationships on exiting the loop, this is the optimal setting to use if you have no intention of doing anything with relationships in a loop, which is more often than not. The flush on exit will only be for the last record you end up on I believe. This is because once you navigate back to any other record after the loop ends, that records relationships will be refreshed.

Once FileMaker releases more specific documentation (which is crazy they haven't yet) we'll know with a bit more clarity.


To give you some idea of the performance increase, Daniel did a test with 3,000 records looping and the loop time went form 9 minutes to 2 minutes . The loop was a record syncing loop (which often sync scripts have lots of relationships attached to the TO).

I myself did a loop test selecting a large TOG with relationships to a single field. Did 100 loops setting the field to itself each time. With "Always" on it took 12 seconds, and with "Defer" on it took 8 seconds.

I suspect this feature could actually get some interesting usage within scripts. Consider wrapping your entire script in a Loop with "Exit Loop If [ 1 ]" at the end. You can write a script that completely ignores relationship refreshing in this manner.

14 Likes