FileMaker Pro 17 Advanced v220.127.116.11, and
FileMaker Pro 16 Advanced v18.104.22.1680
OS and version
Browser - n/a
MacBook Pro 13-inch 2017 and
iMac 27' 2017
ExecuteSQL SQL query fails to return all matching values
How to replicate
Unknown; occurs only with specific data set.
Workaround (if any)
Use UniqueValues ( List ( fieldName ) )
I have two different scripts that are having a problem where ExecuteSQL fails to return all matching values. Both queries are to single tables (no joins) and return unique values of a single field, based on the search criteria. These scripts - and the SQL queries - have been in use and functioning properly for close to 4 years. But in the last few months, the the SQL queries are failing to return all expected values. The returned values are from two separate fields, but both are similar - calculation fields containing concatenated text from several other fields. I've isolated the values that are not being returned. When I changed the script step to use UniqueValues ( List ( fieldname ) ) all expected values were returned.
I found a discussion at FMS 15v3 huge issue : Client-Server Cache gets ... | FileMaker Community that concerned a corrupted cache. But deleting the cache as described did not solve my problem.
I'm showing one of the SQL queries below. If it would be helpful, I would be glad to share my file.
// Built by SQLExplorer. Compliments of SeedCode… Cheers!
Let ( [
// Define Carriage Return Substitution Character
ReturnSub = "\n" ;
// Enable the second line here if you want the header in your results
header = "";
//header = "a.trancheConsolidation__c";
// Define Table variables
aORI = Quote ( GetValue ( Substitute ( GetFieldName ( ORI::_id ) ; "::" ; ¶ ) ; 1 ) ) & " a" ;
// Define Field Variables
aid_unit = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( ORI::_id_unit ) ; "::" ; ¶ ) ; 2 ) ) ;
atrancheConsolidation__c = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( ORI::trancheConsolidation__c ) ; "::" ; ¶ ) ; 2 ) ) ;
// Build SQL Query
"SELECT DISTINCT " & atrancheConsolidation__c & "
FROM " & aORI & "
WHERE " & aid_unit & " = ? " ;
// Run SQL Query
result = ExecuteSQL ( q ; Char ( 9 ) ; "|*|" ; $$UnitID_current ) ] ;
// Clean up carriage returns
List ( header ; Substitute ( result ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ] ) ) )