Jump to content

ExecuteSQL fails to return matching record


This topic is 2549 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 ] ; [ "|*|" ; ¶ ]  ) )  )

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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 

Link to comment
Share on other sites

This topic is 2549 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.