AnsweredAssumed Answered

ExecuteSQL on a Table Occurrence from an external FM Database

Question asked by John Funk on Jan 16, 2015
Latest reply on Jan 16, 2015 by John Funk

I am stumped.

I am trying to debug an SQL statement that works only some of the time, it references a root table (Jobs) from an external FM database with the table placed and (named Jobs) on the relationship diagram of the local file.

SELECT COUNT("Constant") FROM "Jobs" WHERE "Market" = 'Automotive' << this does not work it produces a ?

SELECT COUNT("Constant") FROM "Jobs" WHERE "Market" = 'Calibration' << this does work

 

As a test, I created a local table called Jobslocal with the same fields and data as Jobs in the external db.

I am using the same SQL statement only changing the table name that works 100% of the time.

SELECT COUNT("Constant") FROM "Jobslocal" WHERE "Market" = 'Automotive' << this does work

SELECT COUNT("Constant") FROM "Jobslocal" WHERE "Market" = 'Calibration' << this does work

 

It seems ExecuteSQL is buggy when working with tables that do not reside inside the local file. All FM documentation says as long as the table is on the relationship diagram it will work like a local table and the local table name is used. I am creating the SQL Statements dynamically and I have checked all my variables and how the SQL is being created. As you can see from above the only difference in the two statements above is the table name.

 

My question is, is there any known bugs in FileMaker 12/13 in this area or have others experienced this same problem?

Outcomes