2 Replies Latest reply on Jan 16, 2015 11:43 AM by John Funk

    ExecuteSQL on a Table Occurrence from an external FM Database

    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?

        • 1. Re: ExecuteSQL on a Table Occurrence from an external FM Database
          taylorsharpe

          I've not noticed this problem before.

           

          Is there any chance that the Constant field is effectively a primary key?  If so, just do a Count ( * ) because that is much faster.  Or add the Constant field constraint to the WHERE clause and use the Count ( * ) function.  It will probably perform much faster.

           

          If this is only happening in this one circumstance, you could remove the index and add it back in to see if something has gotten corrupt.  This often happens to non-server hosted files if they are hosted for long periods of time and may have had times when the FileMaker app had to be closed forcefully.

           

          When you say you are having issues with tables from files that do not reside in side the local file, are these FileMaker files or ESS?

          • 2. Re: ExecuteSQL on a Table Occurrence from an external FM Database
            John Funk

            Taylor,

            All sources are FileMaker. The Count (Constant) is really just a test, eventually it will be a sum of an invoice total.

            Your suggestion of looking at the Indexes was the key.....

            I turned OFF the indexing for the Market field and ran my scripts, and all counts showed as expected!!

            When I went back into the source database, the Market field was turned to minimal, so FileMaker automatically reset that.

            Resolved!!

            Thank you!