I'm building this database for a company with Accountmanagers, that each manage Companies, with Contact persons and Contact moments. The Companies (in turn) have Contracts to which Objects belong.
Now I'm building a dashboard for the Account managers. So it is in a layout based of the table Account managers.
Making a chart that shows the amount of Objects related to each Account manager is no problem. FileMaker splits the amout per account manager. For the Y-axis I use the calculation "Count ( Objects::ID )". Some Account managers have many, others few.
Making a chart that shows the amount of Contact moments related to each Account manager is also no problem. FileMaker splits the amout per account manager. For the Y-axis I use the calculation "Count ( Contactmoments::ID )". Same thing:
But what I would like to see it the amount of OPEN Contact moments. I.e. those with Follow up type "Call". So I thought I use the calculation:
ExecuteSQL ( "select COUNT (*) FROM Contactmoments WHERE FollowupType = 'Call' " ; "" ; "")
But now, FileMaker assings each Account manager with all (Follow-up) Contact moments. ...All managers all moments. It's become a useless horizontal chart.
I wonder why does it do that, and how do I solve it?
Thanks for your hints, Thomas.