1 2 Previous Next 19 Replies Latest reply on Jun 11, 2010 5:12 PM by Sunny1

    Summary Field Help

    Sunny1

      Title

      Summary Field Help

      Post

      I have a portal on a tab control which shows school enrollment information.  The portal shows the info for school for that particular school district (Main Table).  The portal lists school names, the year and enrollment information.  Out side the portal, I have created a total field where I summarize the enrollment numbers for that school district.  I want to be able to summarize based on school type (public, private and charter) which are assigned to each school.  Then I need a total of all schools.  I only want to summarize the most current year info.  How do I do this?  Do I need four separate total fields, one for each school type and one for grand total?  How do I write the equation?

       

      Thank you for your help in advance.

       

      Sunny1

        • 1. Re: Summary Field Help
          philmodjunk

          If I remember your project correctly, you already have three portals set up with three different relationships as this is a filemaker 10 system...

           

          You can use the Sum function and refer to the field in your portal table occuurrence that you want to summarize.

           

          Sum ( Public::Enrollment )   will give you the total enrollment matching the records you have in these portals.

           

          If you want to limit your records to just those of a given year, you'll need a third pair of fields in your relationship that will filter them by year as well as school type and district.

          • 2. Re: Summary Field Help
            Sunny1

            So, I should have the enrollment field in each of the table occurances for the schools. (ie:  Public School, Private School and Charter).  Currently, I just created a table for Counts which includes School Name, enrollment and year.  Should I not have created this table?  The reason I did it this way is that I will be adding yearly information for each school as time goes on and I thought this was the best way.  Wrong?

             

             

            • 3. Re: Summary Field Help
              philmodjunk

              Sounds like you've done this exactly right. You hadn't described such a table, and I assumed incorrectly as to how you were implementing this.

               

              How have you linked Counts to the other table occurrences in your relationships graph?

              • 4. Re: Summary Field Help
                Sunny1

                by school name.

                • 5. Re: Summary Field Help
                  philmodjunk

                  Yes, but which table occurrence links to which table by school name?

                   

                  If I have the actual table occurrence names and how they are linked, I can give you an answer that is much easier to follow. Include your existing relationships for your three filtered portals that we set up in an earlier thread also.

                   

                  (And school name isn't your best choice for linking tables--some times a school's name is changed and that'll mess up your data.)

                  • 6. Re: Summary Field Help
                    Sunny1

                    My main table that starts this whole thing is SchoolDistrict.  Then I have a table called Schools which is conected by school district name.  Then I have three table occurances of schools called Public School, Private School and Charter School.  They are linked to Schools by Schooltype and School District name.  Then I have a table named Counts that is linked to table Schools by schoolname.  It contains the fields SchoolName, Enrollment, Year, Teachers (because we will track the number of teachers at a given school each year), and schooltype.  I added this last field thinking that I may need it to help with my totaling but I can delete it if not needed.

                     

                    In a nut shell, each school district has numerous schools.  Some Public, Private and Charter.  I need to keep track of the info for each and for all. 

                     

                    I hope that helps you help me.  :)

                    • 7. Re: Summary Field Help
                      philmodjunk

                      Let's do it this way:

                       

                      Create three table occurences of Counts, Public Counts, Private Counts, and Charter Counts.

                       

                      Define a global field, gYear in Schools.

                       

                      Link your table occurrences like this:

                      Public School::School = Public Counts::School AND

                      Public School::gYear = Public Counts::Year

                       

                      Define a calculation field, cPublicEnrollment, in SchoolDistrict as

                      Sum ( Public Counts::Enrollment )

                       

                      Do the same for the other two new Table occurrences.

                       

                      For Total District Enrollment, use cPubliEnrollment + cPrivateEnrollment + cCharterEnrollment

                       

                      That will likely work best for putting totals on your tabs.

                       

                      Another approach is to design a summary report based on Counts, that uses summary fields and sub Summary parts to list all schools in a distrcit, grouped by type with enrollment subtotals.

                       

                      If that looks like a good option, see this thread:

                      Creating Filemaker Pro summary reports--Tutorial

                      • 8. Re: Summary Field Help
                        Sunny1

                        There must be something missing because my information does not feed into the SchoolDistrict cPublicEnrollment field.  Is a relationship missing?  When I look at the table for School District, the field for cPublicEnrollment is empty though the Public Counts is correct.

                        • 9. Re: Summary Field Help
                          philmodjunk

                          Do you have these links in your relationship graph?

                           

                          SchoolDistrict-----------<PublicSchool---------<Public Counts

                           

                          Did you enter a year in School::gYear?

                           

                          Is gYear defined as a global field?

                          • 10. Re: Summary Field Help
                            Sunny1

                            Yes, I checked each of those items.  Currently, the field that I am using to reflect the information is from SchoolDistricts and the field is cPublicEnrollment.  That should show the total of PublicCounts.  But it doesn't show anything.  When I look at the relationships, there is no connection between COUNTS and PUBCOUNTS, PRICOUNTS or CHRCOUNTS.  Those three table occurances are related to PUBSCHOOL, PRISCHOOL and CHRSCHOOL accordingly.  Those three are related to SCHOOLS and COUNTS is related to SCHOOLDISTRICTS.  is something missing?

                            • 11. Re: Summary Field Help
                              philmodjunk

                              Yes, those aren't quite the relationships I thought you had here.

                               

                              I assumed you had this relationship linking SchoolDistrict to each of the three "filtered" table occurrences for Schools:

                               

                              SchoolDistricts:: District = Public Schools:: District AND

                              SchoolDistrict::cPublicKey = Public Schools::SchoolType

                               

                              This gives you the needed link from SchoolDistricts to Schools to Counts (using data source names instead of table occurrence names) needed for cPublicEnrollment to compute a total enrollment.

                               

                              Here's a demo file: http://www.4shared.com/file/dNKt2abj/TwoLevelRelwGlobal.html

                              • 12. Re: Summary Field Help
                                Sunny1

                                I recreated the previous step because so many changes have occurred that I wanted to start fresh.  Then I added your last instruction and now all of my information about each school on the Public school tab, Private School Tab and CHarter school tab have disappeared because the relationship that was connecting them is gone.  I have to go back a few steps and start fresh.  Something is really wrong.

                                • 13. Re: Summary Field Help
                                  philmodjunk

                                  Compare what you've done to the demo file--that should give you a clue.

                                  • 14. Re: Summary Field Help
                                    Sunny1

                                    I am finally back working on my database and I have gone back several steps and recreated where I started trying to get the enrollment info to show up.  I can get it to show up by school, on the correct tab in my portal but I cannot get the school districts to total by Public, Private and Charter, and then to do a grandtotal of all three.  Something in the relationships is not correct because I do not show where they are linked.  In doing the steps you stated, I end up with so many table occurrences I can keep them straight.  I was unable to get to your Demo file.

                                     

                                    I just know that i must have one small thing wrong for this not to work.  Can you still help me?

                                     

                                     

                                    1 2 Previous Next