Calculated field not calculating
I have two tables Events and Participants. Since this represents a many to many relationship, I have a "Key table" between them. EventParticipant contains the primary Key of the event and the primary key of the participant. This works well for the portals on both the participant and event layouts.
However, in the Event table record there is a field ParticipantCount that is a calculated field defined as
[$query = "select count(DISTINCT Person_Id) from TrainingParticipant where Training_Id=?" ;
$result = ExecuteSQL ($query;"";""; Event::Training_Event_Id)
This field is displayed on the Event layout along with the Participant portal. The problem is that the count shows zero (0). The check box "do not evaluate if all referenced fields are empty" is unchecked. Calculation result is set as Number, and the number of repetitions is 1.
Any ideas I could try to get this to calculate on the Events Layout as I am entering participants?
Thanks for your help!!