2 Replies Latest reply on Aug 2, 2011 12:59 PM by philmodjunk

    Count of unique values does not populate consistently



      Count of unique values does not populate consistently


      Hello helpful FM people!

      I am creating a rather complex report, but  I hope it distills to a simple question and answer.

      SETTING. The report is for a clinical office where, out of a population of 246 diabetic clients, 39 are enrolled in a special treatment program. The report compares

      Column 1 - the average health conditions for a found set of treated clients (such as "all female")
      Column 2 - the average health conditions for the 39 treated clients
      Column 3 - the average health conditions for the 207 untreated clients

      REPORT STRUCTURE. A copy of the report is attached. It uses the following table occurrences which correspond to the above columns.

      Health Measures >
           Health Measures (Self) Treated (where a "treated" field in both table occurrences are equal)
           Health Measures (Self) Untreated (where the same "treated" field in both table occurrences are not equal).

      RUNNING THE REPORT. To run the report, the user generally starts on the client record, finds a subset of treated clients (such as "all female), then clicks on a "Go to Related Records" button through this table occurrence:

      Client (Found Set) > Client::HealthMeasures

      The results are displayed in an unrelated layout, the Health Measures layout (not Client::HealthMeasures). From this Health Measures layout the user clicks on a button for the Report, which navigates to the report page (attached).

      Up to this point, no problem. The found set of clients' health records are averaged in Column 1, the entire treated set of clients are averaged in Column 2, and the untreated group records are averaged in Column 3.

      PROBLEM. My customer asked me to put the number of clients (N) whose health records are represented in this report at the top of each column. I tried a lot of things that didn't work, and finally used the following method found on the FileMaker forum website. Instead of using a count of kp_Client ID from the Client record, this method uses the kf_Client ID on the Health Measures records, and the GetSummary calculation. I created three fields:

      (Name) "CountClientID" - (Type) Summary - (Action) Count of kf_Client ID
      (Name) "FractionOfCount" - (Type) Calculation - (Action) 1/GetSummary(CountClientID;ClientID)
      (Name) "TotalOfFraction" - (Type) Summary - (Action) Total of FractionOfCountClientID

      The yellow fields on the attached report are the above fields. To make this work, the found records are sorted by Client ID.

      The first two fields populate quickly. The Count of Client ID populates as soon as I have a found set, and after the sort each record has a fractional value. For example if there are four health records with the same client ID, they each have a fractional value of 1/4 after the sort If there are only two health records with the same client ID, they have a fractional value of 1/2.

      What isn't working is the final "Total of Fraction" summary field, which is supposed to add up the fractions and deliver the (N) number of clients I am looking for. Sometimes if I scroll among records the value will suddenly populate, and sometimes if I navigate around the database and come back it will be populated. But most of the time I can't trigger it to populate.

      (By the way, to populate the "Number of Clients" for the Treated and Untreated Columns, I created a script that navigated to each table occurrence, performed the find, sorted the records by client id, collected the "Total of Fractional Count of Client ID" value as a $variable, and then set the $variable into global fields on the report. This is working fine.)

      I have tried sorting the Health Measures records by each of the possible table occurrences without success. The problem may arise because I am working with a number of table occurrences, starting with Client::HealthMeasures and ending with an unrelated table called HealthMeasures, and along the way visiting HealthMeasures(treated) and HealthMeasures(untreated) to obtain values for the global fields.

      If this is a headache question related to messed up table occurrences, just say so, as I am spending most of my time trying to figure out whether the fields and sorts are working properly. If there is a simple answer to make that summary field add up the fractions, I welcome it.


        • 1. Re: Count of unique values does not populate consistently


          The solution seems to be that I must find the child records in the same table occurrence where the report is run. Switching from one TO to another did not affect the summary results (averages health conditions, for example) but did affect the total of the GetSummary calculation.

          This did not work:

          Find client attriubes on Client layout
          Go to Related Health Records (Client::HealthMeasures) using an unrelated layout (HealthMeasures)
          Go to Report (using same Table Occurrence as Health Measures)
          Does not produce a count of unique kf_Client ID numbers but does produce other summary results

          This does work:

          Go to HealthMeasures Layout
          Find client attributes on HealthMeasures layout
          Go to Report (using same Table Occurrence as Health Measures)
          DOES produce a count of unique kf_Client ID numbers as well as other summary results

          • 2. Re: Count of unique values does not populate consistently

            Summary fields and calculations such as getsummary depend on the current found set and sort order (in the case of sub totals like you need here) to determine what value to display. Each TO has it's own current record, found set, and sort order, so changing to a layout basd on a different TO of the same table can produce a set of records that don't have the same found set and/or sort order and thus these fields return a different value.