9 Replies Latest reply on Jun 13, 2016 2:21 PM by danjamins

    Counting Main Records From Related Table

    danjamins

      Hi!

       

      I have a little problem I can't seem to figure out, maybe it's the lack of sleep.

       

      I have a table "Clients" and a related table "Appointments" that are related by the fields Clients::__pk_ClientID and Appointments::_fk_ClientID

       

      Using this guide:

      How to show the number of related records | FileMaker 

       

      I am able to count how many appointments a client has.

       

      What about the reverse? I want to pull a report from the Appointments table, to get the total # of appointments by appointment date range, and then tell me out of the total number of appointments, how many unique clients there are.

       

      I've tried creating a field in appointments for Count ( Clients::__pk_ClientID ) but it doesn't seem to work, it just gives me "1" no matter what.

       

      Is there a way to find this out without using ExecuteSQL?

       

      Thank you,

      Dan.