4 Replies Latest reply on Oct 11, 2015 11:11 AM by jenbee

    Help to decipher an ExecuteSQL statement in Starter Solution calc.

    jenbee

      Hi

      I'm on the 'invoices' starter solution in FMP Advanced 14.0.2

       

      and the TotalAllSQL field in Company Dashboard table.

      They've used a calculation field with a SQL SUM function to add the values in a portal as an alternative to creating a report with grouped data and subtotals.  Here's the definition of calculation field: TotalAllSQL

      Let  (
      [
      month =  Company Dashboard::Current Month ;
      year =  Company Dashboard::Current Year ;
      status =  Status Filter ;
      decimal = Left ( Evaluate ( 1/2 ) ; 1 ) ;
      SQL =  ExecuteSQL (
           "
           SELECT SUM ( a.\"Total\" )
           FROM \"Invoices\" a " &
           If  (
            IsEmpty ( status ) ;
            "WHERE a.\"Month\" = ? AND a.\"Year\" = ? AND a.\"Status Order\" IN ( 0 ,1, 2 ) " ;
            "WHERE a.\"Month\" = ? AND a.\"Year\" = ? AND a.\"Status Order\" = ? "
            )
           ; "" ; ""  ;
           month ; year ; status
           )
      ] ;

      If ( SQL ; Substitute ( SQL; "." ; decimal ) ; 0 )

      )

       

       

       

       

      I'm just trying to work my way through understanding it.  I've done a basic SQL course before, but have way more experience with FileMaker on its own than I do with SQL.  Firstly what's the purpose of the '  a. ' in the part that I've highlighted red?  That's what I want to know , hope yous can help

        • 1. Re: Help to decipher an ExecuteSQL statement in Starter Solution calc.
          erolst

          jenbee wrote:

           

               SELECT SUM ( a.\"Total\" )
               FROM \"Invoices\" a " &

          Firstly what's the purpose of the '  a. ' in the part that I've highlighted red?

           

           

          a.\"Total\" is a fully qualified field name in SQL dot syntax, using the alias for the TO name Invoices, defined in the next line:

          \"Invoices\" a (which you could also write as \"Invoices\" AS a).

           

          So in brief, this is a reference to the field Invoices::Total in SQL syntax.

          • 2. Re: Help to decipher an ExecuteSQL statement in Starter Solution calc.
            jenbee

            Ah yes that makes sense erolst.  Thankyou.  It's been a while since I did the course and practised writing SQL.

             

             

            Yes I remember now.  It's annoying that you can do shorthand code and leave bits out.. If it said " AS a " , that would have made so much more sense to me.

             

            Question 2: I think that a backslash is always needed in front of a quotation mark within FileMaker if you don't intend the quotation marks to return actual text , did I get that bit right?

             

            Question 3: Apparently a question mark in SQL means a variable.. So does that mean this bit:

             

                  "WHERE a.\"Month\" = ? AND a.\"Year\" = ?

             

            is another way of saying: Set Invoices::Month and Invoices::Year as variables  ?

             

             

            • 3. Re: Help to decipher an ExecuteSQL statement in Starter Solution calc.
              erolst

              jenbee wrote:

              Question 2: I think that a backslash is always needed in front of a quotation mark within FileMaker if you don't intend the quotation marks to return actual text , did I get that bit right?

               

              A Backslash is the escape character, which you can translate as a command to “treat the next character literally”.

               

              Since a " denotes the beginning of a string, without the escape character the next " would be seen as denoting end of the string, which in this case is 1) not what you mean, and 2) would (in almost all cases) trip the parser because the rest of the expression isn't valid.

               

              So, this is my convoluted way of saying, "yes, you got that bit right" (and now you know why )

               

              jenbee wrote:

              Question 3: Apparently a question mark in SQL means a variable.. So does that mean this bit:

               

                    "WHERE a.\"Month\" = ? AND a.\"Year\" = ?

               

              is another way of saying: Set Invoices::Month and Invoices::Year as variables  ?

               

              It means “find all records where the value of the field equals the value of the variable associated (positionally) with the respective placeholder".

               

              Generically,

               

              WHERE textFieldA = ? and numberFieldB = ?

              " ; "" ; "" ; "someString" ; aNumber

               

              will find records where textFieldA = "someString" and numberFieldB = aNumber

               

              Let me refer you to the official documentation:

              ExecuteSQL