Is there any way that allows me to make a search using Execute SQL that searches ONLY in the found set of records?
ExecuteSQL ( " SELECT FirstName
; "" ; "" )
Sorry, no. SQL by its very nature knows nothing about what you see so it doesn't have a 'Context' and does not use any of the Table Occurrences in the TO graph. All relationships are defined in the SQL as JOINS of various kinds.
You could include your Find criteria in the WHERE clause. Of course if the Found set is 'add-hoc', made by the user omitting records, then there is no Find criteria for that.
Another possible way is to enter a value in a field to Mark them as 'Found' then you could include that value in the WHERE clause of the SQL Query. This isn't very Multi-user friendly unless you ADD a user specific value to the field and clear only that value after the query.
There was a related thread a few days ago that may be of some assistance:
Kevin Frank wrote a custom function to gather the record id’s from a found set for use in an ExecuteSQL statement. Maybe it could be of use here.
Here is the link.. http://www.filemakerhacks.com/?p=1065
Check out the “FoundSetToSQL” function.
For larger Found sets, the fastest method I've found is setting up a layout with just the key field. Go to that layout and use the Copy all records function, you can then delimit the resulting text field and use in an " IN " Clause. Still not the speediest, but its the best we have at the moment. I really wish FMI would provide a "FoundSet" flag field as a pseudo column for use with the ExecuteSQL function.
I have set a calc field to
Let ( mf = match field in table A ;
"SELECT field(s) FROM table B WHERE matching field=? ; "" ; "" ; mf )
and it produces a list of related values...it denormalizes table A but sometimes its the right choice.
>> Another possible way is to enter a value in a field to Mark them as 'Found' then you could include that value in the WHERE
Yes. I was using that work around, but I was wondering if there´s something that can be done inside the SQL query.
Retrieving data ...