Using GTRR with ESS records

Discussion created by hschlossberg on Feb 13, 2012
Latest reply on Feb 15, 2012 by LSNOVER

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