9 Replies Latest reply on Jun 15, 2015 2:18 PM by philmodjunk

    ExecuteSQL noob, keep getting "?"

    jdevans

      Title

      ExecuteSQL noob, keep getting "?"

      Post

      I created an ExecuteSQL statement, and it works when I just create it right inside the data viewer. But when I copy that entire statement, and apply it to the calculation engine for a new field, all I get is "?". I have tried multiple ways of rewriting the ExecuteSQL statement, but to no avail.

      I've tried adjusting whether or not the referenced TO and or fields are wrapped in quotes. I've tried renaming table occurrences to include no spaces. I'm not sure what the rules are on "Calculation Result" data type. For example if I define a new field, type Calculation, name it sql_test and then write:

      SELECT date_field, number_field FROM table_ccurrence

      What should I select as the "Calculation Result" in the Calculaton definition dialog? I have in the select statement a date field and a number field, so should the calculation result be text?

        • 1. Re: ExecuteSQL noob, keep getting "?"
          jdevans

          not cool Kobutor subujgolafrown

          • 2. Re: ExecuteSQL noob, keep getting "?"
            philmodjunk

            The off topic posts--which contained links that might have installed Malware, have been suspended.

            Can you copy and paste the exact expression that you are using that results in a ?

            (also make sure that your field is wide enough to display the result as that can also result in a ?)

            And you might find this custom function helpful:

            If (

            //the sql call results in an error, return empty so the error will be returned
            _executeSQL = "?" ; "" ;

            //the sql call is executed correctly, just return the result
            _executeSQL
            )

            // ===================================
            /*

                This function is published on FileMaker Custom Functions
                to check for updates and provide feedback and bug reports
                please visit http://www.fmfunctions.com/fid/335

                Prototype: sql.debug( _executeSQL )
                Function Author: Andries Heylen (http://www.fmfunctions.com/mid/57)
                Last updated: 28 July 2012
                Version: 2.2

            */
            // ===================================

            If you enclose a call to ExecuteSQL inside a call to this custom function in the data viewer, click Monitor and then reopen the watch expression, you'll get actual error text returned inside the Evaluation box.

            • 3. Re: ExecuteSQL noob, keep getting "?"
              jdevans

              OK, I was watching the field name in the Data Viewer, which showed the ?.

              But I also used the exact same expression in Data Viewer but not attached to a field, and it reports the data....

               

              test_sql is a calculation field with the calculation set to:

              ExecuteSQL (

              "
              SELECT DISTINCT fk_employee_id FROM All_hours
              "

              ; ", "; "¶¶" )

              • 4. Re: ExecuteSQL noob, keep getting "?"
                philmodjunk

                Could it be that your calculation field is not set up as an Unstored Calculation field?

                • 5. Re: ExecuteSQL noob, keep getting "?"
                  jdevans

                  I used the Custom Function, and now I don't even get the "?"

                  All I get is a blank. Nothing at all.

                  I just don't understand how that if I open Data View, and hit the + sign, and past the ExecuteSQL Instructions into the Expression Dialog box I can immediately see what I expect to see- 7 Distinct values. But when I create a new field in the table where these values originate, and paste the exact same code into its ExecuteSQL calculation, nothing happens. Is there some key piece that I am totally missing here?

                  • 6. Re: ExecuteSQL noob, keep getting "?"
                    jdevans

                    Seems like that "Unstored Calculation Field" would be at the top of the list in "How to use ExecuteSQL" in big bold letters. I've searched, read, watched, and nowhere in anything that I read do I remember seeing or hearing anything about that key piece of information. Now that I know, I'll be re-reading what I've found up to this point.

                    • 7. Re: ExecuteSQL noob, keep getting "?"
                      philmodjunk

                      Well, it's info that is not specific to ExecuteSQL.

                      Any stored calculation field that does not specifically reference another field does not have a "trigger" to force it to re-evaluate.

                      a calculation such as:

                      Get ( CurrentDate )

                      or

                      Get ( FoundCount )

                      will have the same limitation.

                      On the other hand, if you used ExecuteSQL, with the optional ? parameter and inserted a reference to a field to use as that parameter, you could have still used a stored calculation and changes to the referenced field would trigger a requery of the data--just like any other stored calculation field.

                      A useful resource: Go to the Filemaker Hacks website and look up a presentation by Darren Terry on "shaking the dependency tree".

                      • 8. Re: ExecuteSQL noob, keep getting "?"
                        jdevans

                        http://www.filemaker.com/help/12/fmp/html/func_ref3.33.6.html This doesn't mention it.

                         I'm not griping at you Phil. You are very helpful.

                        I'm just wondering out-loud how on earth are you supposed to just know this about making sure your calculation field is set to Unstored before ExecuteSQL will work?

                        • 9. Re: ExecuteSQL noob, keep getting "?"
                          philmodjunk

                          But as I just posted. This is not actually the case. Only if you are using ExecuteSQL in a fashion that lacks direct references to a relevant field in the same record.

                          This info is not in a help reference on ExecuteSQL as it applies to all stored calculation fields, not just ExecuteSQL. And yes, this is a key fact that could be better documented.

                          It was the Darren Terry presentation--that I got via a digFM meeting at FMI headquarters that was the "aha" moment that explained what was till then a "memorize by rote" list of "don't make this a stored calculation" situations that I had amassed over the years...