Add Records to Found Set

I’m using FMP19 and Win 10. I have two tables - customers and customer_orders. From a layout based on customer_orders I have generated a found set of 20 customers who ordered product X. I want to create 20 corresponding records for product Y.

One way is to loop through the found set, open a new window, create the record, and close the window. Unfortunately, this causes window flashing (not sure if this is a Win-only “bug” or not).

Another way — which I believe would avoid window flashing — would be to use PSOS.

Any other options to avoid window flashing when I create the new records?

Hi Steve,

Not sure if you had this in mind or not, but just in case:

Whichever methodology you use to create the new records (and there are at least a few), a lot of times it is a smoother experience if you grab all the data you need from your found set to create all the records, and then set about the task of creating the new records. This would be as opposed to grabbing the data for just one record, creating that record, then grabbing data for one more record, creating that one, etc..

Another note:

Particularly useful if you need to harvest data from a found set is the use of the GetNthRecord function. This function allows you to harvest data from each record in the found set without actually navigating to it. Though, in this case, it sounds like you might also be able to grab the data you need via SQL, as well.

1 Like

There are many ways to achieve this. My suggestion is but one approach.

I assume that the found set is visible to the user… else why is flashing an issue. I also assume you need to make the new records in a different layout than the one displayed… else why use a different window. Let me know if either assumptions are incorrect.

  1. Start by creating a new off-world processing window for the found set, displaying the creation layout. Using an off-world window minimizes flashing.

  2. Go to the last record in the found set. You will see why in future steps.

  3. Duplicate the last record and change the product X to the product Y. I assume you have auto-entered fields that will update (record ID, creation date / time, etc.).

  4. Omit the record that was just created from the found set.

  5. Omit the record that was duplicated from the found set.

  6. Repeat steps 3 to 5 until the found set is empty.

  7. Close the processing window.

Hope this helps.


Do you use a 'Freeze Window' step in the script that creates the new records?

See the recent discussion of various techniques here: Scripting creation of a record in an un-related table

1 Like

Sorry if what I am adding here is obvious to most developers. I want to ensure that even new and junior developers can understand the "why".

FileMaker displays a found set's next record when one of its records is omitted. Duplicating the last record creates a new last record. Omitting the new and duplicated records displays the last record of the found set again because there is no next record.

This approach eliminates the need to change record, count records, etc. One needs only to check that a found count exists to end the loop.

1 Like

No, the found set does not have to be visible. However, I believe even off-screen windows cause flashing in Windows 10. The new windows are needed due to the looping.

Flashing can occur in-window or in-desktop. You control in-window flashing with the Freeze Window script step, as mentioned by @Torsten. Short of never creating or changing windows, you can not eliminate flashing in-desktop. The problem is more visible in Windows than Mac OS, especially if a maximized window is displayed.

Windows allows for only one maximized window to be visible. All others must be behind the maximized window. Creating a new window automatically de-maximizes the maximized window, creating a very visible change to the user.

Creating a new window is always visible to the user in Windows and Mac OS. A new window, even if off-world, becomes the new active window. The previously active window, if and when visible, changes state to indicate it's no longer the active window. You can not eliminate this change. You can only reduce its impact.

That was my reasoning behind my suggestion to create an off-world window once and only once and have all processing occur in that window. The user will see something twice, once when the window is created, once when it is closed. That is the best that can be done to perform processing in a separate window.

You mentioned that multiple windows are necessary to perform looping. That implies the loop changes the original found set in such a way that the original found set can't be restored were the duplication steps performed in the same window. Copying the found set's customer_orders IDs in a variable would be a good way of no longer requiring the found set. You could then add a super-loop to process each ID instead of relying on restoring the found set.

Of course, using PSOS becomes viable when you copy IDs to a variable. That will completely avoid flashing. It will even perform faster on large found sets and over WAN.

Hope this helps.

1 Like

Thank you for the comprehensive response; I’ll try collecting the customer_ID values into a variable, opening a single window, and creating records.

I’ll also experiment with PSOS - have not done that yet.

I used a custom function (FoundList) to save the customer_IDs to $listA and $listB. Then I compared the two lists using another custom function (ExcludeValues) to produce a list of customers needing product B.

Tomorrow I’ll write and debug script steps to create the new records.

Thanks for your help.

I have used Freeze Window in my new script, yes. Even though in some cases FW may not really be required because my found set is relatively small and FM is fast enough that the layout change is invisible.

My updated script now uses Freeze Window, changes layouts, runs two finds, compares the found sets, creates needed records, and returns to the original layout.

I’ve completely eliminated the looping and creation of new windows.

Thanks to everyone who provided ideas and expertise.