{"id":16162,"date":"2023-12-01T06:00:00","date_gmt":"2023-12-01T06:00:00","guid":{"rendered":"https:\/\/www.directimpactsolutions.com\/?p=16162"},"modified":"2025-05-01T03:06:46","modified_gmt":"2025-05-01T03:06:46","slug":"new-flush-option-loop-script-step","status":"publish","type":"post","link":"https:\/\/www.directimpactsolutions.com\/en\/new-flush-option-loop-script-step\/","title":{"rendered":"New Flush Option for FileMaker’s Loop Script Step"},"content":{"rendered":"

According to Claris’ FileMaker Pro Release Notes for Version 20.3.1<\/a>,<\/p>

Flush includes the following three values:
1. Always: When setting a field within a loop, the relationship is flushed along with the join data.
2. Minimum: When setting a field within a loop, minimal data is flushed.
3. Defer: When setting a field within a loop, data and relationship data are flushed only after exiting the loop.<\/p><\/blockquote>

What does that mean, and how does it impact your FileMaker solutions?<\/p>

\"\"<\/figure><\/div>

The new flush<\/strong> option for the loop script step<\/p>

Understanding Relationships and Join Data<\/h2>

We can use relationships to fetch and display data from related tables.<\/p>

In this scenario, consider the relationship as a tunnel going from the current record in the current table to the related table, helping us locate related records.<\/p>

Whenever a relationship is established, FileMaker will use the relationship to identify related records, query related data, and cache the queried result. These cached related records’ data are referred to as join data<\/em>.<\/p>

If we update the value of one of the fields used on the left side of the relationship, typically, FileMaker will need to re-establish the relationship (find related records again) and re-query the join data (fetch related records’ data via the relationship again).<\/p>

For example, I have a relationship shown in the screenshot below. It is used to display a list of related records in a portal. On the left side of the relationship, I use a global field zgtProjectCategoryFilter, to show only related records with a matching project category.<\/p>

\"\"<\/figure><\/div>

A relationship used for a filterable portal<\/p>

On the layout, the user can set the value of zgtProjectCategoryFilter to one of the three values using these buttons:<\/p>

\"\"<\/figure>

Whenever a user changes the value, FileMaker will need to flush the cached join data, re-establish the relationship and re-query the join data so the portal displayed on the layout can achieve the \u201cfiltering\u201d effect.<\/p>

Performance Implication<\/h2>

Now that we understand relationships and join data, what are the implications of the new flush option?<\/p>

Imagine you have a script that loops. Each time it loops, it updates a field on the left side of a relationship. Imagine this field is used in multiple relationships, and a large amount of join data might be fetched through those relationships. By default, FileMaker will flush and re-query every time the left side field is updated. This could have a serious performance implication for your solution, especially if it runs over the WAN (check out this video<\/a> to understand why).<\/p>

Before version 20.3.1, we had some, but limited, control over when FileMaker flushed the join data (with the Refresh Window script step), but not when it comes to looping that sets fields used in relationships. Under the loop scenario, FileMaker will always flush and re-query, regardless of necessity.<\/p>

With Version 20.3.1, this new feature gives developers control over when FileMaker should re-establish the relationship and flush join data. It allows us to avoid expensive flush operations when unnecessary.<\/p>

Let\u2019s take a second look at the release notes\u2014this time, you should have a bit more context:<\/p>

Flush includes the following three values:
1. Always: When setting a field within a loop, the relationship is flushed along with the join data.
2. Minimum: When setting a field within a loop, minimal data is flushed.
3. Defer: When setting a field within a loop, data and relationship data are flushed only after exiting the loop.<\/p><\/blockquote>

Always<\/em> is what we had before this version, and it is the default option. If you open up a FileMaker solution using FileMaker Pro version 20.3.1+ for the first time, all of your loop flush options will be set to Always<\/em>. This flushes not when it\u2019s necessary, but when it can.<\/p>

Defer<\/em> is fast. Its behavior is simple: it will try not to flush anything until exiting the loop. There will be one flush, in total, at the end.<\/p>

Minimum<\/em> is a bit mysterious. At the time of this article\u2019s publication, there\u2019s no official documentation on this feature other than what\u2019s quoted above. The name seems to indicate that this option is doing something smart to minimize the amount of join data flushed while looping. Based on my testing, my guess is that its behavior is between Always<\/em> and Defer<\/em>; Minimum<\/em> tries to do as few flushes as possible, unless other script steps in the loop require a flush.<\/p>

Behavior and Performance Tests<\/h2>

Testing Method<\/h3>

To test this feature, I used a scenario like what I described above: one field that is part of nine different relationships gets updated repeatedly.<\/p>

In the earlier example, I only showed one relationship with the global field. In reality, I have nine of them.<\/p>

\"\"<\/figure><\/div>

Nine relationships that use the zgtProjectCategoryFIlter field on the left side<\/p>

This is probably not the smartest way for me to build a grid view in FileMaker, but it\u2019s perfect for testing out this new feature.<\/p>

The testing script I used is shown in the screenshot below. It runs from a layout that uses these relationships to display related data in portals. The script loops many times, and each time it loops, it sets the zgtProjectCategoryFilter field to a different value; in the end, it spits out the total time taken, and a list of related data fetched.<\/p>

Testing Process and Data<\/summary>

Test Case 1 – 999 loops, fetch join data mid-loop & evaluate portals<\/h3>\n\n

For the first test, inside each loop, I tried to fetch one piece of join data and record it.<\/p>\n\n

\"\"<\/figure><\/div>\n\n

Script used<\/p>\n\n

<\/td>Testing Scenario<\/strong><\/td>Always<\/strong><\/td>Minimum<\/strong><\/td>Defer<\/strong><\/td><\/tr>
1<\/strong><\/td>999 loops
9 relationships
23 related records at most
2023-11-26<\/td>		22 seconds
Mid-loop data fetched is accurate<\/td>		18 seconds
Mid-loop data fetched is accurate<\/td>		1 seconds
Mid-loop data fetched is NOT accurate<\/td><\/tr>
2<\/strong><\/td>999 loops
9 relationships
13 related records at most
2023-12-10<\/td>		21 seconds
Mid-loop data fetched is accurate<\/td>		18 seconds
Mid-loop data fetched is accurate<\/td>		~0 seconds
Mid-loop data fetched is NOT accurate<\/td><\/tr>
3<\/strong><\/td>999 loops
5 relationships
13 related records
2023-12-24<\/td>		21 seconds
Mid-loop data fetched is accurate<\/td>		18 seconds
Mid-loop data fetched is accurate<\/td>		~0 seconds
Mid-loop data fetched is NOT accurate<\/td><\/tr><\/tbody><\/table><\/figure>\n\n

Test Case 2 – 999 loops, only evaluate portals<\/h3>\n\n

Now, let me disable the step that fetches the related data in the middle of the loop, leaving only the layout utilizing join data.<\/p>\n\n

\"\"<\/figure><\/div>\n\n

Script used<\/p>\n\n

<\/td>Testing Scenario<\/strong><\/td>Always<\/strong><\/td>Minimum<\/strong><\/td>Defer<\/strong><\/td><\/tr>
1<\/strong><\/td>999 loops
9 relationships
23 related records at most<\/td>		1 seconds<\/td>~0 seconds<\/td>~0 seconds<\/td><\/tr>
2<\/strong><\/td>999 loops
9 relationships
13 related records at most<\/td>		~0 seconds<\/td>~0 seconds<\/td>~0 seconds<\/td><\/tr>
3<\/strong><\/td>999 loops
5 relationships
13 related records<\/td>		~0 seconds<\/td>~0 seconds<\/td>~0 seconds<\/td><\/tr><\/tbody><\/table><\/figure>\n\n

Test Case 3 – 99,900 loops, only evaluate portals<\/h3>\n\n

To test the overhead introduced by layouts during loops, I cranked up the total loop count by a hundredfold to 99,900.<\/p>\n\n

<\/td>Testing Scenario<\/strong><\/td>Always<\/strong><\/td>Minimum<\/strong><\/td>Defer<\/strong><\/td><\/tr>
1<\/strong><\/td>99,900 loops
9 relationships
23 related records at most<\/td>		50 seconds<\/td>10 seconds<\/td>8 seconds<\/td><\/tr>
2<\/strong><\/td>99,900 loops
9 relationships
13 related records at most<\/td>		49 seconds<\/td>9 seconds<\/td>9 seconds<\/td><\/tr>
3<\/strong><\/td>99900 loops
5 relationships
13 related records<\/td>		47 seconds<\/td>9 seconds<\/td>9 seconds<\/td><\/tr><\/tbody><\/table><\/figure>\n\n

Bonus Test Case – 99,900 loops, only evaluate portals & data viewer accidentally left open with \u201cautomatically evaluate\u201d on<\/h3>\n\n

Accidentally leaving the data viewer open significantly slowed down the process. Even though I\u2019m not using it to watch any join data relevant to my test.<\/p><\/details>

<\/p>

Conclusions & Usage Considerations<\/h2>
  1. The Defer<\/em> option does not flush join data in the middle of the loop at all. This is why, in test case 1, the mid-loop join data fetched with the Defer<\/em> option is inaccurate.
    1. This verifies the behavior mentioned in the release notes.<\/li>\n\n
    2. This also explains why it\u2019s the fastest among the three options.<\/li>\n\n
    3. We should only use the Defer<\/em> option when the join data is irrelevant to the loop logic. Otherwise, we will get the wrong data.<\/li><\/ol><\/li>\n\n
    4. The Always<\/em> option is the slowest, as we expected.<\/li>\n\n
    5. The Minimum<\/em> option\u2019s performance is between Always<\/em> and Defer<\/em>.
      1. It tries to keep the mid-loop join data evaluation accurate, as shown in test case 1.<\/li>\n\n
      2. However, the more \u201cavoidable join data flush\u201d there is, the more performant it becomes, to the point that it could be comparable to the Defer<\/em> option\u2019s performance (compare results from test cases 1 and 3).<\/li>\n\n
      3. This seems<\/em> to be the no-brainer option here, but please don\u2019t quote me on this. Let\u2019s wait for the official documentation that clarifies the behavior before mass adoption.<\/li><\/ol><\/li>\n\n
      4. Different operations that rely on relationships have different performance overheads.
        1. Explicitly fetching join data in the script introduces a much bigger performance overhead than related fields displayed on layouts (compare results from test cases 1 and 2).<\/li>\n\n
        2. The number of relationships touching the field being updated doesn\u2019t matter much. The number of related records does matter a little bit. This makes sense as the number of related records determines the number of join data that could be fetched (test case 3).<\/li>\n\n
        3. Data viewer changes FileMaker\u2019s evaluation\/flush behaviour (bonus test case).<\/li><\/ol><\/li><\/ol>

          One More Thing<\/h3>

          Inspired by this thread<\/a>, we could use this new flush option with the single-pass-loop technique<\/a> to control the join data flushing behavior of the entire script.<\/p>

          I hope this article sheds some light on this new feature.<\/p>","protected":false},"excerpt":{"rendered":"

          According to Claris’ FileMaker Pro Release Notes for Version 20.3.1, Flush includes the following three values:1. Always: When setting a field within a loop, the relationship is flushed along with the join data.2. Minimum: When setting a field within a loop, minimal data is flushed.3. Defer: When setting a field within a loop, data and …<\/p>\n

          New Flush Option for FileMaker’s Loop Script Step<\/span> Read More »<\/a><\/p>\n","protected":false},"author":11,"featured_media":16183,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"_uag_custom_page_level_css":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"set","footnotes":""},"categories":[29],"tags":[],"class_list":["post-16162","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-low-code"],"uagb_featured_image_src":{"full":["https:\/\/www.directimpactsolutions.com\/wp-content\/uploads\/2023\/12\/iStock-1354745114-min.jpg",2235,1341,false],"thumbnail":["https:\/\/www.directimpactsolutions.com\/wp-content\/uploads\/2023\/12\/iStock-1354745114-min-150x150.jpg",150,150,true],"medium":["https:\/\/www.directimpactsolutions.com\/wp-content\/uploads\/2023\/12\/iStock-1354745114-min-300x180.jpg",300,180,true],"medium_large":["https:\/\/www.directimpactsolutions.com\/wp-content\/uploads\/2023\/12\/iStock-1354745114-min-768x461.jpg",768,461,true],"large":["https:\/\/www.directimpactsolutions.com\/wp-content\/uploads\/2023\/12\/iStock-1354745114-min-1024x614.jpg",1024,614,true],"1536x1536":["https:\/\/www.directimpactsolutions.com\/wp-content\/uploads\/2023\/12\/iStock-1354745114-min-1536x922.jpg",1536,922,true],"2048x2048":["https:\/\/www.directimpactsolutions.com\/wp-content\/uploads\/2023\/12\/iStock-1354745114-min-2048x1229.jpg",2048,1229,true],"woocommerce_thumbnail":["https:\/\/www.directimpactsolutions.com\/wp-content\/uploads\/2023\/12\/iStock-1354745114-min-300x300.jpg",300,300,true],"woocommerce_single":["https:\/\/www.directimpactsolutions.com\/wp-content\/uploads\/2023\/12\/iStock-1354745114-min-600x360.jpg",600,360,true],"woocommerce_gallery_thumbnail":["https:\/\/www.directimpactsolutions.com\/wp-content\/uploads\/2023\/12\/iStock-1354745114-min-100x100.jpg",100,100,true]},"uagb_author_info":{"display_name":"Weihao Ding","author_link":"https:\/\/www.directimpactsolutions.com\/en\/author\/weihao-dingdirectimpactsolutions-com\/"},"uagb_comment_info":0,"uagb_excerpt":"According to Claris’ FileMaker Pro Release Notes for Version 20.3.1, Flush includes the following three values:1. Always: When setting a field within a loop, the relationship is flushed along with the join data.2. Minimum: When setting a field within a loop, minimal data is flushed.3. Defer: When setting a field within a loop, data and…","_links":{"self":[{"href":"https:\/\/www.directimpactsolutions.com\/en\/wp-json\/wp\/v2\/posts\/16162","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.directimpactsolutions.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.directimpactsolutions.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.directimpactsolutions.com\/en\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/www.directimpactsolutions.com\/en\/wp-json\/wp\/v2\/comments?post=16162"}],"version-history":[{"count":4,"href":"https:\/\/www.directimpactsolutions.com\/en\/wp-json\/wp\/v2\/posts\/16162\/revisions"}],"predecessor-version":[{"id":19981,"href":"https:\/\/www.directimpactsolutions.com\/en\/wp-json\/wp\/v2\/posts\/16162\/revisions\/19981"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.directimpactsolutions.com\/en\/wp-json\/wp\/v2\/media\/16183"}],"wp:attachment":[{"href":"https:\/\/www.directimpactsolutions.com\/en\/wp-json\/wp\/v2\/media?parent=16162"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.directimpactsolutions.com\/en\/wp-json\/wp\/v2\/categories?post=16162"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.directimpactsolutions.com\/en\/wp-json\/wp\/v2\/tags?post=16162"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}