9 Replies Latest reply on Apr 12, 2012 7:23 AM by MargoMarie

    Quota Count

    MargoMarie

      I'm trying to wrap my head around the task of doing quota counts in FileMaker Pro. For instance, in market research we like to get counts on how many males or females are recruited on a focus group. Sometimes we recruit 100+ respondents. The gender is easy to get a count, however sometimes I'm required to combine that count with Ethnicity counts and Age counts. So I would need a report to tell me how many males vs. females, how many caucasians, hispanics, african american, asians, etc., then how many ages 25-29, 30-34, 34-39. I usually have to do this manually but I'm fully confident that FileMaker can do all these counts for me if I knew how to write a script or calculation to have it perform this report for me. Can anyone give me some help with this?

      Thanks,

      Margo

        • 1. Re: Quota Count
          Stephen Huston

          First thought:

          You could do this via relationships from global or constant fields to the field for that attribute in the persons records and do either Sum or Count on related values for each attibute relationship.

          • 2. Re: Quota Count
            BruceHerbach

            Margo

             

            To do this manually I assume you are doing a find.  To script this do a series of finds and assign the result of each find to a global variable.  You can put the global variables on a layout to display the results.

             

            You should check the error return on each find,  if no records are found it returns a value other the 0.   When this happens,  you have 0 found records.

             

            HTH

            Bruce

            • 3. Re: Quota Count
              Stephen Huston

              You can also use Bruce's method of collecting values in $$variables. You don't even need to do aany finds once you have found the set of records you want to test. Just script a loop through the records.

               

              Go to First Record

              [Set all global variables to Zero here in case they were already populated during the current session]

                   Loop

                        Use IF tests to detemine if matches each value and reset various global variables  to current value +1 on each match.

                        Go to Next record (exit after last)

                   End Loop

              Go to layout where $$variables appear to see the counts.

               

              This will leave yo with the original found set untouched, just the variables incremented during the loop.

               

              So now you have 3 ways to pick from, and others may still offer more.

              • 4. Re: Quota Count
                BruceHerbach

                One additional thought on the find method,  when you want to use multiple criteria you can use multiple fields in 1 record or use multiple record in the find.  The get the result with get(found count).

                 

                HTH

                Bruce

                • 5. Re: Quota Count
                  usbc

                  Another method is to do it in a sub-summary report.

                  A simplistic view:

                  You have a FocusGroup table. It includes the fields such as, Gender, Ethnicity, Age Range, etc. Also a field that is a calculated number = 1 ("Calc_1"). That will be used to count the sub categories.

                  Forgive me for not knowing your comfort level with sub-summary layouts but I'm sure there is a good example in the sample files (true?)

                  But it's a prettty sraight forward deal whereby you script a sort order including your category fields that will display this information in a handy view.

                  • 6. Re: Quota Count
                    Stephen Huston

                    I believe this method will require a different sort to return each subtotal, as nested subtotals within other subsummaries may confuse the issue.

                     

                    Still, when sorterd on the correct catagory fields, would allow quickly collecting the needed info visually. Might take a bit of work to capture and save the results of the various sorts within FileMaker, but would certainly be useful for on-screen  as needed.

                    • 7. Re: Quota Count
                      Malcolm

                      I believe this method will require a different sort to return each subtotal, as nested subtotals within other subsummaries may confuse the issue.

                       

                      You're correct. To do the counting correctly each attribute ( gender, age, income, etc) must have it's own record. The solution is to have the data stored in a related table.

                       

                      Malcolm

                      • 8. Re: Quota Count
                        usbc

                        I agree that there are far more sophisticated approaches. However, sometimes the person posting the question may be helped with a much more simple starting point.

                        In this case the poster mentions not knowing how to write a calculation or script. So looping variables can take on an aura of string theory.

                        The attached sample file is hopefully a handy step above "doing it manually" in a spread sheet ot text application.

                        • 9. Re: Quota Count
                          MargoMarie

                          Thank you for your reply to my post.  I will try all these helpful suggestions and let you know how it goes.

                          Margo