12 Replies Latest reply on Feb 7, 2014 7:29 AM by PeggyConant

    Quickest/Safest way to transfer a found set of records from one window to the next

    PeterWindle

      Hello all,

       

      I have a find script that creates a new window, performs a find then returns the found set, closing off the original window.

       

      This works very well, expet that if a user starts in one table, then performs a search in another table, the original found set in the original table has changed.

       

      I was thinking that perhaps I should change the script to return the found set of records to the original window, rather than close off the original window...

       

      so, what would be the quickest/safest way of doing this?

       

      I was thinking that I can use "list" to grab all the record ID's then perform a find using the list values in the original window...?

       

      If ths found set is large, I'm thiking that this could be dangerous??

       

      Any other suggestions?

        • 1. Re: Quickest/Safest way to transfer a found set of records from one window to the next
          erolst

          You could

          • 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.

          • 2. Re: Quickest/Safest way to transfer a found set of records from one window to the next
            Mike Duncan

            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.

            • 3. Re: Quickest/Safest way to transfer a found set of records from one window to the next
              flukey

              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.

              • 4. Re: Quickest/Safest way to transfer a found set of records from one window to the next
                BruceRobertson

                Except ExecuteSQL knows absolutely nothing abou the found set.

                • 5. Re: Quickest/Safest way to transfer a found set of records from one window to the next
                  BruceRobertson

                  Mike: FM13 List Of as mentioned by erolst is the new champeen for speed in getting a list across a found set.

                  • 6. Re: Quickest/Safest way to transfer a found set of records from one window to the next
                    RalphLearmont

                    Hello Peter,

                     

                    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.

                     

                     

                    That's it.

                     

                    Regards

                    Ralph Learmont

                    • 7. Re: Quickest/Safest way to transfer a found set of records from one window to the next
                      BruceRobertson

                      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.

                      • 8. Re: Quickest/Safest way to transfer a found set of records from one window to the next
                        PeterWindle

                        thanks everyone, you've all given me a lot to consider...

                         

                        ;-)

                        • 9. Re: Quickest/Safest way to transfer a found set of records from one window to the next
                          siplus

                          google for

                           

                          filemaker using gtrr to switch togs

                          • 10. Re: Quickest/Safest way to transfer a found set of records from one window to the next
                            BobGossom

                            Peter,

                             

                            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.

                             

                            Bob Gossom

                            • 11. Re: Quickest/Safest way to transfer a found set of records from one window to the next
                              PeggyConant

                              Bruce,

                               

                              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.

                               

                              Peggy Conant

                              • 12. Re: Quickest/Safest way to transfer a found set of records from one window to the next
                                PeggyConant

                                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?

                                 

                                Peggy