4 Replies Latest reply on Mar 8, 2016 5:20 AM by tkemmere

    Charting: Split per record from related table

    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.

        • 1. Re: Charting: Split per record from related table
          tkemmere

          Dear all,

          I'm still wondering about this.

          (It is probably not a very appreciated practice to 'bump' a post upwards the way I'm doing right now, so I promise only to do it once).

          Thomas.

          • 2. Re: Charting: Split per record from related table
            beverly

            bump is appropriate!

            Where are you placing the values of the result? If you have it as a field and include the select  based on the account, then you will have only those values.

             

            beverly

            • 3. Re: Charting: Split per record from related table
              user19752

              There is no field names for coding, so it is hard to explain but assuming

              you need one more criterion

               

              ExecuteSQL ( "select COUNT (*) FROM Contactmoments WHERE FollowupType = 'Call' AND AccountManager=? " ; "" ; "" ; someTable::AccountManager)

              • 4. Re: Charting: Split per record from related table
                tkemmere

                Ok, I managed to get back to this. My apologies for first bumping this up, and then getting busy with other work.

                 

                Dear beverly Thanks.

                Where am I placing the the values of the result, you ask. I'm not sure I understand.

                I place...

                ExecuteSQL ( "select COUNT (*) FROM Contactmoments WHERE FollowupType = 'Call' " ; "" ; "")

                ...here:

                cause I would like its values to go straight into the graph, split per accountmgr, just as it did for the Count(Objects::ID).


                I think your're heading the direction of: Creating a new calc-field in the contactmoments-table, with the calc being ... err...

                Or wait, maybe I need to create 5 global fields, counting respectively 'Call', 'Follow up' etc. ...No that won't work cause then they won't be split per accmgr.


                I'm stuck. In the end my question is: What could I type under "Data" to make FM count all contactmoments with the followup type 'Call'? FM has shown in the two succesful tables that it can/will then split the data per account manager.


                Dear user19752 Thanks.

                I included your suggestion, (even though you can see above that my line of thought is that FM seems to be able to make the distinction between the accountmgrs based on the x-axis-definition), so I made it:

                ExecuteSQL ( "select COUNT (*) FROM Contactmoments WHERE FollowupType = 'Call' AND Firstname=? " ; "" ; "" ; AccountManagers::Firstname)

                But all goes to zero then. I did it for "Call" and for "Follow up":


                I'm not there yet...


                Regards, Thomas.