7 Replies Latest reply on Apr 16, 2013 10:29 AM by JohnSindelar

    ExecuteSQL using the Found Set (blog)

    JohnSindelar

      Yes!…well maybe not.

       

      Jason's put together some very cool techniques here that actually work...but may not be applicable in the real world. Some very productive stuff along the way:

       

      http://seedcodenext.wordpress.com/2013/04/08/executesql-using-the-found-set-yes-well-maybe-not/

       

      Enjoy!

        • 1. Re: ExecuteSQL using the Found Set (blog)
          LSNOVER

          Ah, interesting technique attempting to use the Snapshot info. to get the unique ids.

           

          I'm not quite clear on where the performance is breaking down.   Can't all the IDs be formatted into a comma delimited list and used in a simple but long "IN" statement?  I.e. do you have to use the Betweens?  Is there a performance benefit for doing so?  Is there a narrow limit on how big the Execute SQl text can be?

           

          This is really a pet peave of mine.  The Found Set is so integral to how Filemaker works, I do not understand why FMI doesn't build a Found_Sit pseudo column into ExecuteSQL so that we can use it in a straightforward way.  Also, we should be able to use the "List" function in the scope of the main table for a layout to get all the values of a particular field for the active found set, without having to resort to self relationships, going to related records, or looping through the found set.  I've seen notes from Filemaker developers asking for this going back YEARS and the new ExecuteSQL functionality just compounds the need for some easy and fast mechanism to get this data.

           

          Using the snapshot is worthy of some more testing.  But we really should not have to resort to such tricks.

           

          Cheers!

          Lee

          • 3. Re: ExecuteSQL using the Found Set (blog)
            user10625

            Sometime a long while ago, Bruce Robertson handed out a demo file of various ways of collecting a found set of IDs (or whatevers).   I think the winner was doing small sets (10000?) of records using a Replace Records trick:

             

             

            Replace Records [  DummyGlobalField ;

                Let (

                       $IDCollector = $IDCollector & "¶" MyTable::IDField;

                      "")

             

             

             

            (I've left out code we could add to eliminate hanging "¶"  for clarity's sake.)

             

            The Replace-Records filling a global with "", doesn't do much itself, but as a side effect, it collects all of the IDs in $IDCollector.  You can then do the old:

             

            Substitute ( $IDCollector;  "¶";  ",  " ) 

             

             

            to make it  IN-able in an ExecuteSQL.

             

            Anyway, I use this trick all the time to do ExecuteSQL on found sets.

             

            Thanks,

            Christopher Bailey

            Baytaflow

            Boston, MA

             

            • 4. Re: ExecuteSQL using the Found Set (blog)
              jormond

              Hey Malcom...lost your post...assuming to the email-post-gremilins.

              • 5. Re: ExecuteSQL using the Found Set (blog)
                Malcolm

                Here's the lost post:

                 

                This is really a pet peave of mine.  The Found Set is so integral to how Filemaker works, I do not understand why FMI doesn't build a Found_Sit pseudo column into ExecuteSQL so that we can use it in a straightforward way.  Also, we should be able to use the "List" function in the scope of the main table for a layout to get all the values of a particular field for the active found set, without having to resort to self relationships, going to related records, or looping through the found set.

                 

                Ditto. I can't believe we are alone either. I've put in feature requests for this valuable function but haven't seen it yet.

                 

                Malcolm

                 

                 

                (Another pet peeve of mine is forum software that is just clever enough to be dangerous)

                • 6. Re: ExecuteSQL using the Found Set (blog)
                  BruceRobertson

                  Glad the demo file helped. It was an adapation of a nice example started by Ralph Learmont.

                   

                  The actual overall peformance winner was Ralph's example, which used export-import.

                   

                  The global-replace technique can be handy for small found sets.

                   

                  I agree with Malcolm though - why don't we have proper fast native functions?