2 Replies Latest reply on May 8, 2012 12:10 PM by uaykylie

    Get totals from related field and assign level of participation

    uaykylie

      Title

      Get totals from related field and assign level of participation

      Post

      I am currently working on a db for clients.  I have an Intake table that relates to a Contact table.  What I have been banging my head into a wall over is how to create a count of contacts for each client and then assign them a level of service based upon participation in the last three months.  I know I must be missing some very key concept but I'm not sure what. Any advice on how to start this?

        • 1. Re: Get totals from related field and assign level of participation
          philmodjunk

          What is the relationship between clients and contacts? Is it this?

          Clients::ClientID = Contacts::ClientID

          If so,

          Count ( Contacts::ClientID ) will count all related contacts records for a given client. A "count of" summary field defined in Contacts will produce the same count.

          To limit the count to a specific date range can be done by including dates in the relationship or by using a filtered portal (If you have FileMaker 11 or 12.)

          With a filtered portal, you can make it a one row portal and put the previously descrbed summary field in the portal row and it will corretly count the filtered, related records.

          GlobalsTable::gDate1 < Contacts::Date AND GlobalsTable::gDate2 > Contacts::Date

          is one possible portal filter.

          Clients::ClientID = Contacts::ClientID AND
          Clients::gDate1 < Contacts::Date AND
          Clients::gDate2 > Contacts::Date

          Would work as a relationship. (gDate1 and gDate2 should most likely be defined as global fields.)

          • 2. Re: Get totals from related field and assign level of participation
            uaykylie

            Thank you so much!  That cleared up my problem entirely!!!!