ExecuteSQL fails to return matching record

[Tom R.]

I am dealing with a very weird condition in my solution where an ExecuteSQL statement was failing to return a matching record. 

My solution tracks documents such as deeds and leases, where a document can have N grantors and N grantees. So I have a Parties table, a Documents table, and identical join tables, Grantors and Grantees. 

I noticed the problem when printing out a report, and a particular document didn't have any grantees. The report uses the executeSQL query in question. So I looked for that document in the database, and the particular grantee appeared as expected in the portal. I looked in the Grantee join table, and there was a matching record, complete with the ID of the Party and the ID of the document. I then checked, and every record where that particular party appeared, in the report that party was omitted for every record where it should have appeared. In one record, that party was 1 of 6 grantees. The ExecuteSQL statement returned the other 5 parties, but not that particular one.

In the course of troubleshooting, I created a new record for that party, nameFirst = Mollie, nameLast = Sternberg. Then I added the new Mollie Sternberg record to those documents. The original Mollie Sternberg record, I set the formerly empty field nameMaiden to "Bugged". Then I re-ran the report. The new Mollie Sternberg record appeared as expected, but so did the original record, Mollie Sternberg Bugged. So I don't know why changing a field in the party table for that record would cause the ExecuteSQL statement to return it, when it hadn't been returning before. 

I'm freaked out, because I identified a second party that was having the same issue. Changing that party's original record to nameMaiden = Bugged also caused it start showing up, too. But I don't know how many other Party records may be having the same problem, and I have no way to identify a characteristic of the Party record that may have caused the probelm, so that I can identify potential problem Party records. 

Is this a bug in FileMaker? Or an issue with my SQL query? Any thoughts or suggestions most appreciated. The query is below.

Thank you,

Tom

// Find Document Grantors

// 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 = "";

// Define Table variables
a@GRANTORS = Quote ( GetValue ( Substitute ( GetFieldName ( @Grantors::_id ) ; "::" ; ¶ ) ; 1 ) ) & " a" ;
b@PARTIES = Quote ( GetValue ( Substitute ( GetFieldName ( @Parties::_id ) ; "::" ; ¶ ) ; 1 ) ) & " b" ;

// Define Field Variables
acapacity = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @Grantors::capacity ) ; "::" ; ¶ ) ; 2 ) ) ;
a_id_parent = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @Grantors::_id_parent ) ; "::" ; ¶ ) ; 2 ) ) ;
a_id_party = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @Grantors::_id_party ) ; "::" ; ¶ ) ; 2 ) ) ;
aakaFull__c = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @Grantors::akaFull__c ) ; "::" ; ¶ ) ; 2 ) ) ;
ainterestConveyed = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @Grantors::interestConveyed ) ; "::" ; ¶ ) ; 2 ) ) ;
b_id = "b." & Quote ( GetValue ( Substitute ( GetFieldName ( @Parties::_id ) ; "::" ; ¶ ) ; 2 ) ) ;
bnameDisplay = "b." & Quote ( GetValue ( Substitute ( GetFieldName ( @Parties::nameDisplay ) ; "::" ; ¶ ) ; 2 ) ) ;

// Build SQL Query
q = 
"SELECT " & bnameDisplay & " , " & aakaFull__c & " , " & acapacity & " , " & ainterestConveyed & " 
FROM " & a@GRANTORS & " 
INNER JOIN " & b@PARTIES & " ON " & a_id_party & " = " & b_id & " 
WHERE " & a_id_parent & " = ? " ;

// Run SQL Query
result = ExecuteSQL ( q ; Char ( 9 ) ; "|*|" ; $_id_assignment ) ] ;  

// Clean up carriage returns
 List ( header ; Substitute ( result ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ]  ) )  )

[Fitch]

Have you considered it might be your data that's the issue? Or the $_id_assignment variable?

I'd recommend simplifying the query so you can run some tests in the data viewer and/or in a test script.

In said script, I'd create the query in a variable (e.g., $query) so I can see it in the data viewer.

[Tom R.]

Fitch, thanks for the reply.

I did a manual search is the Grantees table, using the _id_assignment (foreign key) value, which returned six join records, including the one for the original Mollie Sternberg record; but the ExecuteSQL query omitted the Mollie Sternberg record, even though it returned the other 5 records, which had the identical foreign key as the Mollie Sternberg record. So unless I'm missing something, the data was correct and the ExecuteSQL querry should  have returned all six records.

[Wim Decorte]

A couple of things could be the issue:

 

- SQL queries are case sensitive whereas native FM finds are not case sensitive.  So if the _id_assigment is not numeric that can cause a discrepancy.

- if you use PSoS to do the query but the user has not committed the record yet then server does not know about it and will return the results minus the user's record.  So always do a commit before the SQL query.

[Tom R.]

Wim, 

Thank you for the reply. My ID fields are all numeric, and I'm currently the only user so I don't think it would have to do with that. A guy on facebook, Richard Christian, pointed out a possible bug in FMS 15v3 that I think may be the culprit. https://community.filemaker.com/thread/168254.

Thanks,

Tom