AnsweredAssumed Answered

Charting: Split per record from related table

Question asked by tkemmere on Feb 9, 2016
Latest reply on Mar 8, 2016 by tkemmere

Hi,

 

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.

Outcomes