There are new options in the Loop script step. Claris documentation isn't up-to-date at this point and doesn't include any discussion of these options. Does anyone know what they do.
Here's my current issue. I have a loop that will refresh objects on a layout. Everything works under each of the options. So which should I choose? And why am I making that choice?
Here is some information from the blog of db service
Thanks for the link. Unfortunately it is just a re-hash of the email announcement from Claris.
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.
Brilliant thinking and very helpful post. Thanks, @weetbicks
The use of a loop as a defer evaluation block is something that I am sure to use 
Do you know if the ignoring relationship refreshing applies to subscripts too?
It also sounds like even if you need the refresh the best way might be to wrap the whole script in a loop and then do a "refresh joins" at the end so you only incur that cost once... cool cool cool.
I assume that if I don't reference any data through any relationship, the flush option doesn't matter ?
Yes but only if your script does not require the need to refresh relationships - you may be doing something where you want to then GTRR or evaluate a relationship, in which case if the relationship is not refreshed you'll have issues.
This is the kind of feature that really needs an Engineering blog article written, or at the very least a monicker of documentation
yeah I was thinking about a specific script I have with about 30 steps and lots of SQL calls to cache data. It takes a few seconds to run, so I'm going to try this trick and see if the time goes down, but that being said there are some places that I might have to manually add a refresh back in so my joins and things work correctly.
If time to run is an issue, SQL is not faster than FMP searches. SQL searches have many positives but speed is not the reason to choose them. Under the hood, an SQL search is converted to an FMP search, then performed. An FMP search will be the same speed or faster.
Interesting... I had a few cases where SQL was slower, but most of the time when I convert a "go to layout, perform find, grab results" to SQL the time gets cut by at least 50%. I wonder if I'm doing something inherently slow when I perform a Filemaker find?
I usually use Koji Takeuchi's script to get all found values after performing a find:
and then UniqueValues () to get DISTINCT or a summary field for SUM or AVG when needed.
Is there a faster way to do this?
Try this: create a blank layout using the TO that you need to search. Create a script that will enter find mode, switch to your blank layout. perform the search. Even on large data sets this should be quick.
The ListValues CF is based on GetNthRecord - well known to be the slowest function in the toolkit.
The exact type of eSQL query you use has a big factor on performance and how it is executed.
Case sensitivity will impede performance, so using UPPER to avoid this has an impact. the LIKE function is a real performance killer as well as IN.
If an eSQL query touches a table in which you the current user has a locked reccord then the entire set of records in that table are downloaded to you from server. This is the main reason we avoid it if we can on anything other than small tables. This only applies to yourself locking it, but this can occur due to poor script awareness, or mutli-window environments where a record may be open in another window unbeknownst to the user.
Thanks, great input.
What does UPPER do? Make it non-case sensitive? I did a google search and nothing is jumping out.
RE: entire set of records... Good to know. I don't think I've run into that, but maybe I have and haven't noticed. Would this ever apply to scripts run on the server? Should I commit records before running complex SQL?
One way to do case-insensitive search in SQL is like:
ExecuteSQL ( "
SELECT id, name, height
FROM Person
WHERE UPPER(name) = ?
" ; "" ; "" ; "JASON"
)
This will find Person records with name "Jason", "jason", "JASON", "JaSoN" etc.
Interesting... never needed to use it. Is @weetbicks saying it's slow, or a way to speed things up?
one of the common issues when a FileMaker developer (not case sensitive) deals with other systems (case sensitive): One searches or selects something and gets no or wrong results.
Therefore, we work with lower or upper all the time. FM attributes are converted to uppercase or lowercase (just for that) - and the same in the SQL statement (-:
Ahh makes sense.
Still couldn't find anything in the online FMP20.3.1 help.
This is where I checked: Loop