• create a global text field
• create a relationship from some table to your target table/TO, using the global field and the target primary key as match fields
In your script
• gather the IDs of the found set
• upon returning to the original window, set the global field to your ID list
• Go to Related Record(s)
You cannot use List () to just “grab’ a list of field values for the found set in one step; you have to do it in a loop. There are techniques that go through the found set and build up the list, either “physically” with Go to Record [ next ], or “logically” with GetNthRecord.
In FM13 you can simply use the new (and very handy) summary field option List of.
This is a problem I've wrestled with and haven't found a perfect answer, but the closest I've come that will also consider arbitrarilly large found sets, that also is fairly fast, is to import to a temp table (with replacing) and then go to related records from there.
You could do a matching import again to get to the found set, but of course that will trigger auto enter fields to update, like modification date, which you probably don't want.
This also works across files where the target may be in a different file than the originating window.
Instead of using the List function, use the ExecuteSQL to build the list of IDs and save them to a text field. Then just use the GTRR command as described above to go to the found set.
Except ExecuteSQL knows absolutely nothing abou the found set.
Mike: FM13 List Of as mentioned by erolst is the new champeen for speed in getting a list across a found set.
Here's an alternative technique for saving and restoring a found set of records. It's very fast over large record sets and it's not too difficult to set up. Rather than use another window, this method uses another layout.
It doesn't matter how the set of records is arrived at (it's not restricted to the result of a Find). It can be a set of records after doing a combination of things such as Finds, omitting records, adding records etc.
For example, say you have an "Invoices" table, and you have a need to store a set of Invoices...
Create a new layout named "StoredInvoices", based on a new Table Occurrence named "StoredInvoices"
This new Table Occurrence named "StoredInvoices" is defined in 'Manage Database' by duplicating the "Invoices" TO and naming it "StoredInvoices". In other words you need two Table Occurrences based on "Invoices". You will have "Invoices" and "StoredInvoices". There's NO need to make a relationship between them.
On the "Invoices" layout make a button labelled "SAVE this set of records". Specify this button (in Layout mode) to "Go to Related Record", and set options to:
Get related record from "Invoices"
Show record using layout: "StoredInvoices"
Show only related records
Match all records in current found set.
Likewise, on the "StoredInvoices" layout make a button labelled "RESTORE this set of records". Specify this button (in Layout mode) to "Go to Related Record", and set options to:
Get related record from "StoredInvoices"
Show record using layout: "Invoices"
Show only related records
Match all records in current found set.
StoringFoundSet.zip 43.4 K
That is an excellent method; as long as you're not in a different window. Peter's original requirement was to begin with a new window.
Quite possibly; because he thought that was the only way to preserve the original found set. If he's willing to stay within one window (but different layouts) then Ralph's method works great.
thanks everyone, you've all given me a lot to consider...
filemaker using gtrr to switch togs
It's possible you could solve this problem pretty easily with some windowing. Instead of closing the original window, you could rename and hide it. When you are done, and want to return, simply rename it and bring it back. As long as your subsequent programming happens in a different window, your original found count will remain.
We do something like this all of the time. The user will be looking at a layout/found count and hits a button that requires a bunch of processing. We spawn a new window in negative space (left/top at -30,000), do all of the processing in that window and close it when we're done. All the user sees is their window losing focus for a moment or two (ususally), and then they are back where they started.
On the other hand, in the "old" days one of the ways we used to do this was to create a multiple record find. This technique would enter Find mode and then simply loop through the keys and create a new find request for each key, and then perform the find. Seems a little weird, but it was very fast and reliable. I forget the largest count we did with this, but I know it was more than 10,000 separate requests in one find. You could store the keys in a global variable or global field.
Of course if you store them in a global field, you could also do a GTRR (all) to get back to the original found count.
I am employing the Summary List Of feature in a solution that is a front-end for a MySQL database using an ESS connection. On each of my MySQL tables, I have added a summary field that provides List Of values so that I can grab those and use them via a native FM globals table anytime I need to identify a found set. It is indeed a great feature, particularly in my abstracted quick find script. I was hoping that I could make us of List Of fields widely in my solutions, but I am stymied now by an issue I think caused by the List Of fields.
Here is where I run into problems: I have a MySQL table with 65000 records. I go to a layout with a found set of 650 of those 65000 records.. I click on a popover to display related data from another table for which there may or may not be a related record. The first act of clicking on the popover button in that found set, without any associated script triggers, returns the popover after a minute or two. Not good.
Since no script is running, I can't really verify what is being done in the background using script debugger but I have seen a sorting dialog box pop up during this process on a bigger record set. I assume that if I have "list of" fields in both my current table and on the table being called in the popover that the Summary List of fields are forced to evaluate during this operation. This may work fine for local FM tables but in the ESS environment this is problematic and I haven't had a chance to apply this to a similar set of native FM records.
To make sure that no unneeded sorting was in play, I set things so that neither the portal on the popover nor the TOs driving displays on the popover are "sorted." and I made sure that the list layout that I am has no summary parts. I could imagine that all of those things might drive an automatic evaluation of the summary "list of" fields.
Should this post be a separate thread under FM 13 Summary List Of ? Any thoughts on how to use this great new feature globally and efficienty without getting bogged down would be welcome.
In follow up to my post yesterday, I changed a TO driving a portal on the popover which was beset by the sluggish processing and I think that did the trick. Seems to be working fine now. I was concerned that the evaluation of List Of field was causing this. I would be curious to know under what conditions does the List of field evaluate. I assume that it happens anytime you have a table sorting by the field which is called by the List Of field. Is that correct?