This type of report format:
Project A: 5
Project B: 6
PRoject C: 3
Project A: 3
Project B: 2
Project C: 1
Is much easier to create in FileMaker than the "grid" you describe. Please confirm that you really need the grid.
Also, what version of FileMaker are you using?
You can get your columns with portals, but they can be a little bit easier to set up using portal filters in FileMaker 11.
I have File Maker Pro 11.
When I say grid, I only mean it looks like a grid or spreadsheet. In your example, can we make it read across and down
ie: Grade 1 Grade 2 Grade 3 Total
Project A: 5 8 7 20
Project B: 2 0 5 7
Total 7 8 12 27
That's my point, the format you specify can be done but takes a lot more setting up to create and maintain than the classic summary report format I gave as a counter example.
Well, personally, I don't see much difference than the previous summary that I have for my enrollment counts. So that method will probably suffice. I am doing this as a favor to someone else and just going by what they gave me. The previous method, I just used it on a tab control not a report so I am not sure how to relate it.
Do I create a whole other table for counts and such?
Assuming you have this relationship between your two tables, you can do it this way in FileMaker 11:
Students::ProjectID = Projects::ProjectID
Define a summary field, sStudentCount that is the "count of" a nonblank field in the table such as ProjectID
Create a list view layout on a Projects based layout with the project name field in column 1. Place one single row portal to Students for each of the remaining columns you need for your report. Put sStudentCount as the sole field in each portal row. Since each portal will be identical except for their filter expressions, you can set up the first one and then copy it for each of the remaining columns double clicking them to open Portal Setup... where you can specify the appropriate filter.
In the first portal you'd create a portal filter expression such as: Students::Grade = 1
In the last column, don't use any filter so that you will see the total for that row.
Hmmm, now that i've written that out, using FileMaker 11, makes this a much simpler approach than that required for older versions where you'd need a different special relationship for every portal...
In using your example, Yes, I do have a relationship with ProjectID which is the auto serial number that I am creating in the background. But I had to substitute Project Category for the Project Name field in Column 1 since we want to have totals of students by grade in each category. So, I created a category field in the student table and added the relationship to Projects. But I am not getting any numbers in the boxes. What have I done wrong...any ideas?
As a list view layout, if we have 10 categories but only three have been used in this example, do I need 10 rows or just three?
Let's check some basic assumptions before we get any further.
In Students you have one record for every student.
In Projects, do you have one record for every student project or one record for every project category? I assumed the latter but it now sounds like every time a student starts a project, you create a record for it in Projects.
Yes, in students I have one record for each student.
In Projects, I have one project but could have more than one student. In Projects, I have one category for each project. But a project titled Project 2 can be category BIO and Project 3 can be category BIO but they each have a separate Project ID.
Does that help?
Just thought of another question where I might assume wrong and mess this up.
Are all students assigned to a given project the same grade level? (Split grade level classes come to mind here.)
yes, there are no split grade levels. Each 8th grader is doing an 8th grade project.
Also, I just learned that while we made the Project ID number consist of the Category, the grade and the auto serial number, I found out that they want it to be the Category, the grade level and the auto serial number within that category. IE: BIO07001, BIO07002, BHS07001, rather than BIO07001, BHS07002, BIO07003.
Project ID should strictly be a serial number that you don't let your users see. You can then add such a field to your projects table for labeling purposes, but when they decide they need to change it like this, it won't break all the links to your student records.
OK, we can work with that.
Keep the original ProjectID based relationship between projects and students.
If you don't already have it, define a calculation field, cStudentCount, in Projects as Count ( Students::ProjectID ). This computes the number of students per individual project.
Define a summary field sStudentTotalCount, as the total of cStudentCount. We can use this field to compute total counts within categories.
Now we need a new relationship for our portals.
Go to Manage | Database | Relationships and drag from the Projects::Category field to outside the box and then back to this same field. This creates a new table occurrence to Projects and relates them by category. You can rename this occurrence from Projects 2 to ProjectsSameCategory.
Now select Students and click the button with two plus signs to make a second occurrence of this table. Connect it to ProjectsSameCategory by ProjectID. We'll call this one StudentsSameCategory.
Now create a new layout based on Projects, enter layout mode and double click the label for the body layout part. Change it to a sub summary part when sorted by Category. Place the category field as the first field in this sub summary part. then place your portals to the right of it. Make each portal filter an expression that compares a value like 5 to Grade level in StudentsSameCategory to filter out all values except for grade 5 students.
Here's a demo file you can look at: http://www.4shared.com/file/pWRM5Hjy/StudentProjects.html
Earlier in our creation emails, you had me create a summary field in Projects to count Project IDs. Am I no longer using that field? Also, in this recent email...I am confused...you say to create a cStudentCount calculation field in Projects as Count. Is the name cStudentCalculation or Count.
My earlier post was based on incorrect assumptions about what kind of data was stored in Projects. Please disregard.
cStudentCount is the field name I suggested. Count ( Students::ProjectID ) is the calculation expression you should enter into it's field definition.
If you download the demo file and check it out, you'll find this all set up there so you can see in greater detail how this might be set up in your database.
Where to find the demo file?