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:
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)
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.