5 Replies Latest reply on Mar 28, 2014 10:55 AM by RichNasser

    ExecuteSQL function syntax for quotes

    RichNasser

      Title

      ExecuteSQL function syntax for quotes

      Post

           Hi:

           I have read the documentation provided by FileMaker regarding the ExecuteSQL function.  I am having trouble using it in a field calculation as I don't know how to handle the quotes.  For instance, if I want to use a SELECT statement in the function, I have to use "SELECT * FROM...." but if in this statement I need a WHERE status = 'Paid' I get an error.  Does anyone know the correct syntax for quotes usage?


           Thanks

        • 1. Re: ExecuteSQL function syntax for quotes
          philmodjunk

               It's a good practice to enclose all field and table names in double quotes. If you didn't really need them, no harm is done, but SQL can have an issue with certain characters such as a leading underscore and some field names (such as status, I would guess) can be the same as a SQL reserved word and thus must be quoted to be treated as the name of a table occurrence or field instead of a reserved word.

               use:

               WHERE \"status\" = 'Paid'

               the \" combination allows you to insert a double quote character inside a quoted string such as the text used for the query in ExecuteSQL.

          • 2. Re: ExecuteSQL function syntax for quotes
            RichNasser

                 Thanks.   If I have an ExecuteSQL function in my calculation for say a record in my invoice table, how can I reference that particular invoice number field in my ExecuteSQL?  is that what the documentation means by dynamic?

                  

            • 3. Re: ExecuteSQL function syntax for quotes
              philmodjunk

                   Use the ? character in your SQL and then include a reference to that field as an optional parameter after the record and field separator parameters. See the example at the end of the article on ExecuteSQL that you can find in FileMaker Help for how to set that up.

              • 4. Re: ExecuteSQL function syntax for quotes
                RichNasser

                     thanks as always

                • 5. Re: ExecuteSQL function syntax for quotes
                  RichNasser

                       An interesting thing is happening with this ExecuteSQL statement:

                       following the example above - if "...WHERE \"status\" = 'Paid'" refers to a field status that is a calculation, the statement returns ?  

                       when i went back to my status calculation, I had it returning a number and not text - when I changed the calculation in the status field to return text, the ExecuteSQL worked great!