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.