Count number of patients 18 and under
Hello, I am working on a database to track information about a clinic I work at in Haiti. I am trying to breakdown information into useful data for our funding organizations. For example, I am currently trying to determine how many people we treated that were 18 years old or under. I am learning as I go, but I can see that I will need to be able to generate a count that is dependent on the values of multiple fields. I have a parent table called "Master File" and child table called "Appointments". They are related by a unique value field called "Patient ID". The fields I am currently working on are as follows, and they are displayed in a "Reports" layout under the parent table (I want to stay away from the summary and sub-summary partitions. I need to be able to create a report using the various fields below and create a custom layout to display them):
AGE: which is a calculation field that yeilds a numeric value (ie. 1-99)
Cause: which is a text field. The data is entered via a Value list, one of which is the value "EQ" for earthquake victims.
First, I want to return a value for how many patients we have seen that are 18 years old or younger. To do this I created two additional fields. The first is called "Total Patients Under Eighteen", it is a calculation field with the following equation:
=PatternCount (Age ; "18") + PatternCount (Age ; "17") ... +PatternCount(Age ; "2") + PatternCount(Age ; "1")
The probem here is that a value of 3 is returned for records that are equal to "17". 1 for the "1", 1 for the "7", and 1 for the "17", for a total of 3. How can I alter this equal to return a value of 1 for each record containing a value of 18 or less and 0 for any value 19 and greater?
The second field I created is called "Total Patients Under Eighteen Summary". It is a summary field and is set to return the "Total of" "Total Patients Under Eighteen". This part works fine but is returning faulty information due to the inaccuracies of the first field.
Next, I want to be able to sort the records by filtering the information to display several criteria. I found another post that showed me how to create a script that will find records within a particular date range. I used that concept to create a script that will find the records that will show info for the patients 18 and under. Now, I want to create a script that will further breakdown that set of found records. I want the script to show just the records of the patients that are 18 and under, and EQ victims, within a particular date range. When I find the records for patients 18 and under, then hit the button that finds records for EQ victims, the first find is lost and the records show ALL eq victims under and over 18. Each new script overrides the previos script and finds records from all records and not just the current foundset.
I hope I have given you enough information without getting too confusing. I have been working on this for several days and would greatly appreciate some guidance. I am relatively new to Filemaker, so a beginners explanation would be appreciated too.