8 Replies Latest reply on May 20, 2011 11:48 AM by aammondd

    Count or summary of Drop Down Fields

    tso

      Title

      Count or summary of Drop Down Fields

      Post

      Need advice for the best/easiest way to count or summarize drop down fields in a table ( I've got a book on FMP but I'm still a beginner. Don't see exactly what i need there...)

      We have a database filled with patients and donors but we only need to tally demographic info on the patients for every month, quarter, year.

      We have a table called PATIENT DEMOGRAPHICS with 10 drop downs - like ethnicity (choose 1 of 9), income (choose low, med, high)...etc.

      So we need to generate  reports during specific time frames of patients served, thus counting up all the data in the drop downs.

      April 2011:

      32 women

      18 men

      5 from San Francisco county

      18 african american

      .......

      tips?

      thanks.

        • 1. Re: Count or summary of Drop Down Fields
          philmodjunk

          I'm not sure that I'm interpreting your report example correctly.

          When you indicate that you have

          5 from SanFrancisco county

          Is that 5 men from San Francisco county or 5 of the 40 total men and women who donated in April 2011?

          • 2. Re: Count or summary of Drop Down Fields
            tso

            Well I just made that stuff up but its more complex than that. I'm trying to find the easiest way.

            We want to know how many patients we saw during time frame...that should be the total number of patient records in the group.

            Then of that group we only need to know how many women under 55, women over 55, men under 55,men over 55.

            And then of that same group of patients count of (non-gender specifically) income, insurance.

            It would pull the data from whatever was entered in the drop downs for each patient record from the patient demographic table.

            If possible. Maybe i set it up wrong?

            Make any sense?

            • 3. Re: Count or summary of Drop Down Fields
              philmodjunk

              The key point is in how you want to count the records. The following report format is easy to set up in FileMaker and may do what you want:

              April 2011
                Female Patients:  20
                     Under 55:  5
                     Over 55:   15

                Male Patients: 6
                     Under 55: 2
                     Over 55:   4

              Total for April 2011: 26

              Note that any one patient was counted only one time at the lowest level (age category).

              Here's how to set this up from a table of Patients with a found set of all patients who were treated in one month:

              Base your report on your patients table. Define a summary field, sPatientCount in this table that computes the "count of" some non-empty field such as PatientID.

              Put the treatment date field in the layout header and format it to only display the Month and Year. (This is likely a field from a related table of office visits and you'll need to sort this relationship by date in descending order so that it refers to the most recent office visit for that patient.)

              Click on the part label for the Body and press delete to remove it.

              Use Part setup.. to add a sub summary part, "when sorted by" your gender field.
              Add sPatientCount to this part to compute the total men and total women
              Add a sub summary part below it "when sorted by" your age category field.
              Put the same sPatientCount field to it to compute the break down by age category
              Add a trailing grand summary part to your layout and put sPatientCount in it to compute the grand total count at the end of your report.

              To view a monthly report, go to this layout, enter find mode and specify a date such as 4/2011 (US date settings only) or 4/1/2011...4/30/2011 to find all patients with an office visit in April 2011. Then sort your records sorting first by Gender, then by age category to group your records correctly and so that the sub summary parts will appear.

              This find and sort process can be scripted to make it faster and easier.

              Note: if You try this with a version of FileMaker older than FileMaker 10, you must preview or print the report before you can see the sub summary parts.

              • 4. Re: Count or summary of Drop Down Fields
                LaRetta_1

                Hi Lori,

                "We have a table called PATIENT DEMOGRAPHICS with 10 drop downs"

                10 drop-down fields in each record?

                Keep in mind that, since each value list appears in the same record, you cannot generate a report which includes the same record in two different value lists.  For example:  If Mary Johnson was in value list for under 55 and also Female, you cannot get her to appear in both sub summaries since the actual record exists only once.

                So if you want a result of:

                April 2011:
                32 women
                18 men
                5 from San Francisco county
                18 african american

                ... then you will need a different approach than sub-summary report.  It can be done but I just want to be sure of the format in which you want the results so let's walk through it:

                You perform a find for April, 2011.  Script then goes to the first value list (maybe Gender) and tallies and generates a line into a variable (holding queue) as:

                April 2011:
                32 women
                18 men

                ... then it goes to the next value list (maybe County) and generates:

                11 Humbolt
                8 Lassen
                3 Mendocino
                6 San Francisco
                5 San Mateo
                10 Santa Cruz
                7 Shasta

                After going through all the value lists, the report for April 2011 will look like:

                April 2011 50 people polled:
                32 Gender: women
                18 Gender: men
                11 County: Humbolt
                8 County: Lassen
                3 County: Mendocino
                6 County: San Francisco
                5 County: San Mateo
                10 County: Santa Cruz
                7 County: Shasta
                19 Income: High
                22 Income: Medium
                9 Income: Low

                ... etc.  In this instance, the report result needs to be specific since it all needs to be scripted.

                I should add one more thing here:  Ideally, the demographics would be a table with the following fields:

                ClientID
                Category (income, ethnicity, gender whatever)
                Value: high, Mendocino, Men etc

                If it were structured as RECORDS instead of fields within records with multiple values within fields then generating your summaries, counts, groupings, reports and demographics would be very simple piece of cake.

                So now the ball is in your court ... 1) can you change the structure and 2) if not, what specifically do the results need to look like? 

                ADDED AFTER SUBMISSION:  I should also add that much will depend upon how this data will be grouped comparing these periods to other periods to look for trends.  What are the plans for this data beyond this simple report concept?

                • 5. Re: Count or summary of Drop Down Fields
                  aammondd

                  You dont neccissarily need to change the underlying structure since it will most likely involve scripting anyway you could build a single report output table that looks much like the demographics table LaRetta suggested. You can also add fields to indicate the reporting period etc and produce your report layout off the Report output table.

                  A report output table may be preferable to Variables because you can manipulate it a bit better and go back to it  without having to run the script again. With added key fields like a report run id date and period covered. You could build history to allow you to measure trends. In a sense you are doing some data mining of your raw data to produce some meta-data (data about data) Storing it in tables makes retrieving it faster as your database grows and changes.

                  I would also consider storing your dropdown values in a reference table that with the fields tablename fieldname and value which will assist in script processing of the data some. It will also allow you to count 0 values rather easily too.

                   

                  • 6. Re: Count or summary of Drop Down Fields
                    LaRetta_1

                    You have made some good points, aammondd. 

                    Since this is static data which will not change, we can manipulate it further instead of using a virtual table (no real concern of multi-user interference and static will be quicker).  But the transformation, we both agree, will need to take place.  The current structure will not produce the reports required.  Thanks for offering your input!

                    • 7. Re: Count or summary of Drop Down Fields
                      tso

                      Thanks for your comments.

                      So maybe some restructuring is needed to generate this kind of demographic report.

                      LaRetta was right in that there are 10 drop downs for each record...and the kind of report would look like what LaRetta did.

                      The same person/record would be appearing more than once in the varied drop down lists values if the report worked right.

                      Betty Rubble: female, asian, over 55, humboldt county, ovarian cancer..etc. There are 10 different drop down and each patient has one answer for each.

                      @aammondd- "I would also consider storing your dropdown values in a reference table  that with the fields tablename fieldname and value which will assist in  script processing of the data some. It will also allow you to count 0  values rather easily too." I'm not sure what you mean (again I'm a super beginner) but I think I get the report output table idea.

                      I'm going to try some stuff out and see what i can do.

                      Any ideas more than welcome!

                      Thank you!!!!

                      • 8. Re: Count or summary of Drop Down Fields
                        aammondd

                        What I mean is create a table with the following fields :

                        Tablename

                        Fieldname  

                        DDValue

                        Basically you are going to create a list of all your dropdown values and which table and field name they are associated with.

                        This will allow scripting to loop through this table to processs the information in each of the fields rather than having  to build a bunch of case/if statements etc. It will also allow you to find those values that are not present should you wish to.