2 Replies Latest reply on Feb 15, 2012 2:26 PM by LSNOVER

    Using GTRR with ESS records

    hschlossberg

      I have a process that gathers a number of the primary keys for records in a particular ESS table. Once I have that list, I need to go to the set of ESS records represented by those keys. The quickest way to do this is generally via a GTRR, via a relationship from a multi-line global field to the primary key field. This works fine for about 600 keys or less. But much past that FMP returns a SQL error while trying to perform the GTRR against the ESS table ("syntax error near 'WHERE'").

       

      I believe the problem is that SQL Server has a limit of 4000 characters in its query length. There are apparently ways around this, but FMP doesn't take advantage of those methods.

       

      So the question is: are there any other fast methods I could use to get around this limitation and get to my desired found set? (The other obvious method is to loop through the list and create many, many find requests. This works, but the search time goes from about one second up to 90-120 seconds, which is unacceptable.)

       

      Thanks,

      Howard

        • 1. Re: Using GTRR with ESS records
          ian.moree

          i belive that loop is the way to go.

          Matt Navarre uses the loop and has many find requests using his FMSEarchResults and it is super fast as he has demo'ed it to searh wikipedia and it rocks out.

           

          SO i believe that is the way to go, but nothing beats a failure like a trial or vice versa!

           

          -i

          • 2. Re: Using GTRR with ESS records
            LSNOVER

            Howard:

             

            I've found for large sets, I have to loop through the records and build the key list, then I update a utility record in  the SQL database, which marks the records in the found set via a stored procedure.  I then go to the layout and do a find on a FoundSet flag field updated by the stored procedure.  Not great, but it does work.   The trick is creating and destroying flag records in the SQL DB efficiently and keeping tabs for each user.  I wish there were a better way, but at present this is the best I've been able to do. 

             

            I'll have to take a look at Matt's technique and see if it works better.

             

            Regards,

            Lee