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.
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?
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?
by school name.
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.)
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. :)
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
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.
Do you have these links in your relationship graph?
Did you enter a year in School::gYear?
Is gYear defined as a global field?
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?
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
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.
Compare what you've done to the demo file--that should give you a clue.
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?