2 Replies Latest reply on Jul 14, 2010 9:05 AM by bcooney

    display the total number of fields



      display the total number of fields



      I have 2 tables in 1 FM file. 

      Client and users, there is a 1 to N relationship as there are many users for 1 client. In one of my layouts I would like to display the total number of related users with a status of Active. 


        • 1. Re: display the total number of fields

          Presumably, you have this relationship:

          Clients::ClientID = Users::ClientID

          You can create a second table occurrence of Users (click the box in the relationship graph and then click the button with two plus signs.)

          Create a calculation field, cActive, in Clients that always returns a status of "Active"

          Now make a second relationship:

          Clients::ClientID = ActiveUsers::ClientID AND
          Clients::cActive = ActiveUsers::Status
          (ActiveUsers is the second table occurrence of Users)

          A calculation field in Clients defined as Count ( ActiveUsers::ClientID ) will now return the number of Active users for the current client record.

          • 2. Re: display the total number of fields

            Your title says, "Total Number of Fields." What you are really asking, though, is a sum of related records. Phil has outlined how to achieve this with a relationship. However, I would start thinking about using flag fields. For a user that is "Active", create a field that is a number, "flag_Active" and format this field to be a checkbox with a value list of "1". Resize this field on the layout to just show the checkbox.

            In Clients, create a calc field, result number, zz_Constant1 =1. To get a count of how many active users a client has related to them:

            Clients::ClientID = Users::ClientID AND
            Clients::zz_Constant1 = Users::flag_Active

            Using flag fields scales well, because you can use the same zz_Constant to filter many relationships from Client.