5 Replies Latest reply on Aug 15, 2014 11:52 AM by jmvatc

    counting unique values in a subset of a subsummary without adding a subsummary part

    jmvatc

      Title

      counting unique values in a subset of a subsummary without adding a subsummary part

      Post

           I'm trying to count the number of unique subsets I have within a subsummary.  This thread was very close to what I want, but the calculation yields a count of all the unique records within the subsummary.

           I want a count of the number of subsets within those records.

           I've created a report for my clients that summarizes our activity for them.  This is already summarized by their organization, and then again by a class of the record.

           Since each record is created by an individual employees, I'm trying to count how many employees are involved within each subsummary.

           For instance, when all records are shown, I can count that five employees are involved in one organization, but only three in another.  Further down, I can tell that of the five employees, two are associated with one class of record, etc...

           How would filemaker calculate that?

           I have

           clients::client id -< tos::client id

           employees::employee id -< tos::employee id

           tos::s_employee id = count of ( employee id )

           tos::c_eeid_organization = ( 1 / GetSummary ( s_employee id ; organization ) ) {provides inverse of count of records within organization subsummary}

           tos::c_eeid_class = ( 1 / GetSummary ( s_employee id ; class ) ) {provides inverse of count of records within each class subsummary}

           NB: as suggested in the linked post, summary field s_eeid_organization, total of c_eeid_organization yields 1.

           TIA,

           Jeremy

           FMPRO-ADV 13.0v3, mac os x.9.4

        • 1. Re: counting unique values in a subset of a subsummary without adding a subsummary part
          philmodjunk

               GetSummary does not require a subsummary layout part in order to evaluate.

               tos is apparently a join table between employees and clients.

               Am I correct that you want the number of employees linked, via join table to a given client?

               If so, then, in your clients table, a calculation field: Count ( Employees::employee id )

               But it is possible that you may need to filter the number of tos records counted--such as only those within a certain range of dates, in which case you'd need to set up a filtering relationship between clients and an occurrence of tos that matches only to tos records that meet this additional criteria before the count function will correctly return the count that you want.

          • 2. Re: counting unique values in a subset of a subsummary without adding a subsummary part
            jmvatc

                 In effect, yes, tos is a join table between employees and clients.  The relationship employees -< tos >- clients does exist.

                 This report is on the client's tos records as created by an employee, but I'm also trying to sneak in  how many employees created tos records within a particular client's organization.  I keep landing on a count of the records, but not count of employees.

                 Is this still the route to take?

                 Or should I change the context of the calculations mentioned in the previous thread?

            • 3. Re: counting unique values in a subset of a subsummary without adding a subsummary part
              jmvatc

                   Here's another take on what I'm after...

                   On a report showing TOS records, sorted by client, by organization, by employee, by tos class with sub summary parts: client, organization, employee, tos class.

                   I get all the information I want except I want a count of the subset "employees" under organization.  On my layout, I can visibly count that 3 organizations have tos records. 2 employees created tos records for the first organization; 2 employees created tos records for the second organization; 1 employee created tos records for the third organization.

                   I want to display the count of the subset of employees in each organization without displaying the subsummary part "employees".  I can get a count of tos records under each subsummary, but not a count of the subsets.

                   I'm lead to believe there has to be a calculation for this.

                    

              • 4. Re: counting unique values in a subset of a subsummary without adding a subsummary part
                philmodjunk

                     What I describe should work. And this calculation field defined in clients can be referenced on a layout based on tos so that you get the number of employees that created at least one tos record linked to that client.

                     The trick is to count records in Employees, not tos.

                     (This is not so say that sum the reciprocal couldn't be adjusted to work, but what I am suggesting seems much simpler to implement.)

                • 5. Re: counting unique values in a subset of a subsummary without adding a subsummary part
                  jmvatc

                       Ok, I've tried several pathways, but can't seem to get past counting each individual TOS record.

                       Am I on the right path if I create clients::c_getsumm_tos_employees (employee id), and organizations::c_getsumm_tos_employees (employee id) then call those two fields on the layout of TOS?