4 Replies Latest reply on Aug 20, 2014 5:03 AM by beverly

    Summing Field Contents

    Bobster

      Hi,

       

      I’m just getting back into FileMaker programming with FMA 13 and I’m a bit rusty.

       

      I have the responses to several questions that were asked in a survey.

       

      For simplicity, let’s say the answers to each question were “Yes”, “No” and “Maybe”.

       

      Is there a way to Sum the number of each possible answer to each question across the entire database?

       

      i.e. The answers to Question 1 were: “Yes” = 25, “No” = 50, “Maybe” = 12

       

      I’m racking my brain, but the answer isn’t jumping out at me.

       

      Hope you can help!

       

       

      Bob Stuart

      Lord Of The Files

      Think Data Pty Ltd

      Noosa

      Queensland

      Australia.

       

      +61 7 5442 5624

      +61 413 350 993

        • 1. Re: Summing Field Contents
          davehob

          Bob - if the possible answers are consistent and relatively few in number, I'd probably have a Boolean calc field for each possible answer, e.g. answerIsYes, answerIsNo, answerIsMaybe - this would make totalling the responses across a found set very straightforward.  It rather depends on how the answers are held though.

           

          Dave.

          • 2. Re: Summing Field Contents
            Bobster

            Thanks, Dave. I think that might be the way to go. I'll create a summary field for each of the three answers and use the results to create a chart.

             

            Good one!

             

            Bob.

            • 3. Re: Summing Field Contents
              jbrown

              Good morning.

              If the answer information is not updated every minute of the day, I'd lean to making a summary table of the information.

              I'm a big fan of summarizing data and allowing the summary to be static.

              I'd set up a new table with the fields: Question #, Question, AnswerYesCount, AnswerNoCount, AnswerMaybeCount.

               

              Then my script would go to this table, create a new record, put a question in those fields, then count all the Yes and put that sum in the Yes field, then count all the Nos for that question and put that in the No field. Do the same with the Maybes. I'd loop through all the questions that way.

               

              ExecuteSQL is a great way to get the data you need for the script and record creation.

               

              This procedure allows all data to be static. Graphs and other reports can access these numbers in this table rather than an unstored calc or a summary field. If the database is hosted and you're accessing it via WAN, reports and graphs may render faster because there's no calculations that have to be done.

               

              I'd also plan to re run the script every time new information is put into the questions table.This time, Id update the script to first look for the question that I'm updating. If a question is already there, simply recount the Yes, No, Maybe answers. If the question is not already in the summary table, create a new record, stuff the question in, and then count.

              • 4. Re: Summing Field Contents
                beverly

                +1

                If you need the values, execute SQL would be an excellent way to go rather than creating summary or other calculation fields.

                 

                -- sent from myPhone --

                Beverly Voth

                --