I can't really tell how the second grid? Portal? Table view? ??? of info is supposed to relate to the first.
But there are many different ways in FileMaker that would let you match to a specific test record by student ID and by the year. I am assuming that each row in the first table is a different record in your test table with the same student ID, but a different year.
StudentTable::__pkStudentID = TestTable::_fkStudentID AND
StutdentTable::gSelectedYear = TestTable::Year
gSelectedYear can be a global field where you select the year for which you want to see your averages.
You can also use a relationship that matches only by student ID, if you then use a portal filter that filters by a specific year that you can again specify inside a global field.
ExecuteSQL could return the average for a specified year and student (or all students for a specified year for that matter).
You could also perform a find for a specific year on the testTable and use a layout based on the test table to list information from both the test table and the related test table.
I like the idea of the AND relationship. If I want to be able to see 2015, 2016, 2017 each on the StudentTable, would I need three global fields? g2015, g1016 and g2017? Then make another one each year? Does that also mean I would make a relationship for each one?
The portal filter is also a good idea. I see how it can filter the TestTable to the correct year; however, I can't picture how to get the average.
In both examples where would the average calculation field live? In the StudentTable or in the TestTable?
Also, I need to be able to find and sort in the average field.
If you can point me in the right direction, I can get there.
Thanks so much.
How is the data arranged in your tests table? If a given student has three test scores, do you use one record with three fields or a single field with three records?
There are 6-8 records in the TestTable each year for each Student.
Does that mean that you have only one test score in each record?
Is your average that of every test score for a given student for one year?
Great question. Each record is one test entry; however, each test has 5 categories. Those 5 average to a test average.
The years on the top grid are Averages of each Student's Tests in each category. (1, 2, 3, 4, 5, overall)
Thanks for helping
What you need would appear to be a cross tab report. There are a number of good resources on the subject that you can research. You can use either a different relationship or an ExecuteSQL call for each combination of studentID, category, and year to calculate an average. Summary fields and filtered portals can also compute these values.
I got my head around the summary fields and filtered portals.
I made summary fields in the TestTable calculating the Average. One field for each category (5 total).
In the TestTable, I did some finds and I see the calculation is giving the correct average based on the find.
I then added a portal, placed the summary fields, and made the portal filtered. The summary fields are not dynamic based on the filtered portal.
Should the summary fields work that way? I might be missing something.
Perhaps this article will help?
If you duplicate your filtered portal, make it one row and include a summary field (aggregate) from the child records, the filter will be honored even if you dynamically change the criteria.
Sent from miPhone