Calculation based on multiple related records?

[MagicSpark]

Sorry if this is a silly question. I'm pretty new to FileMaker.

I am setting up a database to help academic advisors at a college. It uses multiple files, two of which are "students," and "grades." They are set up so that a portal in "students" displays all of a student's records from "grades," like a transcript, showing the course, the grade received, units earned, and semester the course was taken.

Now I am trying to set up another layout that will track whether students have fulfilled their graduation requirements. For example, ENG 1A is a required course. If the student has completed ENG 1A and has a grade reported in the "grades" table, I want the "ENG 1A units" field to display the number of units earned for the course.

Right now, the "ENG 1A units" field is defined this way:

If ( grades::course number = "ENG 1A" ; 4 )

This works fine, but only if the student's record for ENG 1A is the first one displayed in the portal. Is there any way to get this calculation to work based on all of the student's related records from "grades?"

I had scripts set up to fill these fields, but it was beginning to seem cumbersome.

I'll welcome any and all suggestions. Thanks.

[The Shadow]

Take a look at this example, by passing through a requirements table you can filter another instance of the "grades" table down to the requirements only.

I'm beginning to get the feeling I have too much free time on my hands...

[MagicSpark]

Thanks, Shadow. I've played around with your solution, and I may end up using it, but I'm afraid I wasn't quite specific enough about what I need.

I'm trying to diplay the requirements filled in a layout that looks as much as possible like the paper form that our advisors currently use, so they will be as unintimidated by the new system as possible. In order to do this, I need to be able to display different requirements in different places. I might be able to rig something with sorting and displaying single portal rows, but I'm hoping that there is a way to do this with calculation fields, especially since my backup space is limited and I'm concerned about doubling the number of records that need to be kept for each student.

Any thoughts?

Many thanks.