ExecuteSQL - how to use search results as records instead of text

Discussion created by dburnham on Apr 10, 2013
Latest reply on Apr 11, 2013 by PalmDBS

Still learning my way around the new ExecuteSQL function. Partial success, but I wonder if anybody would know a way for me to improve my result:


I have a FileMaker database with an external data source that is connected via ODBC. It's got 66,000 records. All the text in all the records is in UPPER case. My goal is to give my users a set of global fields on a layout in which they can enter whatever search criteria they want, and use ExecuteSQL (instead of a filtered portal with a god-awful calculation) to present the results.


Initially, I was putting the SQL result into a global field, exporting field contents to tab-delimited text, and then importing those "record" into a table that presents the results in a portal. It's fine when the search results are not lengthy, but it always requires deleting those temporary records before creating new ones, and if the search results are thousands of records, it's painfully slow. The only advantage I see is that in a portal I can format the position of the returned text more easily and possibly use the portal rows for GTRR operations later.


So instead, I just made one big field that occupies the same space as the portal would have. The SQL result goes into that field, but because the field values are not fixed-length, I needed to use the Ruler to create tab-stops. That worked fine, until the next SQL operation replaced all the text. In other words, the tab-stops seem to be inherent in the text within the field, not the field itself. (Unless I'm mistaken, in which case there is a lot of egg on my face.)


My workaround has been to insert a "header row" with tab stops and append the SQL result to the first row of text, and that seems to work. It's just not a bulletproof way of doing this.


One other issue that I discovered is that the target table I am using for the SQL query has a 2 number fields. I don't see anything in the field attributes that would indicate any difference between them, but then, FileMaker may not present everything that is written in the MySQL data table. Anyway, one field is called Quantity, and ExecuteSQL shows me those values without any problem. But the other field is called Key and it contains serial numbers for the entire data table, and if I try to return those values with ExecuteSQL I always get the annoying ? result. Can't figure out why that one field resists being queried and returned.


Thanks in advance for any suggestions, especially if there is a way to convert the SQL result to something that would behave like temporary records, instead of just text in a field.