5 Replies Latest reply on Oct 2, 2013 10:32 AM by philmodjunk

    Calculated field using ExecuteSQL -  can't get it to work

    gregdc

      Title

      Calculated field using ExecuteSQL -  can't get it to work

      Post

           First this is a small part of my database - tables are:

           Student -( 1 to M) ->StudentClass -( 1 to M) -> Classes

           StudentClass is what I call a helper table that has the following fields:

           Student_ID - key to Student table

           Class_ID - key to Classes table

               These two fields create a unique combination.  Student_ID is unique in Student, and Class_ID is unique in Classes. 

            

           I want to put a calculated field in Students that gives the total count of Classes where the Classes type = Adv or Grad

           I tried putting in a query like this:

           GetAsNumber (

           Let ([ $query = "Select Count(Class_ID) From StudentClass JOIN Classes ON StudentClass::Class_ID = Classes::Class_ID  WHERE ClassType IN ("Adv", "Grad") AND StudentClass::Student_ID =?";

           $result = ExecuteSQL($query;"";"";Student_ID)

           ]; $result
           ))

            

           The results were either ? or blank.

            

           Will anyone out there, that has created a calculated field similar to this, please point me in the right direction.   I am using FM Pro 12 Advanced. 

           GregDC

        • 1. Re: Calculated field using ExecuteSQL -  can't get it to work
          philmodjunk

               StudentClass is what we call a "join" table. It makes possible a many to many relationship between students and Classes. In some systems, this table might be called "enrollment".

               I see syntax issues in the string expression you use for your SQL:

               "Select Count(Class_ID) From StudentClass JOIN Classes ON StudentClass::Class_ID = Classes::Class_ID  WHERE Classes.ClassType IN (\"Adv\", \"Grad\") AND StudentClass.Student_ID =?"

          • 2. Re: Calculated field using ExecuteSQL -  can't get it to work
            gregdc

                 Phil, Thanks for the help!   I am still stuck but as least I had something to kickstart me this morning.  OK so after 4 hours (minus interruptions) I am still getting the ? in the Calculated field in the Student table.   This is my latest attempt:

                 GetAsNumber (

                 Let (

                 $result = ExecuteSQL (
                 "SELECT Count(*)
                 FROM StudentClass JOIN Classes ON StudentClass::Class_Id = Classes::Class_Id
                 WHERE StudentClass::Student_Id = ? AND
                              Classes::Class_Theme_Id IN (\"10\",\"11\") AND
                              StudentClass::Class_Role = \"2\"
                 GROUP BY StudentClass::Student_Id";
                 "";
                 "";
                 Student_Id); $result

                 )
                 )

                 I have tried a lot of combinations but I get the ? everytime.   Is there something that you see that is making this an invalid statement??

                 Thanks!

                 GregDC


                  

            • 3. Re: Calculated field using ExecuteSQL -  can't get it to work
              philmodjunk

                   You are still using filemaker table::field references instead of SQL references that should look like this: Table.Field.

              • 4. Re: Calculated field using ExecuteSQL -  can't get it to work
                gregdc

                     Phil,

                       OK that was a good catch.  However, after changing the :: to .  I still have ? in the field.   It now looks like:

                     GetAsNumber (

                     Let (

                     $result = ExecuteSQL (
                     "SELECT Count(*)
                     FROM StudentClass JOIN Classes ON StudentClass.Class_Id = Classes.Class_Id
                     WHERE StudentClass.Student_Id = ? AND
                                  Classes.Class_Theme_Id IN (\"10\",\"11\") AND
                                  StudentClass.Class_Role = \"2\"
                     GROUP BY StudentClass.Student_Id";
                     "";
                     "";
                     Student_Id); $result

                     )
                     )

                • 5. Re: Calculated field using ExecuteSQL -  can't get it to work
                  philmodjunk

                       The ? syntax error indicator is the most maddening thing about using ExecuteSQL as it gives you no clue as to what part of the SQL query contains the error.

                       While I see no reason for the Let function in the current calculation and don't think that you need quotes around your number contants, I don't see where either would result in a syntax error. So I don't see why you are still getting that result.

                       Just to check something very obvious, you do still get a ? even if you click into the field?