9 Replies Latest reply on May 24, 2014 6:12 PM by dchabot

    Is there a better way to Count field responses?

    mikeo'neil

      FMA 13

       

      Database is a multiple table solution that tracks performance statistics of Fire/EMS agencies.

       

      Several fields in my Cardiac Arrest table tracks what hospital patient was transported to (possible 15 choices), another field tracks Advanced Airway method that was used (5 choices), another tracks where the patient was discharged to (7 possible choices). All responses are from a value list and are text.

       

      I know I can define a calculation to Count responses in individual fields and then Summarize for counts, but is there a better way?.

       

       

      IE:

      Count Home Responses

      Case(Discharge = Home,1,0)

       

      Count SNF Responses

      Case(Discharge = SNF,1,0)

       

       

      Count Expired Responses

       

      Case(Discharge = EXP,1,0)

       

       

      Count Hospice Responses

       

      Case(Discharge = HOSPICE,1,0)

       

      Then Sum each field to get a count.

       

      Looking for a method that is more efficient?

       

       

       

      Thanks,

       

      Mike

        • 1. Re: Is there a better way to Count field responses?
          Mike_Mitchell

          Mike -

           

          A more normalized approach to this situation would be to define a set of tables that contains the possible choices for each of the variables (hospital, airway method, etc.). Then, define relationships between those tables and the Cardiac Arrest table fields where the values are kept (from the value in the "domain" table to the appropriate field in the Cardiac Arrest table). Define a calculation field in the domain table equal to Count ( Cardiac Arrest::{field} ) where {field} is a field that's always populated (like the primary key).

           

          This will let the domain table tell you immediately how many records in the Cardiac Arrest table match each value in the domain table. You'll be able to display them in List view, or on a report, and it'll look something like this (just an example):

           

          Hospital                                                                 # of Arrests

           

          St. Mark                                                                      5

          VA                                                                                3

          Medical College                                                       2

           

          and so forth.

           

          The other big advantage to this approach is that, if you need a new choice for any of the variables, all you have to do is add a new record to the appropriate domain table. All the calculations will automatically update (along with the values in any appropriate value lists).

           

          Hope that makes sense.

           

          Mike

          • 2. Re: Is there a better way to Count field responses?
            mikeo'neil

            Mike,

             

            Once again, thanks for your suggestion.

             

            I was able to link to a "Hosp" table but still need to display the results on a summary report in the Cardiac Arrest file. I need to spend some time looking at things but need to run out and will hit it in the AM. Just wanted to update you.

             

            Mike

            • 3. Re: Is there a better way to Count field responses?
              coherentkris

              it possible to use a list layout based on the with sub summary part based on the discharge field. This will have the effect of grouping the different values in DISCHARGE by the sort order. In the sub summary you can place record count so that each group will have the count of members of that group... probably repeated alot of what mike said.

              • 4. Re: Is there a better way to Count field responses?
                Mike_Mitchell

                Mike -

                 

                You can still do that. Since the field in the Hospital table is a calculation, it can be referenced from Cardiac Arrest without issue. Just place it in the subsummary part when you sort on Hospital.

                 

                Mike

                • 5. Re: Is there a better way to Count field responses?
                  mikeo'neil

                  I did what was suggested but is not what I am trying to do. I attached a draft pdf report to this response.

                   

                  I guess the best way to attempt explaining is a pdf sample of the draft report with test data (attached). The report lists all responses displaying various fields that are tracked. The report is Sorted on Provider ID & Date of Incident. I desire to have responses displayed/sorted by Agency which the sort on Provider does. I tried to also sort on Hospital but lose the desired view by Agency/Provider ID. I will then have the same issue with the Discharge and Airway data display, and how to do this.

                   

                  I typed in RED how I would like to do display the data for the Hospitals transported to, the Advanced Airway used, and the Discharge data by totals by Agency/Provider ID, below the data in the sum-summary part, and also in the Grand Summary part...

                   

                  I am not sure if this is possible to do on one report.

                   

                  18 possible choices for Hospital

                  6 possible choices for Advanced Airway

                  7 possible choices for Discharge

                   

                  I could do this by creating in the same table as the Cardiac Arrest stats (38 fields) a count and summary field for each possible response but that would be about 31 additional calculation & 31 additional summary fields. I know this is not a best practice but in my favor is the amount of records would be under 4,000 per year and a stand alone solution at this time.

                   

                  Any Thoughts,

                   

                  Mike

                  • 6. Re: Is there a better way to Count field responses?
                    jormond

                    See if you have access to this video.  It is a presentation by Bob Bowers at the 2012 DevCon. https://fmdev.filemaker.com/docs/DOC-2954

                     

                    Based on this video, and some of the transactional techniques that Todd Geist (and others) talks frequently about, I have changed, oh so many, things with how I do reporting. While not always possible, or worth the effort, I have moved many of my reports to scripts...or at least the calculation of the data. That has allowed me to reduce the number of unstored calcs and summary fields that I use. And in many cases ( not all ), has increase performance across the solution. Not to mention, there is the potential for using the same script for multiple reports by just feeding it the parameters and fields I want returned.

                     

                    The basic idea of Bob's video is running the records and stuffing the data, or calculated results, into a variable.  Which can then be easily accessed via a Virtual List, or any way you want to access the array. As you get used to using the techniques and understand how they work, your options with customizing the look and function of the report become endless.

                    1 of 1 people found this helpful
                    • 7. Re: Is there a better way to Count field responses?
                      Mike_Mitchell

                      Mike -

                       

                      Okay, you're not dealing with a simple summary. You have summary not only by parent table (Advanced Airway, Hospital, etc.) but also by Provider (in this case, Fire Department). Joshua's suggestion of using scripting to assemble the report is probably your best bet. I haven't watched Bob's video, but the method I normally use for constructing something of this sort is either (a) a temporary join using global fields (where you create a relationship between one or more global fields and the targetted data, then use a Count calculation), or (b) ExecuteSQL to assemble the data into one or more variables. You can then present the data either in a set of global fields, or in an HTML document and a web viewer.

                       

                      So, for example, you might set up two global fields, gProviderID and gDomainID. Create a relationship from gProviderID to the ProviderID and gDomainID to each of Hospital, Advanced Airway, etc., in the Cardiac Arrest table. (You'll need multiple table occurrences to make this work properly.) Then you set up a nested loop, setting gDomainID to each variable you want (each Hospital, Advanced Airway) in turn while you have gProvider ID set to the current choice for Provider. Use Count to calculate the proper value, then store it in a variable or global field.

                       

                      Hope that makes sense.

                      1 of 1 people found this helpful
                      • 8. Re: Is there a better way to Count field responses?
                        mikeo'neil

                        Mike & Joshua,

                         

                        Thanks for the link. I will dig into this over the next couple of days and see what I can come up with. I had been thinking about some way to script this and the methods suggested with be a major help to me. I am doing this free for the EMS Medical Directors in my area so I have some time to work through this. Will let you know the outcome when I test these methods out.

                         

                        Thanks,

                         

                        MIke

                        • 9. Re: Is there a better way to Count field responses?
                          dchabot

                          Thanks for the this link it's one of the best videos I've seen in a while.

                          I believe the Virtual List technique Bob mentions in the video is by Bruce Robertson.

                          Thanks Bruce, and Bob and Joshua for posting this link.

                           

                          DChabot