1 Reply Latest reply on Jun 18, 2014 4:01 PM by philmodjunk

    Graphing Class Demographics

    SamJaques

      Title

      Graphing Class Demographics

      Post

           I have a database with four tables that are relevant to this problem: Contacts, sign-ups, and programs, and program types. Contacts are related to sign-ups by first are last name, which are then related to programs by the program name, just like the example in the tutorial for many-to-many relationships. Programs are related to the program type table by a "program type" field, and the program type relates different instances of programs from different years (so if there was a program on diabetes in 2010, 2011, and 2012, they would all relate to each other). What I'd like to do is create a graph of the demographics for each type of program - i.e., a histogram of how many people are in each age and gender category for a given type of program.

            

        • 1. Re: Graphing Class Demographics
          philmodjunk

               Contacts---<signups>---Programs>----ProgramTypes

               

                    and the program type relates different instances of programs from different years (so if there was a program on diabetes in 2010, 2011, and 2012, they would all relate to each other)

               Not sure how you set that up. Is each "instance" a box in the relationship graph but with Programs as the data source table in each case? Are there different match fields and/or matchfield values such that the relationship for 2011 programs matches different records to a Program type record than in the case of 2012 programs?

               And that may be besides the point as it looks like you would need to chart this on a layout based on Signups anyway. But the multiple criteria identifying each bar in your histogram or bar chart will make this tricky to set up as each bar in the chart is a combination of program type, gender and age group. Unfortunately, current versions of FileMaker can only group records for charts on a single sort field, not three so it takes a calculation field that combines the values from the three different fields into a single text code that then sorts correctly to provide the needed grouping from a single field sort.

               

                    Contacts are related to sign-ups by first are last name, which are then related to programs by the program name,

               Note that linking records by names is not the best approach for these relationships. Names are not unique, people and programs change their names and trying to correct a data entry error after records have been linked via that name can be difficult to do without breaking needed links to related records. Using auto-entered serial numbers to identify each contact and each program makes for a better design as it avoids those issues.