I'm sure the answer to this has already been posted, probably more than once. But I haven't found it after much searching.

I have 2 dbs -- 'students' and 'activeYears' with student ID being the linking field. Every year that a student participates in our program, I create a new record in 'activeYears' with the student ID, the active year and the particular school that student is attending. As the program runs from 4th through 12th grades, students can participate more than once per school (2 years in elementary, 3 in middle school and 4 in high school) therefore generating that many records per student in the 'activeYears' file.

Using a relationship between 'students' and 'activeYears' (_gClubName::school) I can show the students in any club. However, every record for that student at that school shows in the portal (4th AND 5th grades for elementary school, 6, 7 AND 8 if I'm filtering on their middle school, 9, 10, 11 AND 12 when filtering for high school).

How can I create a second layer of filtering so that only the records for the current year show for any student (only 10th grade for a student who also participated last year in the 9th grade at XYZ high school)?

I'm sure this isn't nearly as hard as it looks from here...

Abbe

Hi Abbe,

One solution would be to create concatenated match fields for both Students and ActiveYears

This would be a calc field in both files that would be something like:

StudentID&" - "&CurrentYear

Base your portal relationship on this.

HTH

-Raz

Raz,

How would I construct a calc field for my relationship if SCHOOL isn't in the 'student' db, only in the 'activeYears' file? I want the current club roster to show up in the portal when the user filters by school.

'Student' has studentID, nameFirst, nameLast, etc... All school information resides in the 'activeYears' file. The primary relationship between 'students' and 'activeYears' is 'studentID.' The relationship for the portal is based on a global (_gClubName) in the 'students' file (a checkbox with a value list from 'activeYears::school). _gClubName is related to activeYears::school.

The user selects the school/club from the _gClubName field and yes, all students from that school display in the portal. So do students who have ever attended that school.

Is this more helpful?

Abbe

I want the current club roster to show up in the portal when the user filters by school.

You need to devise a way to determine what "current" means to the database. To do this, you need to be tracking time (probably in years...) in your Activities file. "8th grade" is fine, but it doesnt say when the student was in 8th grade, so I am assuming that you also have a _tSchoolYear field in your activities file (if not, would be easy enough to make one that auto enters the correct year on creation). Now, you need to indicate which year is "current" for the filter, so you will need a _gSchoolYear field in the file with the portal.

Then match fields would be calcs:

_gClubName &" - "& _gSchoolYear in Students File to:

_tClubName &" - "& _tSchoolYear in Activities file

Does that make any more sense?

-Raz

Hey, that works! Thanks, Raz. I'm just beginning to understand the theory behind the more sophisticated relationship (more than just 'file1::field 1' to 'file2::field 1').

I love this forum!

Abbe