2 Replies Latest reply on Dec 5, 2014 8:47 AM by CristosLianides-Chin

    ExecuteSQL ok in Data Viewer but not in Calculated Field

    CristosLianides-Chin

      Title

      ExecuteSQL ok in Data Viewer but not in Calculated Field

      Post

      I'm trying to use an ExecuteSQL statement to perform a COUNT without needing to create unnecessary relationships. Although the calculation works perfectly when run in the Data Viewer, I only get a "?" when creating it in a calculated field. Any suggestions?

      Here's the calculation:

      Let ([
          ~sqlQuery = "SELECT COUNT(xRequest) FROM HS_REQUEST WHERE fOpen=1 AND xStatus=? AND xPersonAssignedTo=?"
      ];
          ExecuteSQL ( ~sqlQuery ; "" ; "" ; "19"; hsID)
      )

      NOTE: The table referenced is an external SQL table, not a FileMaker table.

      Relevant screenshots:

      http://snag.gy/COG6G.jpg

      http://snag.gy/98PNg.jpg

        • 1. Re: ExecuteSQL ok in Data Viewer but not in Calculated Field
          philmodjunk

          A ? result usually means a SQL syntax error but that seems unlikely here. It can also mean that the field is too narrow to display the field value. To see if that might be the case, click into the field. If it's a problem with the field width, the question mark will be replaced with the value of the field.

          And while I wouldn't expect it to produce a ? result, make sure that your calculation field is unstored or it may not update automatically when other records are added/removed/changed in ways that should change the result returned by this query.

          • 2. Re: ExecuteSQL ok in Data Viewer but not in Calculated Field
            CristosLianides-Chin

            I tried clicking in the field and it remained a question mark. (Copy-pasting the contents to plaintext file yielded the same)

            Hadn't remembered to set the calculation to "unstored" -- oddly enough, changing the storage option to "Unstored" fixed the problem, and it now shows the correct number. I am also mystified, but glad it worked!

            Thanks for the help!