summary page problems
We use filemaker pro for our client database to record information pertaining to demographics, services provided and outcomes of assessments.
We utilise a find page and summary page to provide snapshots of our service, such as annual reports and monthly reports. So far this has been working well, except for one field that has the option to choose up to 6 services from a value list. We have the field 'detailed service' with a value list that has numerous options including 'forensic assessment', 'therapeutic intervention' etc with room to place up to 6 'detailed services'. For example, we need to be able to know how many forensic assessments we conduct in a time period. If conducting a find using "control F", selecting the time period 2012 and then "forensic assessment" for the service provided we get the number 25. This search searches all repetitions, so that if forensic assessment is the 2nd to 6th service listed it is counted. However, when using the summary page we only get the number 20 as this method does not count the 5 "forensic assessments" in the 2nd - 6th positions. The summary page currently uses the calculation (If ( Detailed Services = "forensic assessment";1;0 ) and then a total of this.
Is there a calculation that would work for the summary page to ensure that all positions in the repetition were searched during a find. I dont have a sophisticated knowledge of the database and was not involved in the set up of it so please let me know if I need to provide more information about this problem. Im hoping that this question makes sense and there is a simple solution out there.
Best solution would be to get rid of the repeating fields. Replace them with a related record where each repetition is a different related record. The results become much easier to use for finding, grouping and summarizing your data.