3 Replies Latest reply on Nov 16, 2009 9:33 AM by philmodjunk

    how to analyse data in filemaker pro



      how to analyse data in filemaker pro




      I created a registration layout. In this layout there are field like:  Gender, race, salary etc.  What i would like to analyse is... whwnever a data entry is made i would like to count automatically how many male gender, female gender, chinese race, indian race and how many with 1000-2000 dollar salary etc.

      This data analysis is in a new layout.


      Filemaker pro 9.0 advanced

      already create a data system using filemaker, but still new to advanced function


      Your help would be much appreciated


      Thank you, hope for a  quick respond.

        • 1. Re: how to analyse data in filemaker pro

          This is relationship question and solution.

          Make self-join relationship for each data you want.

          Use Count for each joined table. 

          • 2. Re: how to analyse data in filemaker pro

            There are several ways to do this, and not just a little different. It partly depends on how much control you want over the layout, and whether these "counts" are somehow permanently stored or not, which depends on the volume of records (hundreds vs. hundreds of thousands for example).


            But the easiest and simplest way is to use "summaries", and let FileMaker sort it out (pun). Just create a "constant = 1" calculation (or number) field. It needs to have a 1 in it always. You can have that be an auto-enter, or create a calculation, with 1 in it (that's all). Then create another field, a Summary field, which summarizes it. 


            On your "summary" layout add a Subsummary "part" for each of the things you want to summarize. Let's start with Gender, then Race. In Layout mode, Layouts menu, Parts Setup..., add the Subsummary parts (you can put them "above" or "below" the Body part; but we're going to delete the Body part, so it makes little difference). You can drag them around if you get the order wrong (but this make little difference either, as their "view" position is determined by the Sort order).


            The 1st Subsummary part is by Gender, so you put the Gender field in that part (just as is, not "radio" button). In the 2nd Subsummary put the Race field. In both cases put the Summary count field next to the field. Remove the Body part from this layout (it cannot work anyway, as the same person will have a Gender AND a Race, and could only be shown once in the Body).


            Sort by Gender, then Race. You will a Subsummary part for each different gender (2 the last time I counted), and one for each different race (spelling matters), followed by the dynamic count of each. 


            If you are using FileMaker 9 you will have to go into Preview mode in order to see the above. In FileMaker 10 you can see it in Browse mode. It would be worth upgrading for this feature alone, in my opinion. I use it fairly often. In Browse mode the buttons still work, even data entry. In FileMaker 9 you'd need a script to go to the layout, Enter Preview mode, then Pause (indefinitely; hit the Enter key to continue), then Enter Browse mode, then return to the original layout.


            For the "salary" part of it, you'd need to create a calculation field to assign an "index" value to each.

            Case (

            Salary = 0; 0; 

            Salary < 1000; 1;

            Salary < 2000; 2;

            Salary < 3000; 3;

            Salary < 5000; 4;

            Salary < 10000; 5;

            6 )


            A Case statement will exit when it hits the first test which is true. You would Sort by the above "Salary_index" field, and base another Subsummary on it. You would still use the same Summary field in it. That is the beauty of subsummaries; you only need 1 field for the counting of any number of fields. 


            You'd probably also want a calculation field, Text result, Storage, [x] Do not store, to show the range of each index (just for looks).

            Case (

            Salary_index = 1; "1 - 999"

            Salary_index = 2; "1000 - 1999";




            The Sort order determines where each Subsummary part will appear. So if you sorted by Salary_index, then Gender, then Race you'd be seeing a breakdown of people by Salary. If you have FileMaker 10 this can be easily done while on the layout, as buttons to do so would still work.

            • 3. Re: how to analyse data in filemaker pro
                 A third option is to simply perform finds for the desired records and count how many are found. All of three of these methods work. Which is best will depend on the design of your database, its interface and the specific circumstances underwhich you need to be able to produce this information.