Calculation within a portal

[missjean]

Filemaker 12 - Beginner - I have 3 portals listing courses students can take to satisfy their major & minor requirements.  I also have 3 portals below them listing the courses the students have already taken (within those lists) along with the total points accumulated.   If a student takes a minor course which is also listed in the major course choices they cannot count that as points accumulated to satisfy their minor requirement. (these courses are listed but have strike thru conditional formatting on them).  I would like to total the courses taken within their Minor list....(minus the courses that cannot be counted).  I am having trouble with the calculation to subtract those duplicate courses.  The field used for points is just "points".  The conditional formatting formula used to compare is as follows...

not IsEmpty ( FilterValues ( List (MajorsTable 4::Course ) ; grades 13::Dept_Course ) )

[eos]

Note that we don't know the structure of your solution (no to mention of any field names), so the following is a supposition:

Try creating a self-join in your … I assume, “Enrolments”(?) table, where

Enrolment::courseNumber = Enrolment_self::courseNumber
Enrolment::year = Enrolment_self::year
Enrolment::term = Enrolment_self::term
Enrolment::studentID = Enrolment_self::studentID
Enrolment::type ≠ Enrolment_self::type

in other words: find the counterpart enrolment type for the same student in the exact same term for the same course – whatever predicates are necessary to do that. If e.g. a course number uniquely identifies a course within a year/term, you wouldn't need these two fields (and wouldn't have them in the table to begin with).

then create a calculation field as

Case (
  type = "Major" or IsEmpty ( Enrolment_self::id ) ;
  points
)

and sum that field - which only holds points if the type is Major, or it is Minor (implicitly, since at that point we know it is not Major) without a Major counterpart.

The only problem is that this is necessarily an unstored calculation; you could add a flag field and set that whenever the situation from the calculation field arises (double enrolment in both types) and base the calculation field on that flag field. For this it would probably be best if enrolments were added via script.