AnsweredAssumed Answered

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

Question asked by gregdc on Oct 1, 2013
Latest reply on Oct 2, 2013 by philmodjunk


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


     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.